db2top HADR Screen

HADR stands for High Availability Disaster Recovery. It is a technology that protects your database against data loss and allows it to be highly available even when you have a partial or complete site failure. It does this by replicating data changes from one database, called the primary, to another, called the standby. When your DB2 database is configured for HADR, db2top has some additional monitoring functionality specifically designed for such a configuration. Today we will look at the HADR screen in db2top.

To see the HADR screen at all, you need to have your database set up in an HADR configuration. The DB2 for LUW documentation on Initializing high availability disaster recovery (HADR) describes how you can do this. The following is what you would see if you launch the HADR screen (by holding down the Shift key and pressing the ‘A’ key) for a database that is not set up in an HADR configuration:

When you setup your database for HADR, you will find that db2top can be launched for both the primary and standby databases. On the primary, the db2top welcome screen shows the usual values for Status, such as “Active”. On the standby, the db2top welcome screen shows a Status of “Active Standby”.

The HADR screen consists of 15 attributes and their values organized into multiple columns depending on the width of your screen. On an 80 column-wide screen, there are two columns of attributes and their values as shown below for the primary:

On wider screens, such as the 109 column-wide screen shown below, the same attributes are split amongst a larger number of columns:

The tables below contain the 15 attributes, shown three at a time:

Name	Definition
Role	The current high availability disaster recovery (HADR) role of the database (hadr_role). It can have one of the following values: Standard (SQLM_HADR_ROLE_STANDARD) Primary (SQLM_HADR_ROLE_PRIMARY) Standby (SQLM_HADR_ROLE_STANDBY)
Status	The current high availability disaster recovery (HADR) connection status of the database (hadr_connect_status). Connected (SQLM_HADR_CONN_CONNECTED) Congested (SQLM_HADR_CONN_CONGESTED) Disconnected (SQLM_HADR_CONN_DISCONNECTED)
Time	Shows one of the following: high availability disaster recovery (HADR) connection time, HADR congestion time, or HADR disconnection time (hadr_connect_time).

Name	Definition
State	The current high availability disaster recovery (HADR) state of the database (hadr_state). It can have one of the following values: Disconnected (SQLM_HADR_STATE_DISCONNECTED) Local catchup (SQLM_HADR_STATE_LOCAL_CATCHUP) Rem catchup pending (SQLM_HADR_STATE_REM_CATCH_PEND) Remote catchup (SQLM_HADR_STATE_REM_CATCHUP) Peer (SQLM_HADR_STATE_PEER) Unknown
Mode	The current high availability disaster recovery (HADR) synchronization mode of the database (hadr_syncmode). It can have one of the following values: Asynchronous (SQLM_HADR_SYNCMODE_ASYNC) Near synchronous (SQLM_HADR_SYNCMODE_NEARSYNC) Synchronous (SQLM_HADR_SYNCMODE_SYNC)
Log gap	The running average of the gap between the primary Log sequence number (LSN) and the standby log LSN (hadr_log_gap). The gap is measured in number of bytes.

Name	Definition
Heartbeat	Number of missed heartbeats on the high availability disaster recovery (HADR) connection (hadr_heartbeat). If the database is in HADR primary or standby role, this element indicates the health of the HADR connection.
Timeout	This parameter specifies the time (in seconds) that the high availability disaster recovery (HADR) process waits before considering a communication attempt to have failed (hadr_timeout).
Log Writes	The number of log pages written to disk by the logger (log_writes).

Name	Definition
Log IOs	The number of I/O requests issued by the logger for writing log data to the disk (num_log_write_io).
Log Buff	The number of times an agent reads log data from the buffer (num_log_data_found_in_buffer). Reading log data from the buffer is preferable to reading from the disk because the latter is slower.
Log wtime	The average elapsed time per write spent by the logger writing log data to the disk. (log_write_time / log_writes)

Name	Definition
Rf type	The type of rollforward in progress (rf_type). It can have one of the following values: Database (SQLM_ROLLFWD_TYP_DB) Onl tbs (SQLM_ROLLFWD_TYP_TSPACE) None (SQLM_NO_ROLLFWD) If the Role is not “Primary”, “na” is shown.
Rf status	The status of the recovery (rf_status). It can have one of the following values: Redo Undo Success Error User required If the Role is not “Primary”, “na” is shown.
Rf tms	The timestamp of the last committed transaction (rf_timestamp).

Below the 15 attributes is a table that shows data specific to the primary and to the standby. There are six rows of data and they are labeled as follows:

Name	Definition
Host	The high availability disaster recovery (HADR) host name (hadr_local_host or hadr_remote_host). The value is displayed as a host name string or an IP address string such as “1.2.3.4”.
Service	The HADR TCP service (hadr_local_service or hadr_remote_service). This value is displayed as a service name string or a port number string.
Instance	The HADR instance name (server_instance_name or hadr_remote_instance).
Logfile	The name of the current log file on the HADR database (hadr_primary_log_file or hadr_standby_log_file).
Log PAGE	The page number in the current log file indicating the current log position on the HADR database (hadr_primary_log_page or hadr_standby_log_page). The page number is relative to the log file. For example, page zero is the beginning of the file.
Log LSN	The current log position of the primary HADR database (hadr_primary_log_lsn or hadr_standby_log_lsn). Log sequence number (LSN) is a byte offset in the database’s log stream.

The deltas/actuals key (‘k’), the active/all key (‘i’) and extended mode on/off key (‘X’) have no effect on the HADR screen. The HADR feature is currently not supported in multi-partition instance environments, so the global/local key (‘G’) does not apply either.