Criando Standby no DB2

Criar um Standby no DB2 é simples.
Para esta demonstração, são utilizadas duas VMs com CentOS 5.5, e DB2 Enterprise Edition 9.7. As versões de Sistema Operacional e DB2 devem ser as mesmas nas duas máquinas.
Primeiramente, vamos criar um Banco de Dados. Vamos utilizar o SAMPLE, que já vem com a instalação do DB2, basta executar o script de criação.

[db2inst1@DB2-01 ~]$ db2sampl
Creating database "SAMPLE"...
Connecting to database "SAMPLE"...
Creating tables and data in schema "DB2INST1"...
Creating tables with XML columns and XML data in schema "DB2INST1"...
'db2sampl' processing complete.

Em seguida, vamos preparar o SAMPLE para que ele grave tudo nos Logs, e mantenha eles.

[db2inst1@DB2-01 ~]$ db2 UPDATE DB CFG FOR SAMPLE USING LOGINDEXBUILD ON
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@DB2-01 ~]$ db2 UPDATE DB CFG FOR SAMPLE USING INDEXREC RESTART
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@DB2-01 ~]$ db2 UPDATE DB CFG FOR SAMPLE USING LOGRETAIN ON
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

Agora, executamos um Backup e copiamos para o segundo servidor, que só tem o DB2 instalado, está sem Banco de Dados.

[db2inst1@DB2-01 ~]$ db2 BACKUP DB SAMPLE PARALLELISM 4 COMPRESS
Backup successful. The timestamp for this backup image is : 20101121203009
[db2inst1@DB2-01 ~]$ ls -l SAMPLE.0.db2inst1.NODE0000.CATN0000.20101121203009.001
-rw------- 1 db2inst1 db2iadm1 18370560 Nov 21 20:30 SAMPLE.0.db2inst1.NODE0000.CATN0000.20101121203009.001
[db2inst1@DB2-01 ~]$ scp SAMPLE.0.db2inst1.NODE0000.CATN0000.20101121203009.001 DB2-02:/home/db2inst1/
The authenticity of host 'db2-02 (192.168.56.102)' can't be established.
RSA key fingerprint is 16:8e:9e:3d:0e:12:d2:23:46:ca:94:f3:38:09:85:48.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'db2-02' (RSA) to the list of known hosts.
db2inst1@db2-02's password:
SAMPLE.0.db2inst1.NODE0000.CATN0000.20101121203009.001          100%   18MB   2.9MB/s   00:06

No segundo servidor, restauramos o Backup, e configuramos ele para ser o Standby. As configurações que já fizemos no Primary (por exemplo, LOGRETAIN) vem junto com o Backup, e já estão aplicadas no Standby.

[db2inst1@DB2-02 ~]$ ls -l SAMPLE.0.db2inst1.NODE0000.CATN0000.20101121203009.001
-rw------- 1 db2inst1 db2iadm1 18370560 Nov 21 20:32 SAMPLE.0.db2inst1.NODE0000.CATN0000.20101121203009.001
[db2inst1@DB2-02 ~]$ db2 RESTORE DB SAMPLE FROM /home/db2inst1/
DB20000I  The RESTORE DATABASE command completed successfully.
[db2inst1@DB2-02 ~]$ db2 UPDATE DB CFG FOR SAMPLE USING HADR_LOCAL_HOST 192.168.56.102
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@DB2-02 ~]$ db2 UPDATE DB CFG FOR SAMPLE USING HADR_LOCAL_SVC 55002
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@DB2-02 ~]$ db2 UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_HOST 192.168.56.101
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@DB2-02 ~]$ db2 UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_SVC 55001
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@DB2-02 ~]$ db2 UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_INST db2inst1
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@DB2-02 ~]$ db2 UPDATE DB CFG FOR SAMPLE USING HADR_TIMEOUT 120
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@DB2-02 ~]$ db2 UPDATE DB CFG FOR SAMPLE USING HADR_SYNCMODE NEARSYNC
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

Agora sim, iremos habilitar a replicação. Primeiramente, avisamos o Standby que ele é um Standby.

[db2inst1@DB2-02 ~]$ db2 START HADR ON DB SAMPLE AS STANDBY
DB20000I  The START HADR ON DATABASE command completed successfully.
[db2inst1@DB2-02 ~]$

Agora vamos configurar o Primary. Só fazemos estas configurações agora para que elas não fossem incluídas no Backup. Repare que a configuração do Primary é o oposto da do Standby.´

