DB2 Version 9.7 for Linux, UNIX, and Windows

Monitoring high availability disaster recovery (HADR)

There are a number of methods you can use to monitor the status of your (high availability disaster recovery ) HADR databases.

Commands and APIs

db2pd utility

This utility retrieves information from the DB2® memory sets. For example, to view information about high availability disaster recovery for database MYDB, issue the following:

   db2pd -db dbname -hadr

GET SNAPSHOT FOR DATABASE command

This command collects status information and formats the output. The information returned represents a snapshot of the database manager operational status at the time the command was issued. HADR information appears in the output under the heading HADR status.

db2GetSnapshot API

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

Note: Because HADR configuration parameters are not dynamic, you must use one of these methods if you want to determine which HADR configuration parameters are currently effective. If you use the GET DATABASE CONFIGURATION command, new value for the HADR configuration parameters are visible immediately even though they will not take effect until the database is stopped and restarted.

Snapshot administrative views and table functions

Use snapshot administrative views and table function to access a subsection of the snapshot information provided by the GET SNAPSHOT command or db2GetSnapshot API. The following snapshot administrative views provide information equivalent to the GET SNAPSHOT FOR DATABASE command:

SNAPDB_MEMORY_POOL
SNAPDETAILLOG
SNAPHADR
SNAPSTORAGE_PATHS
SNAPDB

The following table functions provide information equivalent to the GET SNAPSHOT FOR DATABASE command:

SNAP_GET_DB_MEMORY_POOL
SNAP_GET_DETAILLOG_V91
SNAP_GET_HADR
SNAP_GET_STORAGE_PATHS
SNAP_GET_DB_V97

You can access the information in any of these views or table functions using a SELECT statement.

HADR-specific details

Some of the details you may want to know about include:

Database role

The current role of a database is indicated by the database configuration parameter hadr_db_role. Valid values for this configuration parameter are PRIMARY, STANDBY, or STANDARD (the latter indicates the database is not an HADR database). To determine the role, you can use the GET DATABASE CONFIGURATION command or GET SNAPSHOT FOR DATABASE command (but only when the database is active).

Standby database status

The standby database status is indicated by the db_status monitor element. The relevant values for HADR are STANDBY and ACTIVE_STANDBY (the latter indicates that the standby database is read enabled). To determine the status, you can use the GET SNAPSHOT FOR DATABASE or db2pd command, the SNAPDB administrative view, or the SNAP_GET_DB table functions, as follows:

db2 get snapshot for all on hadrdb | grep "Database status"

Database status                            = Active Standby

db2pd -db hadrdb 

Database Partition 0 -- Database HADRDB -- Active Standby -- Up 0 days 01:24:01

SELECT SUBSTR(DB_NAME, 1, 20) AS DB_NAME, DB_STATUS FROM SYSIBMADM.SNAPDB

DB_NAME              DB_STATUS
-------------------- ----------------
HADRDB               ACTIVE_STANDBY

SELECT SUBSTR(DB_NAME, 1, 20) AS DB_NAME, DB_STATUS 
FROM  table(SNAP_GET_DB_V97('hadrdb', 0))

DB_NAME              DB_STATUS
-------------------- ----------------
HADRDB               ACTIVE_STANDBY

Note: Other SNAP_GET_DB table functions will return "4" or "5" instead of "ACTIVE_STANDBY" or "STANDBY", respectively.