Technology and Information: Panther: Instance schema / Schema da inst�ncia

This article is written in English and Portuguese
Este artigo est� escrito em Ingl�s e Portugu�s

English version:
This time I'll cover a little but helpful improvement in the dbschema utility. dbschema is the fastest way we have to extract DDL statements about our databases and tables. We can get the full database schema, or just for a table, view, synonym etc. It can also provide all the privileges of objects in the database.

Version 11.7 (Panther) introduces an extension to this tool. We can now obtain the following information at the instance level:

Allocated space (dbspaces, chunks...)
Location and size of the physical log
Location and size of the logical logs

This information was easily available either as the output of onstat commands or by querying the sysmaster database. But then you would need to construct the statements to allocate them. Also note that this could be done by using shell command line interface (onspaces utility) or (since version 11.10) by using the SQL Admin API.

So, now in version 11.7 you can obtain all this info in a ready to use format by running dbschema utility with the new option "-c". By default it will generate SQL statements you can use through the SQL Admin API. But if you also use the "-ns" option (no SQL) it will generate the operating system utilities syntax. Let's see an example of each. I will crop the output so that it does not become too long.


panther@pacman.onlinedomus.net:fnunes-> dbschema -c -q

--  Dbspace 1 -- Chunk 1
--  EXECUTE FUNCTION TASK
('create dbspace', 'rootdbs', 'rootdbs.c1', '250000', '0', '2', '500', '500');

--  Dbspace 2 -- Chunk 2
EXECUTE FUNCTION TASK
('create tempdbspace', 'dbtemp1', 'dbtemp1.c1', '100000', '0', '2', '100', '100');

--  Dbspace 3 -- Chunk 3
EXECUTE FUNCTION TASK
('create sbspace', 'sbs1', 'sbs1.c1', '20000', '0');

--  Dbspace 4 -- Chunk 4
EXECUTE FUNCTION TASK
('create dbspace', 'dbs1', 'dbs1.c1', '250000', '0', '2', '100', '200');

--  Physical Log
EXECUTE FUNCTION TASK
('alter plog', 'rootdbs', '50000');

--  Store pre-existing logical logs information before create new logical logs    
DATABASE sysadmin;    
CREATE TABLE llog (log smallint, flags  smallint);    
INSERT INTO llog SELECT number, flags FROM sysmaster:syslogfil;

--  Logical Log 1
EXECUTE FUNCTION TASK
('add log', 'rootdbs', '10000');

--  Logical Log 2
EXECUTE FUNCTION TASK
('add log', 'rootdbs', '10000');

-- [ CUTTED TEXT.... ]
-- [... more logical logs here... ]

--  Logical Log 10
EXECUTE FUNCTION TASK
('add log', 'rootdbs', '10000');

--  Drop all pre-existing logical logs     
EXECUTE FUNCTION TASK
('checkpoint');

SELECT TASK ('drop log', log) FROM sysadmin:llog    
WHERE sysmaster:bitval(flags,'0x02')==0;

EXECUTE FUNCTION TASK
('checkpoint');

SELECT TASK('onmode', 'l') FROM sysmaster:syslogfil    
WHERE chunk = 1 AND sysmaster:bitval(flags,'0x02')>0;

EXECUTE FUNCTION TASK
('checkpoint');

SELECT TASK ('drop log', log) FROM sysadmin:llog    
WHERE sysmaster:bitval(flags,'0x02')==1;

DROP TABLE sysadmin:llog;

And now with the -ns option:


panther@pacman.onlinedomus.net:fnunes-> dbschema -c -q -ns
# Dbspace 1 -- Chunk 1
# onspaces -c -d rootdbs -k 2 -p rootdbs.c1 -o 0 -s 250000 -ef 500 -en 500

# Dbspace 2 -- Chunk 2
onspaces -c -d dbtemp1 -k 2 -t -p dbtemp1.c1 -o 0 -s 100000

# Dbspace 3 -- Chunk 3
onspaces -c -S sbs1 -p sbs1.c1 -o 0 -s 20000 -Ms 348

