Visão geral

Este artigo fornece um guia para usar os novos aprimoramentos do DB2 9.7 que permitem fazer mudanças on-line no esquema do banco de dados. Mudança on-line significa que os objetos que estão sendo alterados permanecem disponíveis para acesso de gravação e leitura durante o processo de mudança.

Alguns dos novos recursos específicos incluem:

• As colunas podem ser renomeadas on-line usando a instrução ALTER TABLE.
• OR REPLACE foi incluído como opção em várias instruções CREATE.
• Foi incluído suporte a CREATE com erros para visualizações e funções SQL sequenciais.
• Foi ampliado o suporte a ALTER COLUMN SET DATA TYPE.
• As tabelas podem ser alteradas e movidas on-line usando a rotina ADMIN_MOVE_TABLE.

Pré-requisitos e requisitos do sistema

Este artigo foi escrito para administradores de banco de dados DB2. É necessário entender os conceitos básicos de espaços de tabelas, tabelas e colunas.

Para usar os exemplos deste artigo, é preciso ter o DB2 9.7 para Linux, UNIX e Windows. Consulte a seção Recursos para obter um link que poderá usar para fazer o download de uma versão de avaliação gratuita do DB2 9.7 para Linux, UNIX e Windows.

Preparando-se para usar os exemplos

Para usar os exemplos que demonstram o novo recurso de mudança de esquema on-line do DB2, é preciso primeiro criar um banco de dados de amostras que servirá como a infraestrutura necessária. Os exemplos usam o banco de dados SAMPLE do DB2. Se ainda não foi criado o banco de dados SAMPLE do DB2, faça isso seguindo as instruções no artigo "The SAMPLE database" encontrado no Centro de Informações do DB2 (consulte a seção Recursos para obter o link).

Depois de criar o banco de dados SAMPLE, execute estas etapas para criar a tabela necessária e os dados usados nos exemplos:

1. Use o seguinte comando para criar uma tabela baseada no catálogo do sistema que simula informações do cliente:
   
   
   Listagem 1. Criar a tabela CUSTOMER_INFO
   

   CREATE TABLE CUSTOMER_INFO( customer_id INTEGER NOT NULL, first_name VARCHAR(128) NOT NULL, last_name VARCHAR(128), address_street VARCHAR(128), address_city VARCHAR(128), address_state VARCHAR(25), address_country VARCHAR(30), age VARCHAR(2), customer_type VARCHAR(10), CONSTRAINT customer_id_pk PRIMARY KEY (customer_id) ) IN USERSPACE1;

   
   
2. Use o seguinte comando para preencher a tabela CUSTOMER_INFO com dados usando informações simuladas do catálogo do sistema:
   
   
   Listagem 2. Preencher a tabela CUSTOMER_INFO
   

   INSERT INTO customer_info SELECT ROW_NUMBER() OVER () as customer_id , RTRIM(a.tabschema) as first_name, RTRIM(a.tabname) as last_name, CAST(a.colno AS VARCHAR(3)) || ' ' || RTRIM(a.colname) as address_street, RTRIM(a.tabname) as address_city, RTRIM(a.TYPENAME) as address_state, RTRIM(a.TABSCHEMA) as address_country, CAST(CAST(sysfun.rand()*50 AS INTEGER)+1 AS VARCHAR(2)) as age, CASE WHEN MOD(CAST(sysfun.rand()*50 AS INTEGER),3)=0 THEN 'New' ELSE 'Existing' END AS type FROM syscat.columns a ORDER BY sysfun.rand();

   
   
