Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2010
    Posts
    207

    Unanswered: Best way of testing HADR

    Hello Folks,

    I finally created my HADR and its running ( as the Control Center says )
    But:
    What is the best way of seeing whether the clients connect to the standby database.

    The problem is that when i enter a " db2 list active databases " after a takeover
    the database shows no clients connected ...

    when i do the takover back there are the clients connected again.

    Thanks in advance!

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you do a takeover and the applications are not connected to the new primary, you have a problem. To verify that you have actually completed the failover successfully, run this command:

    db2pd -d <db-name> -hadr

    If you don't see connections after a successful takeover, either the applications are not using Automatic Client Reroute, or you are not using a VIP (virtual IP that your system admin person moves with the HADR primary during a takeover via some script).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2010
    Posts
    207
    Hello Marcus,

    Thank you for the reply.

    I think its a problem of the applications because both servers dont use virtual IP Adresses. But how can i find out whether the applications use Automatic Client Rerouting or not?
    Thanks in advance

    DB_N00b

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    First, you have set up the Automatic Client Reroute configuration in the server db cfg.

    If your client uses the type 2 DB2 client, then it will automatically use the reroute the ip address/hostname; however, your application may need to have retry logic if it gets an HADR takeover SQL error code (if it is working properly it will pick up where it left off after a retry of the last statement). The auto reroute info is stored on the client db catalog (db2 list db directory--run this on the client) that it gets from the server upon first connection.

    If your application uses the DB2 universal driver (type 4) then it is a little more complicated because the application has to persist the automatic re-reroute information on its own that it gets from the server, and must also have retry logic after a takeover.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2010
    Posts
    207
    Hmm you mean to enter the command:

    "db2 update alternate server for database sample using hostname <Primary machine IP address> port <instance port>"

    I did that for each database but its not working ...
    any more ideas?

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by DB_N00b View Post
    Hmm you mean to enter the command:

    "db2 update alternate server for database sample using hostname <Primary machine IP address> port <instance port>"

    I did that for each database but its not working ...
    any more ideas?
    If you want help, you will have to supply more details about exactly what you did to configure HADR and automatic client reroute, and what extactly you did to test it. Words are not sufficient, exact commands, db cfg output, and client commands are required.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jan 2010
    Posts
    207
    Code:
    HADR database role                                      = PRIMARY                                                                              PRIMARY
     HADR local host name                  (HADR_LOCAL_HOST) = 6.213.48.223                                                                         6.213.48.223
     HADR local service name                (HADR_LOCAL_SVC) = hadr_fdbkdb                                                                          hadr_fdbkdb
     HADR remote host name                (HADR_REMOTE_HOST) = 6.213.48.221                                                                         6.213.48.221
     HADR remote service name              (HADR_REMOTE_SVC) = hadr_fdbkdb_int                                                                      hadr_fdbkdb_int
     HADR instance name of remote server  (HADR_REMOTE_INST) = db2inst1                                                                             db2inst1
     HADR timeout value                       (HADR_TIMEOUT) = 120                                                                                  120
     HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC                                                                             NEARSYNC
     HADR peer window duration (seconds)  (HADR_PEER_WINDOW) = 0                                                                                    0
    
     First log archive method                 (LOGARCHMETH1) = LOGRETAIN                                                                            TSM:MCDWH08
     Options for logarchmeth1                  (LOGARCHOPT1) =                                                                                
     Second log archive method                (LOGARCHMETH2) = OFF                                                                                  OFF
     Options for logarchmeth2                  (LOGARCHOPT2) =                                                                                
     Failover log archive path                (FAILARCHPATH) =                                                                                
     Number of log archive retries on error   (NUMARCHRETRY) = 5                                                                                    5
     Log archive retry Delay (secs)         (ARCHRETRYDELAY) = 20                                                                                   20
     Vendor options                              (VENDOROPT) =                                                                                
     Auto restart enabled                      (AUTORESTART) = ON                                                                                   ON
     Index re-creation time and redo index build  (INDEXREC) = SYSTEM                                                                               SYSTEM (RESTART)
     Log pages during index build            (LOGINDEXBUILD) = ON                                                                                   ON
     Default number of loadrec sessions    (DFT_LOADREC_SES) = 1                                                                                    1
     Number of database backups to retain   (NUM_DB_BACKUPS) = 20                                                                                   20
     Recovery history retention (days)     (REC_HIS_RETENTN) = 20                                                                                   20
     Auto deletion of recovery objects    (AUTO_DEL_REC_OBJ) = ON                                                                                   ON
    
     TSM management class                    (TSM_MGMTCLASS) =                                                                                      MCDWH08
     TSM node name                            (TSM_NODENAME) =                                                                                
     TSM owner                                   (TSM_OWNER) =                                                                                
     TSM password                             (TSM_PASSWORD) =                                                                                
    
     Automatic maintenance                      (AUTO_MAINT) = OFF                                                                                  OFF
       Automatic database backup            (AUTO_DB_BACKUP) = OFF                                                                                  OFF
       Automatic table maintenance          (AUTO_TBL_MAINT) = ON                                                                                   ON
         Automatic runstats                  (AUTO_RUNSTATS) = ON                                                                                   ON
           Automatic statement statistics  (AUTO_STMT_STATS) = OFF                                                                                  OFF
         Automatic statistics profiling    (AUTO_STATS_PROF) = OFF                                                                                  OFF
           Automatic profile updates         (AUTO_PROF_UPD) = OFF                                                                                  OFF
         Automatic reorganization               (AUTO_REORG) = OFF                                                                                  OFF
     Enable XML Character operations        (ENABLE_XMLCHAR) = YES                                                                                  YES
     WLM Collection Interval (minutes)     (WLM_COLLECT_INT) = 0

  8. #8
    Join Date
    Jan 2010
    Posts
    207
    For the ACR i entered the command
    Code:
    db2 update alternate server for database fdbkdb using hostname 6.213.48.223 port 50000

  9. #9
    Join Date
    Jan 2010
    Posts
    207
    When I enter the command

    db2 list active databases, i get this:

    Code:
                               Active Databases
    
    Database name                              = FDBKDB
    Applications connected currently           = 0
    Database path                              = /daten/db2inst1/NODE0000/SQL00003/
    
    Database name                              = JCRDB
    Applications connected currently           = 6
    Database path                              = /daten/db2inst1/NODE0000/SQL00002/
    
    Database name                              = LMDB
    Applications connected currently           = 10
    Database path                              = /daten/db2inst1/NODE0000/SQL00004/
    
    Database name                              = WPSDB
    Applications connected currently           = 18
    Database path                              = /daten/db2inst1/NODE0000/SQL00001/
    When I do the hadr takeover, the role switch is working from primary to standby, but no clients are connected anymore ...

    what can i do therefore?

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by DB_N00b View Post

    When I do the hadr takeover, the role switch is working from primary to standby, but no clients are connected anymore ...
    The DB2 client does not know that the connection to primary is broken until it attempts to use it. It is only at that time, I think, that it will try to re-establish the connection.

  11. #11
    Join Date
    Jan 2010
    Posts
    207
    Uh right - I havent thought of that.

    Thats because of the HADR Timeout, right?

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Test it out with your own own Command Line Interface connection to the database from a remote client. It only works automatically when you have a type 2 client, a type 4 client is more complicated and requires some changes to the application to persist the alternate server.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  13. #13
    Join Date
    Jan 2010
    Posts
    207
    works now thank you guys!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •