quinta-feira, 12 de agosto de 2010

Manutenção de Tabelas

Tabelas do Catálogo do Sistema

  • systableDescreve cada tabela no banco de dados

    SELECT * FROM systables
    WHERE tabname = “customer”;

  • syscolumns – Descreve cada coluna nas tabelas

    SELECT * FROM syscolumns
    WHERE tabid = (SELECT tabid FROM systables WHERE tabname = “customer”);

Sempre que um banco de dados é criado, automaticamente são criadas tabelas de catálogo de sistema que armazenam informações sobre o banco de dados.  Sendo que o catálogo de sistema é armazenado dentro de tabelas normais do banco de dados, elas podem ser examinadas como qualquer outra tabela do banco de dados. Você pode usar estas tabelas para se familiarizar com a estrutura do banco de dados.

  • A tabela de catálogo de sistema systables descreve cada tabela do banco de dados. Ela contém uma linha para cada tabela, view, e sinônimos definidos no banco de dados, inclusive as tabelas de catálogo de sistema deles. A informação armazenada na tabela inclui o nome da tabela, dono, tamanho de linha, números de linhas, numero de colunas, modo de lock, tamanho de extents, e números de índices. A ligação entre a informação em systables com outras tabelas de catálogos é a coluna tabid, o identificador de catalogo de sistema tem tabids menores que 100.
  • A tabela de catálogo de sistemas syscolumns descreve cada coluna nas tabelas no banco de dados. Cada linha contem um nome de coluna, o tabid da tabela, o numero de seqüência da coluna dentro da tabela, o tipo de coluna, e o tamanho físico.

Tabelas Temporárias

CREATE TEMP TABLE temp_ordem (
ordem_num INTEGER) WITH NO LOG;

SELECT nome, sobrenome, cidade
FROM clientes
INTO TEMP temp_clientes WITH NO LOG;

Tabelas temporárias podem ser criadas em dbspaces especificamente criados para objetos temporários designados pela variável de ambiente ou parâmetro de configuração do Informix DBSPACETEMP.

Você pode criar tabelas temporárias que são semelhantes a uma tabela permanente a não ser que só é valida para a duração da sessão. Não há nenhuma entrada para uma tabela temporária nos catálogos de sistema systables ou syscolumns. Você não pode alterar tabelas temporárias. Você pode criar índices nelas.
Se você fechar a conexão com o banco de dados atual, a tabela temporária será apagada. Alternativamente, você pode derrubar uma tabela temporária usando o comando DROP TABLE.

Dbspaces temporários

As tabelas temporárias devem ser criadas em um dbspace que é especificamente designado para tabelas temporárias dentro do sistema Informix. Um dbspace temporário não gera registro nos logical logs. Então, tabelas temporárias criadas em um dbspace temporário devem ser de um banco de dados sem log ou tem de ser criadas usando a sintaxe WITH NO LOG. Um dbspace é designado como temporário no momento em que é criado.

DBSPACETEMP

A variável de ambiente DBSPACETEMP pode ser configurada para um ou mais dbspaces. Se uma variável de ambiente DBSPACETEMP não é configurada, o Informix usa o valor do parâmetro de configuração DBSPACETEMP.

Sinônimos

CREATE SYNONYM ord FOR logix:ordem;

CREATE SYNONYM cust FOR logix@prd:aviso_rec;

Você pode criar um sinônimo (synonym) para qualquer tabela ou view em qualquer banco de dados de seu servidor ou qualquer servidor de banco de dados na rede. O exemplo a seguir mostra um sinônimo para uma tabela fora do banco de dados atual:

CREATE SYNONYM ord FOR logix:ordem;

Se a tabela esta em um servidor de banco de dados diferente em sua rede, este servidor de banco de dados deve estar on-line quando você cria o sinônimo:

CREATE SYNONYM cust FOR logix@prd:aviso_rec;

Você pode criar um sinônimo para uma tabela que esta sendo excluída ou movida para um local diferente. Se o sinônimo tem o mesmo nome das tabelas excluídas ou movidas, a mudança será transparente para os usuários.
Os sinônimos são descritos na tabela de catalogo de sistema systables, com um tipo de tabela (tabtype) de “S”, e na syssyntable.

Privilégios

Os usuários tem os mesmos privilégios para um sinônimo como para a tabela para a qual o sinônimo aponta. Se o seu banco de dados é MODE ANSI, ou se você usa a palavra chave PRIVATE ao criar sinônimo, o usuário tem que saber o nome do dono do sinônimo.

CREATE PRIVATE SYNONYM meu_estoque FOR logix:estoque_trans;
SELECT * FROM amilcar.meu_estoque; – amilcar é o dono