3. Nesta etapa final, crie uma visualização e função na parte superior da tabela CUSTOMER_INFO.
   

   Observe a ordem em que os objetos são criados. Crie a visualização EXISTING_CUSTOMERS antes de criar a função FULL_NAME da qual a visualização depende. Isso é significativo porque, por padrão, isso faria a instrução CREATE OR REPLACE VIEW falhar com um erro SQL0440N.

   Contudo, a partir da versão 9.7 é possível configurar o DB2 para permitir a criação de objetos com certos tipos de erros, como objetos dependentes ausentes. Esse recurso pode ser útil para criação, projeto e modificação de objeto de banco de dados, porque permite ter uma ordem aleatória de comandos CREATE. Também permite verificar a sintaxe de novas visualizações e procedimentos sem criar os objetos dos quais eles dependem.

   Para ativar esse recurso, use o seguinte comando para alterar o parâmetro de configuração do banco de dados dinâmico AUTO_REVAL a fim de ter um valor de DEFERRED_FORCE.

   
   
   Listagem 3. Definindo o parâmetro de configuração AUTO_REVAL
   

   db2 update db cfg using AUTO_REVAL DEFERRED_FORCE

   
   

   Após alterar esse parâmetro, ao criar a visualização EXISTING_CUSTOMER, aparecerá um aviso SQL20480W e a visualização será inicialmente marcada como inválida. Contudo, se a função da qual a visualização é dependente existir da próxima vez que a visualização for usada, esta será revalidada automaticamente.

   Use os seguintes comandos para criar a nova visualização e função.Observe que os comandos aproveitam a sintaxe do novo CREATE OR REPLACE que é aplicável a funções, procedimentos, visualizações, módulos, alias, acionadores, variáveis e apelidos. Como o nome indica, essa sintaxe criar o objeto ou, se ele já existir, o substitui. Em outras palavras, no caso de objetos existentes, ela combina DROP e CREATE em um comando além de preservar os privilégios existentes concedidos ao objeto.

   
   
   Listagem 4. Criar a função e a visualização
   

   CREATEOR REPLACE VIEW existing_customers AS SELECT full_name(customer_id) AS full_name, address_city, address_state FROM customer_info WHERE customer_type='Existing'; CREATE OR REPLACE function full_name(p_customer_id INTEGER) RETURNS VARCHAR(100) return SELECT first_name || ', ' || last_name FROM customer_info WHERE customer_id=p_customer_id;

   
   

Voltar para parte superior

Fazendo modificações on-line na definição de tabela

Há dois aprimoramentos significativos no DB2 9.7 em relação a modificações on-line na definição de tabela:

• Primeiro, é possível renomear colunas on-line, enquanto a carga de trabalho está em execução com relação à tabela, sem interrupções para os usuários.
• Segundo, o DB2 9.7 ampliou seu suporte para a alteração de tipos de dados da coluna em tabelas existentes.

O exemplo a seguir demonstra como renomear uma coluna usando o comando ALTER TABLE enquanto mantém a tabela totalmente acessível:

ALTER TABLE customer_info RENAME COLUMN age TO customer_age ;

A opção ALTER COLUMN SET DATA TYPE na instrução ALTER TABLE foi estendido para dar suporte a todos os tipos compatíveis. Por exemplo, agora é possível alterar uma coluna definida com um tipo de dados de INTEGER para que tenha o tipo de dados de VARCHAR, ou então mudar o tipo de dados de TIMESTAMP para DATE. Consulte o artigo "Casting between data types" no Centro de Informações do DB2 para obter uma lista completa de tipos de dados compatíveis (consulte a seção Recursos para obter o link).

Durante a operação de ALTER TABLE com a opção ALTER COLUMN SET DATA TYPE, o DB2 executa uma validação completa para assegurar que os dados da coluna sejam compatíveis com o novo tipo de dados e que não haja truncamento, estouro nem qualquer outro tipo de erro. Os valores padrão da coluna também são verificados para assegurar que estejam de acordo com o novo tipo de dados. Se o tipo da coluna e o conteúdo dos dados forem compatíveis, o tipo de dados será alterado com sucesso. Caso contrário, o comando ALTER retornará um erro.

O exemplo a seguir demonstra como alterar o tipo de dados de uma coluna chamada customer_age de VARCHAR(2) para SMALLINT:

ALTER TABLE customer_info ALTER COLUMN customer_age SET DATA TYPE SMALLINT;

Na maioria dos casos, ALTER SET DATA TYPE exige reorganização da tabela (reorg) porque ele muda o formato físico da linha. Pode-se usar a rotina ADMIN_REVALIDATE_DB_OBJECTS para determinar automaticamente se a tabela exige reorg ou não:

CALL ADMIN_REVALIDATE_DB_OBJECTS('TABLE', 'DB2INST1', 'CUSTOMER_INFO');

Se for necessário acesso de gravação ao banco de dados disponível enquanto o tipo de dados da coluna é alterado, poderá usar a rotina ADMIN_MOVE_TABLE descrita na próxima seção.

Voltar para parte superior

Movendo uma tabela on-line

Uma nova rotina no DB2 9.7 chamada ADMIN_MOVE_TABLE permite mover automaticamente uma tabela de um espaço de tabela para outro ou alterar a definição de tipo de dados da coluna enquanto mantém a tabela totalmente acessível ao seus usuários.

