DB2 Version 10.1 for Linux, UNIX, and Windows

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

You can also use the following methods, but starting in Version 10.1, they are deprecated, and they might be removed in a future release:

The GET SNAPSHOT FOR DATABASE command
The db2GetSnapshot API
The SNAPHADR administrative view
The SNAP_GET_HADR table function
Other snapshot administrative views and table functions

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 mode 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.
Even if your HADR setup is in multiple standby mode, the table function does not return any information about any other standbys.

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.

SNAPHADR administrative view and SNAP_GET_HADR table function

This administrative view and this table function return information about HADR from a database snapshot, in particular, the HADR logical data group.

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
SNAPDB
SNAPDB_MEMORY_POOL
SNAPDETAILLOG
SNAP_GET_DB
SNAP_GET_DB_MEMORY_POOL