# Dbspace 4 -- Chunk 4
onspaces -c -d dbs1 -k 2 -p dbs1.c1 -o 0 -s 250000 -ef 100 -en 200

# Physical Log
onparams -p -s 50000 -d rootdbs -y

# Store pre-existing logical logs information before create new logical logs      
dbaccess sysadmin << END      
CREATE TABLE llog (log smallint, flags  smallint);      
INSERT INTO llog SELECT number, flags FROM sysmaster:syslogfil;      
END

# Logical Log 1
onparams -a -d rootdbs -s 10000

# Logical Log 2
onparams -a -d rootdbs -s 10000

## CUTTED TEXT HERE
## ... More logical logs...

# Logical Log 10
onparams -a -d rootdbs -s 10000

# Drop all pre-existing logical logs       
onmode -c
   
dbaccess sysadmin << END      
SELECT TASK ('drop log', log) FROM sysadmin:llog      
WHERE sysmaster:bitval(flags,'0x02')==0;      
END
   
onmode -c
   
dbaccess sysadmin << END      
SELECT TASK('onmode', 'l') FROM sysmaster:syslogfil      
WHERE chunk = 1 AND sysmaster:bitval(flags,'0x02')>0;      
END
   
onmode -c
   
dbaccess sysadmin << END      
SELECT TASK ('drop log', log) FROM sysadmin:llog      
WHERE sysmaster:bitval(flags,'0x02')==1;
   
DROP TABLE sysadmin:llog;      
END

This is useful if you want to keep your instance layout for recovery purposes or if you want to recreate a similar instance layout on your test or quality environments.
Just one thing to note. The generated instructions include the statements to create the root dbspace. And as we know, this is created during instance initialization. That chunk instruction should be removed from the scripts but it's important for documentation purposes.

Vers�o Portuguesa:

Desta vez vou abordar uma pequena mas �til melhoria no utilit�rio dbschema. dbschema � a forma mais r�pida de extrair instru��es DDL sobre as nossas bases de dados e tabelas. Podemos obter o esquema completo da base de dados ou apenas de uma tabela, view, sin�nimo etc. Pode tamb�m fornecer todos os privil�gios dos objectos na base de dados.

A vers�o 11.7 (Panther) introduz uma extens�o a esta ferramenta. Podemos agora obter a seguinte informa��o ao n�vel da inst�ncia:

Espa�o alocado (dbspaces, chunks...)
Localiza��o e tamanho do physical log
Localiza��o e tamanho dos logical logs

Esta informa��o j� estava dispon�vel facilmente quer como resultado da execu��o do comando onstat ou atrav�s de queries na base de dados sysmaster. Mas depois tinham de ser criados os comandos para alocar/definir os referidos objectos. Note-se ainda que isto poderia ser feito atrav�s de utilit�rios na shell (utilit�rio onspaces) ou (desde a vers�o 11.10) atrav�s da SQL Admin API.

Agora, na vers�o 11.7, podemos obter esta informa��o num formato "pronto a usar" correndo o utilit�rio dbschema com a op��o "-c". Por pr�-defini��o ir� gerar as instru��es SQL que pode usar atrav�s da SQL Admin API. Mas pode tamb�m usar a op��o "-ns" (no SQL) para que seja gerada a sintaxe utilizando os utilit�rios a correr no sistema operativo. Vejamos um exemplo de cada. Irei cortar o output para que n�o se torne demasiado longo.


panther@pacman.onlinedomus.net:fnunes-> dbschema -c -q

--  Dbspace 1 -- Chunk 1
--  EXECUTE FUNCTION TASK
('create dbspace', 'rootdbs', 'rootdbs.c1', '250000', '0', '2', '500', '500');

--  Dbspace 2 -- Chunk 2
EXECUTE FUNCTION TASK
('create tempdbspace', 'dbtemp1', 'dbtemp1.c1', '100000', '0', '2', '100', '100');

--  Dbspace 3 -- Chunk 3
EXECUTE FUNCTION TASK
('create sbspace', 'sbs1', 'sbs1.c1', '20000', '0');

--  Dbspace 4 -- Chunk 4
EXECUTE FUNCTION TASK
('create dbspace', 'dbs1', 'dbs1.c1', '250000', '0', '2', '100', '200');