Internamente, ADMIN_MOVE_TABLE cria uma cópia da tabela de origem. Os dados são copiados usando INSERT FROM CURSOR, que é padrão, ou LOAD se a opção correta for configurada. Enquanto a tabela está sendo movida, a rotina ADMIN_MOVE_TABLE também cria uma tabela de migração de dados que rastreia todas as mudanças na tabela de origem. As mudanças são rastreadas por meio de acionadores criados na tabela de origem. No fim da operação de movimentação, a rotina ADMIN_MOVE_TABLE brevemente bloqueia a tabela de origem no modo exclusivo, se atualiza com as atualizações ocorridas e substitui a tabela de origem pela de destino. A tabela de origem é descartada ou mantida com base em um dos parâmetros de entrada de ADMIN_MOVE_TABLE.

Além de alterar o local do espaço de tabela da tabela, também é possível usar a rotina ADMIN_MOVE_TABLE para alterar o armazenamento em cluster multidimensional, as chaves de particionamento, o particionamento de intervalo e o tipo de dados da coluna. ADMIN_MOVE_TABLE move acionadores e visualizações definidos na tabela de origem para a tabela de destino. Contudo, no momento ele não suporta a cópia de chaves estrangeiras, pais ou filhos. Portanto, se sua tabela de origem era pai ou filho em um relacionamento RI, é preciso capturar a definição das chaves estrangeiras e recriá-las após a tabela ser movida.

No exemplo a seguir, uma instrução SQL captura informações de chave estrangeira e gera instruções DROP:

SELECT 'ALTER TABLE ' || RTRIM(a.tabschema)||'.'||RTRIM(a.tabname) || 
       ' ADD CONSTRAINT '||a.constname|| 
       ' FOREIGN KEY (' || fk_colnames || ') REFERENCES ' || 
       RTRIM(reftabschema)||'.'||RTRIM(reftabname) || 
       ' ('||pk_colnames||') ON DELETE ' || 
       CASE deleterule WHEN 'A' THEN 'NO ACTION' WHEN 'C' THEN 'CASCADE' WHEN 'N' THEN 
       'SET NULL' WHEN 'R' THEN 'RESTRICT' END || ' ON UPDATE ' || 
       CASE updaterule WHEN 'A' THEN 'NO ACTION' WHEN 'R' THEN 'RESTRICT' END ||
       CASE enforced WHEN 'Y' THEN ' ENFORCED ' WHEN 'N' THEN ' NOT ENFORCED ' END || 
       ' QUERY OPTIMIZATION '|| 
       CASE enablequeryopt WHEN 'Y' THEN ' ENABLE ' WHEN 'N' THEN ' DISABLE 'END || 
       ';'
FROM syscat.references a, syscat.tabconst b
WHERE a.constname=b.constname;
     
SELECT  'ALTER TABLE ' || RTRIM(tabschema)||'.'||RTRIM(tabname) || 
        ' DROP FOREIGN KEY ' || constname || ';' 
FROM syscat.references 
WHERE (tabschema='DB2INST1' AND tabname='CUSTOMER_INFO') OR 
      (reftabschema='DB2INST1' AND reftabname='CUSTOMER_INFO');

O próximo exemplo demonstra como mover a tabela CUSTOMER_INFO para um novo espaço de tabela:

CREATE TABLESPACE new_ts;
   
CALL SYSPROC.ADMIN_MOVE_TABLE
               ('DB2INST1', 'CUSTOMER_INFO', 
               'NEW_TS', 'NEW_TS', 'NEW_TS',
               '', '', '', '', '', 'MOVE');
     
Result set 1
--------------

KEY                              VALUE                    
-------------------------------- ---------------------------
AUTHID                           DB2INST1                  
CLEANUP_END                      2009-06-11-13.24.45.438000
CLEANUP_START                    2009-06-11-13.24.45.407000
COPY_END                         2009-06-11-13.24.44.641000
COPY_OPTS                        OVER_INDEX,ARRAY_INSERT   
COPY_START                       2009-06-11-13.24.43.829000
COPY_TOTAL_ROWS                  6745                      
INDEX_CREATION_TOTAL_TIME        1                         
INDEXNAME                        CUSTOMER_ID_PK            
INDEXSCHEMA                      DB2INST1                  
INIT_END                         2009-06-11-13.24.43.797000
INIT_START                       2009-06-11-13.24.43.250000
REPLAY_END                       2009-06-11-13.24.45.250000
REPLAY_START                     2009-06-11-13.24.44.641000
REPLAY_TOTAL_ROWS                0                         
REPLAY_TOTAL_TIME                0                         
STATUS                           COMPLETE                  
SWAP_END                         2009-06-11-13.24.45.391000
SWAP_RETRIES                     0                         
SWAP_START                       2009-06-11-13.24.45.329000
VERSION                          09.07.0000                
 21 record(s) selected.

