Determining the HADR standby database state

The state of a Db2® high availability disaster recovery (HADR) standby database determines what operations it can perform.

Procedure

To determine the state of an HADR standby database in a primary-standby HADR database pair:
  • From the primary database or a standby database, issue the db2pd command with the -hadr parameter and check the HADR_STATE field:
    • If you issue the command from the primary database, the command returns a set of data for each standby in your HADR setup.
    • If you issue the command from a standby database, the command returns only a single set of data because the standby cannot obtain information about other standbys.
  • Issue a query that uses the MON_GET_HADR table function to determine the HADR_STATE field on the primary database or a read-enabled standby database:
    • If you issue the query on the primary database, the table function returns a row of data for each standby in your HADR setup.
    • If you issue the query on the standby database, the table function returns only a single row of data because a standby cannot obtain information about other standbys.
    In a Db2 pureScale® environment, you can use this table function on the primary only. You can specify any single member, the current member, or all members. The returned rows represent log streams that are being processed by the member.

Example

Example with one HADR standby
A DBA with an HADR setup with a single standby issues the MON_GET_HADR table function from the primary to query the state of the HADR databases:
select HADR_STATE from table (mon_get_hadr(NULL))
The following information is returned, showing that the HADR pair is in peer state:
HADR_STATE              
-------------------- 
PEER                    

 1 record(s) selected.
Example with one HADR standby in a Db2 pureScale environment
A DBA with an HADR setup with three-member clusters (members 0, 1, and 2) issues the MON_GET_HADR table function from the primary to query the state of the HADR databases on all members:
select LOG_STREAM_ID, PRIMARY_MEMBER, STANDBY_MEMBER, HADR_STATE, HADR_FLAGS
from table (mon_get_hadr(-2))
The following information is returned:
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                    

 3 record(s) selected.
This output indicates that member 1 is in assisted remote catchup state and that member 0 is the assisting member. If the DBA issues the table function with a member argument of member 1, the result is as follows:
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
Example with multiple HADR standbys
A DBA with an HADR setup with multiple standbys issues the MON_GET_HADR table function from the primary to query the state of the HADR databases:
select STANDBY_ID, HADR_STATE from table (mon_get_hadr(NULL))
The following information is returned:
STANDBY_ID HADR_STATE              
---------- -------------- 
1          PEER                    
2          REMOTE_CATCHUP          
3          REMOTE_CATCHUP          

 3 record(s) selected.
The principal standby, which always has a STANDBY_ID value of 1, is in peer state. The auxiliary standbys, which have STANDBY_ID values of 2 and 3, can never be in peer state.