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

Syntax

>>-MON_GET_HADR--(--member--)----------------------------------><

The schema is SYSPROC.

Table function 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.

Authorization

Default PUBLIC privilege

None

Example

• Example 1:

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

  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.

• Example 2:

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

  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.

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.

Information returned