High availability disaster recovery (HADR) monitoring

Monitoring is an integral part of setting up and maintaining your HADR setup. The Db2® monitoring interfaces provide a detailed picture of the configuration and health of your environment.

You can use a number of methods to monitor the status of your HADR databases. There are two preferred ways of monitoring HADR:
  • The db2pd command
  • The MON_GET_HADR table function
In a Db2 pureScale® environment, only these two interfaces return information about the HADR setup.
Alternatively, you can also use the following methods:
  • The GET SNAPSHOT FOR DATABASE command
  • The db2GetSnapshot API
  • Other supported snapshot administrative views and table functions. There are some views and functions that are deprecated or discontinued.
db2pd command

This command retrieves information from the Db2 memory sets. You can issue this command from either a primary database or a standby database. If you are using multiple standby databases and you issue this command from a standby, it does not return any information about the other standbys. If you issue this command from the primary, it returns information on all standbys

To view information about high availability disaster recovery for database HADRDB, you could issue the following command: 
   db2pd -db HADRDB -hadr
Assuming you issued that command from the primary, you would receive something like the following sample output:
  Database Member 0 -- Database HADRDB -- Active -- Up 0 days 00:23:17 -- 
Date 06/08/2011 13:57:23

                              HADR_ROLE = PRIMARY
                            REPLAY_TYPE = PHYSICAL
                          HADR_SYNCMODE = SYNC
                             STANDBY_ID = 1
                          LOG_STREAM_ID = 0
                             HADR_STATE = PEER
                    PRIMARY_MEMBER_HOST = hostP.ibm.com
                       PRIMARY_INSTANCE = db2inst
                         PRIMARY_MEMBER = 0
                    STANDBY_MEMBER_HOST = hostS1.ibm.com
                       STANDBY_INSTANCE = db2inst
                         STANDBY_MEMBER = 0
                    HADR_CONNECT_STATUS = CONNECTED
               HADR_CONNECT_STATUS_TIME = 06/08/2011 13:38:10.199479 (1307565490)
            HEARTBEAT_INTERVAL(seconds) = 25
                  HADR_TIMEOUT(seconds) = 100
          TIME_SINCE_LAST_RECV(seconds) = 3
               PEER_WAIT_LIMIT(seconds) = 0
             LOG_HADR_WAIT_CUR(seconds) = 0.000
      LOG_HADR_WAIT_RECENT_AVG(seconds) = 0.006298
     LOG_HADR_WAIT_ACCUMULATED(seconds) = 0.516
                    LOG_HADR_WAIT_COUNT = 82
  SOCK_SEND_BUF_REQUESTED,ACTUAL(bytes) = 0, 50772
  SOCK_RECV_BUF_REQUESTED,ACTUAL(bytes) = 0, 87616
              PRIMARY_LOG_FILE,PAGE,POS = S0000009.LOG, 1, 49262315
              STANDBY_LOG_FILE,PAGE,POS = S0000009.LOG, 1, 49262315
                    HADR_LOG_GAP(bytes) = 0
       STANDBY_REPLAY_LOG_FILE,PAGE,POS = S0000009.LOG, 1, 49262315
         STANDBY_RECV_REPLAY_GAP(bytes) = 0
                       PRIMARY_LOG_TIME = 06/08/2011 13:49:19.000000 (1307566159)
                       STANDBY_LOG_TIME = 06/08/2011 13:49:19.000000 (1307566159)
                STANDBY_REPLAY_LOG_TIME = 06/08/2011 13:49:19.000000 (1307566159)
           STANDBY_RECV_BUF_SIZE(pages) = 16
               STANDBY_RECV_BUF_PERCENT = 0
             STANDBY_SPOOL_LIMIT(pages) = 0
                   PEER_WINDOW(seconds) = 0
               READS_ON_STANDBY_ENABLED = Y
      STANDBY_REPLAY_ONLY_WINDOW_ACTIVE = N
MON_GET_HADR table function
If you issue this query on the primary, it will return information on all standbys. If you want to issue the MON_GET_HADR function against a standby database, be aware of the following points:
  • You must enable reads on standby on the standby.
  • If your HADR setup has multiple standby databases, the table function does not return any information about any other standbys.
  • HADR in a Db2 pureScale environment does not support reads on standby, so the table function cannot be used on a standby.
For example, you could issue the following query on the primary database:
db2 "select HADR_ROLE, STANDBY_ID, HADR_STATE, 
            varchar(PRIMARY_MEMBER_HOST,20) as PRIMARY_MEMBER_HOST,
            varchar(STANDBY_MEMBER_HOST,20) as STANDBY_MEMBER_HOST
     from table (mon_get_hadr(NULL))"
Sample output is as follows:
HADR_ROLE  STANDBY_ID  HADR_STATE  PRIMARY_MEMBER_HOST  STANDBY_MEMBER_HOST
---------  ----------  ----------  -------------------  -------------------
PRIMARY             1  PEER        hostP.ibm.com        hostS1.ibm.com     

1 record(s) selected.
GET SNAPSHOT FOR DATABASE command

This command collects status information and formats the output. The information that is returned is a snapshot of the database manager operational status at the time that you issued the command. HADR information is displayed in the output under the heading HADR status.

db2GetSnapshot API

This API collects database manager monitor information and writes it to a user-allocated data buffer. The information that is returned is a snapshot of the database manager operational status at the time that the API was called.

Other snapshot administrative views and table functions
You can use the following snapshot administrative views and table functions, which are not HADR specific and return a wider set of information, to query a subsection of the HADR information:
  • ADMIN_GET_STORAGE_PATHS
  • MON_GET_TRANSACTION_LOG
  • MON_GET_DATABASE
  • MON_GET_MEMORY_POOL
  • MON_GET_MEMORY_SET
  • MON_GET_TRANSACTION_LOG