O próximo exemplo é um pouco mais complexo. Esse exemplo move a mesma tabela CUSTOMER_INFO, mas usa a opção LOAD em vez da opção INSERT FROM CURSOR, que é o padrão.

Vários fatores contribuem para tornar a opção LOAD mais rápida do que a opção INSERT FROM CURSOR:

• LOAD não registra as mudanças no destino por registro.
• LOAD é altamente paralelizada para processamento de CPU e E/S.
• LOAD executa gravações em grandes blocos na tabela de destino. Isso fornece eficiência adicional de E/S.

Observe que essa operação não é recuperável. Assim, se for preciso recuperar o banco de dados para o ponto no tempo entre o tempo de conclusão das movimentação da tabela e o backup seguinte, a tabela pode ser perdida. Para evitar perda de dados, use a opção KEEP. A opção KEEP força a rotina ADMIN_MOVE_TABLE a manter o original. Além disso, ao usar LOAD como opção de cópia, é necessário usar a opção FORCE. O requisito da opção FORCE serve de lembrete de que LOAD é uma operação não recuperável e que, se for desejada capacidade de recuperação, será preciso criar um backup.

  
    CALL SYSPROC.ADMIN_MOVE_TABLE
                    ('DB2INST1', 'CUSTOMER_INFO', 
                    'NEW_TS', 'NEW_TS', 'NEW_TS', 
                    '', '', '', '', 
                    'KEEP, COPY_USE_LOAD, FORCE', 'MOVE')


  Result set 1
  --------------

  KEY                              VALUE                    
  -------------------------------- ----------------------------
  AUTHID                           DB2INST1                  
  CLEANUP_END                      2009-06-11-13.36.43.360000
  CLEANUP_START                    2009-06-11-13.36.43.297000
  COPY_END                         2009-06-11-13.36.42.704000
  COPY_OPTS                        OVER_INDEX,LOAD,WITH_INDEXES
  COPY_START                       2009-06-11-13.36.40.563000
  COPY_TOTAL_ROWS                  6745                      
  INDEX_CREATION_TOTAL_TIME        0                         
  INDEXNAME                        CUSTOMER_ID_PK            
  INDEXSCHEMA                      DB2INST1                  
  INIT_END                         2009-06-11-13.36.40.266000
  INIT_START                       2009-06-11-13.36.39.172000
  ORIGINAL                         CUSTOMER_INFOAC61b#o      
  REPLAY_END                       2009-06-11-13.36.43.125000
  REPLAY_START                     2009-06-11-13.36.42.704000
  REPLAY_TOTAL_ROWS                0                         
  REPLAY_TOTAL_TIME                0                         
  STATUS                           COMPLETE                  
  SWAP_END                         2009-06-11-13.36.43.250000
  SWAP_RETRIES                     0                         
  SWAP_START                       2009-06-11-13.36.43.125000
  VERSION                          09.07.0000                

  22 record(s) selected.

O exemplo na Listagem 3 move a tabela muito mais rápido e salva a tabela original com o nome CUSTOMER_INFOAC61b#o.

