MON_GET_HADR table function - Returns high availability disaster recovery (HADR) monitoring information

Authorization

Default PUBLIC privilege

None

Syntax

The schema is SYSPROC.

Routine parameters

member

An input argument of type INTEGER that specifies a member number. Returned rows represent log streams being processed by the member. Specify -1 for the current database member, or -2 for all active database members. If the null value is specified, -1 is set implicitly.

Information returned

Usage notes

HADR pair view

Certain fields are applicable to primary or standby only. For example, PEER_WAIT_LIMIT is applicable only to primary, STANDBY_RECV_BUF_SIZE, STANDBY_SPOOL_LIMIT, READS_ON_STANDBY_ENABLED are applicable only to standby. When this kind of information is reported, the database currently in the role is used (which may be the remote database), rather than the local database. For example, PEER_WAIT_LIMIT seen on a standby database is the value configured on the primary database, not the local config of standby database (which will be used only when the standby turns into primary).

Information about remote database

Primary and standby exchange monitoring information via heartbeat messages. Therefore information about the remote database can be slightly out of date. See heartbeat interval (reported in table function) to estimate timeliness of information (network latency can add additional delay). If a database has never connected to its partner database since activation, information about remote database may be returned as SQL NULL to indicate "unknown".

Log shipping channel end points

The end points for a log shipping channel is uniquely identified by host, instance and member:

• Primary side: PRIMARY_MEMBER_HOST, PRIMARY_INSTANCE, PRIMARY_MEMBER
• Standby side: STANDBY_MEMBER_HOST, STANDBY_INSTANCE, STANDBY_MEMBER

Until a connection is made, end point information of remote end may not be available. When information not available, empty strings will be returned for host and instance names and zero returned for member ID. In addition when in a Db2 Enterprise Server Edition environment, 0 is always returned for the member ID.

Note on unit of time duration

Per monitor table function convention, all MON_GET_HADR time duration fields use milliseconds as unit. For those fields reflecting a configuration parameter (such as HADR_TIMEOUT, HADR_PEER_WINDOW) whose unit in configuration is seconds, the number returned by MON_GET_HADR table function will be different from the number used in db2 get/update db cfg command, and the number returned by SYSIBMADM.DBCFG admin view or SYSPROC.DB_GET_CFG() table function. For example, for a 60 second HADR_TIMEOUT value, MON_GET_HADR will return 60000, while the configuration oriented interfaces will return 60. To convert the millisecond number to second, use column_name/1000 in your query.

Usage during takeover

During takeover, there may be a period when clients cannot connect to either primary or standby database. The recommended monitoring method during takeover is db2pd -hadr.

Examples

1. 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))

   The following is an example of output from this query.

   HADR_ROLE     STANDBY_ID HADR_STATE              PRIMARY_MEMBER_HOST 
   ------------- ---------- ----------------------- --------------------
   PRIMARY                1 PEER                    hostP.ibm.com       
   PRIMARY                2 REMOTE_CATCHUP          hostP.ibm.com       
   PRIMARY                3 REMOTE_CATCHUP          hostP.ibm.com       
    
   STANDBY_MEMBER_HOST   
   --------------------  
   hostS1.ibm.com        
   hostS2.ibm.com        
   hostS3.ibm.com        
   
   3 record(s) selected.

   Query is issued to a primary database with 3 standbys in which 3 rows are returned. Each row represents a primary-standby log shipping channel. The HADR_ROLE column represents the role of the database to which the query is issued. Therefore it is PRIMARY on all rows.

2. 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))

   The following is an example of output from this query.

   HADR_ROLE     STANDBY_ID HADR_STATE              PRIMARY_MEMBER_HOST 
   ------------- ---------- ----------------------- --------------------
   STANDBY                0 PEER                    hostP.ibm.com       
    
   STANDBY_MEMBER_HOST  
   -------------------- 
   hostS1.ibm.com       
   
   1 record(s) selected.

   Query is issued to a standby database with reads on standby enabled. Standby only knows about its own primary. Only one row is returned even if the standby is part of a multiple standby system. STANDBY_ID is always zero when query is issued to a standby.

3. The following example is for HADR in a Db2 pureScale® environment only. The environment for this example has three member clusters. On the primary, member 0 is assisting member 1. Standby member 0 is the replay member.

   • With member ID 0, the HADR information for log stream 0, and the assisted log stream (log stream 1) is output:

     SELECT LOG_STREAM_ID, PRIMARY_MEMBER, STANDBY_MEMBER, HADR_STATE, HADR_FLAGS
        FROM TABLE (MON_GET_HADR(0))
     
     LOG_STREAM_ID PRIMARY_MEMBER STANDBY_MEMBER HADR_STATE     HADR_FLAGS
     ------------- -------------- -------------- -------------- -----------------------
     0             0              0              PEER                    
     1             0              0              REMOTE_CATCHUP ASSISTED_REMOTE_CATCHUP

   • With member ID 1, the HADR information for log steam 1 is output:

     SELECT LOG_STREAM_ID, PRIMARY_MEMBER, STANDBY_MEMBER, HADR_STATE, HADR_FLAGS
        FROM TABLE (MON_GET_HADR(1))
     
     LOG_STREAM_ID PRIMARY_MEMBER STANDBY_MEMBER HADR_STATE      HADR_FLAGS
     ------------- -------------- -------------- --------------- ----------
     1             1              0              DISCONNECTED    

   • For log stream ID 2, the HADR information for log stream 2 is output:

     SELECT LOG_STREAM_ID, PRIMARY_MEMBER, STANDBY_MEMBER, HADR_STATE, HADR_FLAGS
        FROM TABLE (MON_GET_HADR(2))
     
     LOG_STREAM_ID PRIMARY_MEMBER STANDBY_MEMBER HADR_STATE     HADR_FLAGS
     ------------- -------------- -------------- -------------- ----------
     2             2              0              PEER           

   • When specifying -2 as member ID, HADR information on all log streams is reported. For assisted remote catchup, only the record from the assisting member is returned, and HADR_FLAGS indicates that it is in an assisted remote catchup state.

     SELECT LOG_STREAM_ID, PRIMARY_MEMBER, STANDBY_MEMBER, HADR_STATE, HADR_FLAGS
        FROM TABLE (MON_GET_HADR(-2))
     
     LOG_STREAM_ID PRIMARY_MEMBER STANDBY_MEMBER HADR_STATE       HADR_FLAGS
     ------------- -------------- -------------- ---------------- -----------------------
     0             0              0              PEER             
     1             0              0              REMOTE_CATCHUP   ASSISTED_REMOTE_CATCHUP
     2             2              0              PEER