[db2inst1@DB2-01 ~]$ db2 UPDATE DB CFG FOR SAMPLE USING HADR_LOCAL_HOST 192.168.56.101
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@DB2-01 ~]$ db2 UPDATE DB CFG FOR SAMPLE USING HADR_LOCAL_SVC 55001
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@DB2-01 ~]$ db2 UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_HOST 192.168.56.102
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@DB2-01 ~]$ db2 UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_SVC 55002
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@DB2-01 ~]$ db2 UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_INST db2inst1
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@DB2-01 ~]$ db2 UPDATE DB CFG FOR SAMPLE USING HADR_TIMEOUT 120
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@DB2-01 ~]$ db2 UPDATE DB CFG FOR SAMPLE USING HADR_SYNCMODE NEARSYNC
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

Agora sim, iremos habilitar a replicação no Primary. Dessa vez avisamos ao Primary que ele é um Primary.

[db2inst1@DB2-01 ~]$ db2 START HADR ON DB SAMPLE AS PRIMARY
DB20000I  The START HADR ON DATABASE command completed successfully.

Pronto, a replicação está ativada. Vamos testa-la, criando uma tabela no Primary.

[db2inst1@DB2-01 ~]$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 9.7.1
You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG          for help on all of the CATALOG commands.
To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
For more detailed help, refer to the Online Reference Manual.
db2 => CONNECT TO SAMPLE
Database Connection Information
Database server        = DB2/LINUX 9.7.1
SQL authorization ID   = DB2INST1
Local database alias   = SAMPLE
db2 => CREATE TABLE TESTE (NUMEROS INT)
DB20000I  The SQL command completed successfully.
db2 => INSERT INTO TESTE VALUES (1)
DB20000I  The SQL command completed successfully.
db2 => INSERT INTO TESTE VALUES (2)
DB20000I  The SQL command completed successfully.
db2 => INSERT INTO TESTE VALUES (3)
DB20000I  The SQL command completed successfully.
db2 =>

Vamos verificar se a nova tabela foi replicada no Standby.

[db2inst1@DB2-02 ~]$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 9.7.1
You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG          for help on all of the CATALOG commands.
To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
For more detailed help, refer to the Online Reference Manual.
db2 => CONNECT TO SAMPLE
SQL1776N  The command is not supported on an HADR standby database or on an
HADR standby database with the current configuration or state.  Reason code =
"1".

Como é demonstrado acima, não é possível conectar ou cosultar o Standby, pois ele fica como um Banco em Recover.
Para utilizar o Standby, teremos que transforma-lo em Primary, e o Primary em Standby. Para esta operação, basta executar um comando no Standby.

db2 => TAKEOVER HADR ON DB SAMPLE
DB20000I  The TAKEOVER HADR ON DATABASE command completed successfully.
db2 => CONNECT TO SAMPLE
Database Connection Information
Database server        = DB2/LINUX 9.7.1
SQL authorization ID   = DB2INST1
Local database alias   = SAMPLE
db2 => SELECT * FROM TESTE
NUMEROS
-----------
1
2
3

Para desativar / reativar a replicação, os comandos também são simples. Lembre-se que agora o antigo Standby é o Primary.

[db2inst1@DB2-02 ~]$ db2 STOP HADR ON DB SAMPLE
DB20000I  The STOP HADR ON DATABASE command completed successfully.
[db2inst1@DB2-02 ~]$ db2 START HADR ON DB SAMPLE AS PRIMARY
DB20000I  The START HADR ON DATABASE command completed successfully.
[db2inst1@DB2-02 ~]$

3 comments

  1. Pingback: Tweets that mention Criando Standby no DB2 | Nerv -- Topsy.com
  2. Muito bom tutorial Portilho.

    Fiz aqui, testei, já até coloquei em produção e está ótimo.

    No meu caso eu tive que ativar o active standby, que o servidor standby fica aberto para consultas.
    Tive que por mais estes dois comandos:
    DB2SET -G DB2_HADR_ROS=YES
    DB2SET -G DB2_STANDBY_ISO=UR

    Como é uma variável e não um parâmetro é necessário executar nos dois servidores antes de fazer um db2start

  3. Obrigado Rafael!
    Cuidado pois o DB2_STANDBY_ISO=UR faz com que o Isolation Level do Standby seja Uncommitted Read: ou seja, se fizerem um DML na Produção, antes do COMMIT ele já estará visível no Standby. Acho isto um grande defeito do modo Read Only do Standby do DB2, mas para um relatório de “vendas do mês passado”, já está ótimo.

Leave a Reply

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.