--  Physical Log
EXECUTE FUNCTION TASK
('alter plog', 'rootdbs', '50000');

--  Store pre-existing logical logs information before create new logical logs    
DATABASE sysadmin;    
CREATE TABLE llog (log smallint, flags  smallint);    
INSERT INTO llog SELECT number, flags FROM sysmaster:syslogfil;

--  Logical Log 1
EXECUTE FUNCTION TASK
('add log', 'rootdbs', '10000');

--  Logical Log 2
EXECUTE FUNCTION TASK
('add log', 'rootdbs', '10000');

-- [ CUTTED TEXT.... ]
-- [... more logical logs here... ]

--  Logical Log 10
EXECUTE FUNCTION TASK
('add log', 'rootdbs', '10000');

--  Drop all pre-existing logical logs     
EXECUTE FUNCTION TASK
('checkpoint');

SELECT TASK ('drop log', log) FROM sysadmin:llog    
WHERE sysmaster:bitval(flags,'0x02')==0;

EXECUTE FUNCTION TASK
('checkpoint');

SELECT TASK('onmode', 'l') FROM sysmaster:syslogfil    
WHERE chunk = 1 AND sysmaster:bitval(flags,'0x02')>0;

EXECUTE FUNCTION TASK
('checkpoint');

SELECT TASK ('drop log', log) FROM sysadmin:llog    
WHERE sysmaster:bitval(flags,'0x02')==1;

DROP TABLE sysadmin:llog;

E agora usando a op��o -ns:


panther@pacman.onlinedomus.net:fnunes-> dbschema -c -q -ns
# Dbspace 1 -- Chunk 1
# onspaces -c -d rootdbs -k 2 -p rootdbs.c1 -o 0 -s 250000 -ef 500 -en 500

# Dbspace 2 -- Chunk 2
onspaces -c -d dbtemp1 -k 2 -t -p dbtemp1.c1 -o 0 -s 100000

# Dbspace 3 -- Chunk 3
onspaces -c -S sbs1 -p sbs1.c1 -o 0 -s 20000 -Ms 348

# Dbspace 4 -- Chunk 4
onspaces -c -d dbs1 -k 2 -p dbs1.c1 -o 0 -s 250000 -ef 100 -en 200

# Physical Log
onparams -p -s 50000 -d rootdbs -y

# Store pre-existing logical logs information before create new logical logs      
dbaccess sysadmin << END      
CREATE TABLE llog (log smallint, flags  smallint);      
INSERT INTO llog SELECT number, flags FROM sysmaster:syslogfil;      
END

# Logical Log 1
onparams -a -d rootdbs -s 10000

# Logical Log 2
onparams -a -d rootdbs -s 10000

## CUTTED TEXT HERE
## ... More logical logs...

# Logical Log 10
onparams -a -d rootdbs -s 10000

# Drop all pre-existing logical logs       
onmode -c
   
dbaccess sysadmin << END      
SELECT TASK ('drop log', log) FROM sysadmin:llog      
WHERE sysmaster:bitval(flags,'0x02')==0;      
END
   
onmode -c
   
dbaccess sysadmin << END      
SELECT TASK('onmode', 'l') FROM sysmaster:syslogfil      
WHERE chunk = 1 AND sysmaster:bitval(flags,'0x02')>0;      
END
   
onmode -c
   
dbaccess sysadmin << END      
SELECT TASK ('drop log', log) FROM sysadmin:llog      
WHERE sysmaster:bitval(flags,'0x02')==1;
   
DROP TABLE sysadmin:llog;      
END

Isto � �til se quiser manter um "esqueleto" da sua inst�ncia para prop�sitos de recupera��o ou se quiser recriar uma estrutura semelhante nas suas inst�ncias de teste ou qualidade.
H� que notar um detalhe. As instru��es geradas inclu�m c�digo para criar o root dbspace. E como n�s sabemos, este � criado durante a inicializa��o da inst�ncia. Assim, a instru��o referente a esse chunk dever� ser removida dos scripts. No entanto a sua exist�ncia � importante para efeitos de documenta��o.