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.