Alterando uma Tabela

Usando o comando ALTER TABLE, você pode:

  • Adicionar uma coluna.
    Usando a clausula ADD, uma coluna pode ser acrescentada à tabela. Os conteúdos da coluna serão NULOS para linhas que já existam antes do comando ALTER TABLE ser executado. Você pode especificar onde por a coluna somando a clausula BEFORE. O exemplo seguinte põe a coluna ordem_op antes da coluna order_data:

    ALTER TABLE ordem
    ADD ordem_op INTEGER
    BEFORE ordem_data;
  • Modificar uma coluna.
    Você pode usar a clausula MODIFY para mudar o tipo de dado, o comprimento, ou permissão ou não de nulos em uma coluna. Você tem que especificar todos os atributos existentes para uma coluna ( por exemplo, as constraints UNIQUE e NOT NULL), ou eles serão excluídos. Você pode alterar uma tabela para mudar o tipo de dado se o novo tipo de dados for incompatível com os dados que já estão na coluna.
  • Excluir uma coluna.
    Você pode excluir uma coluna usando a cláusula de DROP. Excluir uma coluna significa que serão perdidos todos os dados desta.

In-Place ALTER TABLE

In-Place ALTER TABLE é usado para varias modificações que pode ser feitas a uma tabela. Como resultado, a tabela só é indisponível a usuários enquanto a definição da tabela é atualizada. Não é criada uma copia da tabela. Ao invés disso, são atualizadas as linhas de dados existentes à nova definição somente quando eles são atualizados. Novas linhas são inseridas com a nova definição. Esta característica é usada automaticamente.

Versões de Definição da Tabela (Table Definition Version)

O algoritmo In-Place ALTER TABLE cria uma nova versão da definição da tabela. Até 255 versões de definição da tabela são permitidas pelo servidor de banco de dados. Informações sobre as versões estão disponíveis usando a ferramenta oncheck:

# oncheck logix:ordem de –pT

Cada página de dados é associada com uma versão. Depois de uma In-Place ALTER TABLE, só são inseridas linhas novas em paginas de dados com a nova versão. Então, o mesmo extent pode ter versão 0, versão 1, versão 2 de paginas, por exemplo. Quando linhas em paginas antigas são atualizadas, todas as linhas na pagina de dados são atualizadas para a versão nova, se houver espaço suficiente. Se não houver espaço suficiente, a linha é apagada da pagina velha e inserida em uma pagina com a versão nova.

Quando uma tabela com varias versões é consultada, o Informix retorna os valores apropriados para qualquer coluna que não exista fisicamente. São retornados valores default para colunas criadas com um valor default, caso contrario são retornados como valores nulos.

Cada comando in-Place ALTER TABLE subseqüente na mesma tabela,  leva mais tempo para ser executado. Informix recomenda não mais que 50 a 60 execuções de Alters em uma tabela. Se você deseja eliminar as varias versões de uma tabela, force uma mudança imediata a todas as linhas. Por exemplo, use um simples comando UPDATE que fixa o valor de uma coluna a ela mesma.

Logging e ALTER TABLE

O comando ALTER TABLE cria registros nos logical logs mesmo quando o banco de dados não permite log. Com grandes tabelas e uma quantidade inadequada de espaço de logical log, pode ser difícil de evitar uma long trasaction. Usando um comando in-place ALTER TABLE, cada pagina de dados tem um registro no log no momento que a mudança acontece fisicamente (isto é, quando uma linha é inserida ou é atualizada). Os registros iniciais nos logical logs para o comando In-Place ALTER TABLE são muitos breves.

In-Place ALTER TABLE não sera usado se:

  • Adicionando ou Excluindo uma Coluna
    Se a coluna que é adicionada ou excluída é parte de uma expressão de fragmentação usada para fragmentar uma tabela, o In-Place ALTER TABLE não será usado. Se a coluna excluída for a referenciada por uma expressão de fragmentação de indice, um erro sera retornado.
  • Modificando uma coluna
    A operação de modificação esta sujeita a mais restrições. Em geral, a operação não será executada In-Place, se não puderem ser feitas todas as conversões entre os tipos de dados anteriores e os novos sem erros, ou se a distribuição de linhas por fragmentos mudar como resultado desta conversão. A coluna modificada não pode ser um VARCHAR.

Tamanho do Next Extent e Modo de Lock

ALTER TABLE ordem MODIFY NEXT SIZE 300;

ALTER TABLE ordem LOCK MODE (ROW);

O ALTER TABLE pode ser usado para mudar o tamanho da Next Extent ou modo de lock para uma tabela:

  • Voce pode mudar o tamanho da Next Extent. Isto não alterará qualquer Extent atual que foi alocado, só os futuros. O exemplo acima mostra a mudança do tamanho da Next Extent para 300 Kb.
  • Voce pode mudar o modo de Lock das linhas para PAGE ou ROW.

