IBM DB2 HADR Health

Version 1

    This template assesses the performance of an IBM DB2 HADR health by retrieving data from the built-in SNAPHADR view.

     

    Prerequisites: IBM DB2 installed on the target server. IBM DB2 ODBC Driver (This driver can be found on the IBM DB2 installation media).

    You should give SELECT or CONTROL privileges to the user on the SNAPHADR administrative view and the EXECUTE privilege on the SNAP_GET_HADR table function.


    Credentials: Database user name and password.

    Note: This template was tested on IBM DB2 v 9.7.


    Monitored Components

    Components without predetermined threshold values provide guidance such as "Use the lowest threshold possible" or "Use the highest threshold possible" to help you find a threshold appropriate for your application. For more information, see http://knowledgebase.solarwinds.com/kb/questions/2415.


    Note: If some or all components return nothing, it may indicate that the database is currently unavailable or it is not an HADR database. You should check your database configuration.

     

    HADR Role

         This component monitor returns the current HADR role of the target database.

         Possible values:
          1 – Database is not an HADR database.
          2 – Database is the primary HADR database.
          3 – Database is the standby HADR database.

         Note: By default, this monitor returns a value for the database named, TEST2. To change the database, replace TEST2 with your database name in the following SQL query:
          WHERE DB_NAME = 'TEST2'

     

    HADR Connect Status

         This component monitor returns the current HADR connect status of the target database.

         Possible values:
          1 – The database is connected to its partner node.
          2 – The database is connected to its partner node, but the connection is congested. A connection is congested when the TCP/IP socket connection between the primary-standby pair is still alive, but one end cannot send to the other end.
          3 – The database is not connected to its partner node.

         Note: By default, this monitor returns a value for the database named, TEST2. To change the database, replace TEST2 with your database name in the following SQL query:

       WHERE DB_NAME = 'TEST2'

     

    HADR Heartbeat

         This component monitor returns the number of missed heartbeats on the HADR connection. If the database is in HADR primary or standby role, this element indicates the health of the HADR connection. A heartbeat is a message sent from the other HADR database at regular intervals. If the value for this element is zero, no heartbeats have been missed and the connection is healthy. The higher the value, the worse the condition of the connection.

         Note: By default, this monitor returns a value for the database named, TEST2. To change the database, replace TEST2 with your database name in the following SQL query:

        WHERE DB_NAME = 'TEST2'

     

    HADR Log Gap

         This component monitor returns the running average of the gap between the primary Log sequence number (LSN) and the standby log LSN. The gap is measured in number of bytes. When a log file is truncated, the LSN in the next log file starts as if the last file were not truncated. This LSN hole does not contain any log data. Such holes can cause the log gap not to reflect the actual log difference between the primary and the standby.

         Note: By default, this monitor returns a value for the database named, TEST2. To change the database, replace TEST2 with your database name in the following SQL query:
        WHERE DB_NAME = 'TEST2'

     

    HADR State

         This component monitor returns the current HADR state of the target database.

         Possible values:
          1 – The primary and standby databases are connected and are in peer state.
          2 – The database is doing local catch-up.
          3 – The database is doing remote catch-up.
          4 – The database is waiting to connect to its partner to do remote catch-up.
          5 and 6 – The database is not connected to its partner database.

         Note: By default, this monitor returns a value for the database named, TEST2. To change the database, replace TEST2 with your database name in the following SQL query:
         WHERE DB_NAME = 'TEST2'

     

    HADR Sync Mode

         This component monitor returns the current HADR sync mode of the target database.

         Possible values:
          1 – The databases uses sync mode.
          2 – The databases uses near sync mode.
          3 – The databases uses async mode.
          4 – The databases uses super async mode.

         More information about sync modes can be found here: http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.config.doc%2Fdoc%2Fr0011445.html.


        Note: By default, this monitor returns a value for the database named, TEST2. To change the database, replace TEST2 with your database name in the following SQL query:

        WHERE DB_NAME = 'TEST2'

     

    Portions of this document were originally created by and are excerpted from the following sources:
    IBM, Copyright © 2012. All rights reserved. Available at
    http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0011478.htm.