O próximo exemplo demonstra como alterar o espaço de tabela não apenas para os dados da tabela, mas também para índices e LOBs. Também demonstra como alterar propriedades da tabela. A tabela CUSTOMER_INFO mantém os índices e LOBs no espaço de tabela USERSPACE1, enquanto os dados da tabela são movidos para o espaço de tabela NEW_TS. O exemplo converte a tabela para utilizar armazenamento em cluster multidimensional (MDC), usando a coluna CUSTOMER_TYPE como chave de armazenamento em cluster, e muda o tipo de dados da coluna CUSTOMER_AGE de volta para VARCHAR(2).

 
  CALL SYSPROC.ADMIN_MOVE_TABLE(
           'DB2INST1', 'CUSTOMER_INFO', 
           'NEW_TS', 'USERSPACE1', 'USERSPACE1', 
           'CUSTOMER_TYPE', '', '', 
           'customer_id INTEGER NOT NULL, 
           first_name VARCHAR(128) NOT NULL, 
           last_name VARCHAR(128), 
           address_street VARCHAR(128), 
           address_city VARCHAR(128), 
           address_state VARCHAR(25), 
           address_country VARCHAR(30), 
           customer_age VARCHAR(2), 
           customer_type VARCHAR(10)', 
           '', 'MOVE');

  Result set 1
  --------------

  KEY                              VALUE                    
  -------------------------------- --------------------------------
  AUTHID                           MAKSYMP                   
  CLEANUP_END                      2009-06-11-14.30.04.438000
  CLEANUP_START                    2009-06-11-14.30.04.422000 
  COPY_END                         2009-06-11-14.30.03.750000
  COPY_OPTS                        OVER_INDEX,ARRAY_INSERT   
  COPY_START                       2009-06-11-14.30.03.219000
  COPY_TOTAL_ROWS                  6745                      
  INDEX_CREATION_TOTAL_TIME        1                         
  INDEXNAME                        CUSTOMER_ID_PK            
  INDEXSCHEMA                      DB2INST1                  
  INIT_END                         2009-06-11-14.30.03.125000
  INIT_START                       2009-06-11-14.30.02.250000
  PAR_COLDEF                       customer_id INTEGER NOT NULL, 
                                   first_name VARCHAR(128) NOT NULL, 
                                   last_name VARCHAR(128), 
                                   address_street VARCHAR(128), 
                                   address_ci 
  REPLAY_END                       2009-06-11-14.30.04.344000
  REPLAY_START                     2009-06-11-14.30.03.750000
  REPLAY_TOTAL_ROWS                0                         
  REPLAY_TOTAL_TIME                0                         
  STATUS                           COMPLETE                  
  SWAP_END                         2009-06-11-14.30.04.407000
  SWAP_RETRIES                     0                         
  SWAP_START                       2009-06-11-14.30.04.360000
  VERSION                          09.07.0000 
  

Voltar para parte superior

Conclusão

Este artigo descreveu:

• Como usar a sintaxe CREATE OR REPLACE
• Como criar certos objetos de banco de dados com erros
• Como renomear colunas e alterar o tipo de dados da coluna
• Como usar a rotina ADMIN_MOVE_TABLE para mover tabelas e modificar propriedades da tabela

O novo recurso de mudança de esquema on-line do DB2 9.7 ajuda os DBAs e desenvolvedores de aplicativos a reduzir significativamente o tempo de inatividade do banco de dados associado a indisponibilidades planejadas. Esses recursos também simplificam o gerenciamento dos objetos de esquema e melhoram sua produtividade geral.

Recursos

Aprender

• Na área do DB2 para Linux, UNIX e Windows no developerWorks, obtenha os recursos necessários para melhorar suas qualificações no DB2 9.7.
  
  
• "O artigo "SAMPLE database" do Centro de Informações do DB2 para Linux, UNIX e Windows fornece instruções sobre como criar o banco de dados SAMPLE usado nos exemplos deste artigo.
  
  
• O artigo "Casting between data types" do Centro de Informações do DB2 para Linux, UNIX e Windows fornece uma lista completa de tipos de dados compatíveis suportados pela opção ALTER COLUMN SET DATA TYPE na instrução ALTER TABLE.
  
  
• Encontre informações adicionais que descrevem como usar o DB2 no Centro de Informações do DB2 para Linux, UNIX e Windows.
  
  
• Navegue pela página do livraria de tecnologia para obter livros sobre estes e outros tópicos técnicos.
  
  

Obter produtos e tecnologias

• Faça download de uma versão de teste gratuita do DB2 9.7 para Linux, UNIX e Windows.
  
  

Discutir

• Participar do fórum de discussão.
  
  

Sobre o autor

Maksym Petrenko faz parte da equipe DB2 Beta Enablement no Laboratório da IBM em Toronto. Ele ajuda os primeiros usuários a mover seus aplicativos para o código base mais recente e superior do DB2. Maksym trabalha com o DB2 desde 2001 como desenvolvedor, analista de suporte técnico e consultor de serviços de laboratório. Sua experiência inclui suporte a clientes com problemas de instalação, configuração, desenvolvimento de aplicativo e desempenho relacionados a bancos de dados DB2 em plataformas Windows, Linux e UNIX. Maksym é DB2 Advanced Database Administrator e DB2 Application Developer certificado.

Classificar este artigo

Comentários

Voltar para parte superior

Índice

• Visão geral
• Pré-requisitos e requisitos do sistema
• Preparando-se para usar os exemplos
• Fazendo modificações on-line na definição de tabela
• Movendo uma tabela on-line
• Conclusão
• Recursos
• Sobre o autor
• Comentários

Conheça a IBM da sua cidade

A IBM está mais perto do que você imagina!

---

Saiba mais