Tabelas sem Log

O Informix tem um tipo adicional de tabela, a Raw Permanent Table. Esta tabela não gera registro nos logical logs mesmo que o banco de dados tenha log. Antigamente somente tabelas temporarias poderiam estar sem log. Tabelas com o comportamento normal em um banco de dados com log são chamadas de Standart Permanent Tables.
Esta caracteristica permite o carregamento rápido de tabela muito grandes. Você pode usar qualquer ferramenta de carregamento, o High Performance Loader por exemplo, para carregar tabelas raw. Depois que o dado está carregando, um backup nível-0 deve ser executado. Isto prove um ponto de partida para restarbelecer dados se for necessario. Para evitar problemas de concorrencia e dados incompativeis, voce tem que mudar uma tabela raw em uma tabela standard antes de usar a tabela em uma transação.
Tabelas standart existentes podem ser convertidas temporariamente para tabelas raw para carregar rapidamente dados novos. Será solicitado a você excluir qualquer indice ou constraint, pois esses não são permitidos em uma tabela raw.

Renomeando Colunas, Tabelas e Bancos de Dados

RENAME COLUMN financeiro.paid_date TO data_pagamento;

RENAME TABLE stock TO estoque;

RENAME DATABASE logix TO totvs;

Se voce renomeia uma coluna que é referenciada por uma view no banco de dados, o texto da view na tabela de catalogo de sistema sysviews é atualizado com o novo nome. Se a coluna é referenciada em um check constraint, o texto do check constraint é atualizado na tabela de catalogo de sistema syschecks.
Quando uma tabela é renomeada, as referencias para a tabela dentro de qualquer view são alteradas.
O comando RENAME TABLE opera tanto em sinonimos como tambem tabelas. O nome da tabela é substituido se aparecer em uma definição de Triggers. Não é substituido se estiver dentro das ações da Trigger.

Em Stored Procedures

Os nomes de colunas e tabelas dentro do texto de uma stored procedure não são alterados pelo RENAME COLUMN ou RENAME TABLE. A stored procedure devolvera um erro quando esta se referir uma coluna ou tabela não existente.

Excluindo Tabelas e Banco de Dados

DROP TABLE ordem;

DROP DATABASE logix;

Quando você exclui uma tabela ou banco de dados, o espaço ocupado pelas tabelas é liberado e os dados não são mais acessiveis.

Residencia em Memoria

SET TABLE estado MEMORY_RESIDENT;

SET TABLE estado NON_RESIDENT;

O desempenho de uma query pode ser melhorada se o objeto da query não tiver que ser recuperado em disco. Esta caracteristica permite que um ou mais fragmentos de uma tabela, a tabela inteira ou um indice especifico tenha tratamento preferencial no Buffer Pool da memoria compartilhada. Suas paginas serão as ultimas a serem consideradas pela substituição de pagina quando um free buffer é solicitado pelo servidor de banco de dados.
Um usuario tem que ter permissão DBA na tabela para fixa-la em memoria. Para desativar esta caracteristica, use a sintaxe:

SET TABLE nome_tabela NON-RESIDENT;

O desligamento do servidor também desativara esta caracteristca.

O Utilitario DBSCHEMA

dbschema –d logix

dbschema –d logix –ss

dbschema –d logix –t ordens > schema.out

O utilitario DBSCHEMA é usado para produzir um arquivo de comandos SQL que contém os comandos CREATE TABLE, CREATE INDEX, GRANT, CREATE SYNONUM, E CREATE VIEW solicitados para reproduzir um banco de dados inteiro ou uma tabela selecionada. Você tem que especificar o banco de dados com opção –d.

Opções Adicionais:

-t tabname Só a tabela ou view serão incluidas. Especifique all em lugar de tabname para todas as tabelas.
-s synname Comandos CREATE SYNONYM do usuário (synname) especificado. Especifique all no lugar do synname para todos os sinonimos.
-p pname Imprime só os comandos GRANT para o usuario listado. Especifique all no lugar do pname para todos os usuarios.
-f stproc Exibe informações de distribuição. Especifique all no lugar do stproc para todas as store procedures.
-hd tabname Exibe informações de distribuição. Especifique all no lugar do tabname para todas as tabelas.
-ss Gera informação server-specific para a tabela informada, incluindo o modo de lock, tamanho de extent e nome de dbspace.
-r Gera CREATE e GRANT do role especificado ou entre com all para todos os roles.
outputfilename Envia a saida ao arquivo nomeado.

2 comentários: