IBM DB2 HADR Health

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

This template was tested on IBM DB2 v 9.7.

Prerequisites

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

Grant 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.

Configuration for Orion agent for Linux monitoring

Here are prerequisites for the following steps:

  • IBM DB2 is installed on a Linux-based computer.
  • A user account exists on the server and in the IBM DB2 database.

To run commands, you will need the instance users for the database. The instance user connects to the DB and should have the sqllib directory set up.

  1. Log into the Linux-based computer with administrator privileges.

  2. Use the following command to install the unixODBC package:

    yum -y install unixODBC

  3. Update the ODBC config files by adding the following settings to /etc/odbcinst.ini:
    [DB2]
    Description = DB2 Driver
    Driver = /opt/ibm/db2/V10.5/lib64/libdb2.so
    FileUsage = 1
    DontDLClose = 1

    Verify that libdb2.so has the correct file path.

  4. Add the following settings to /etc/odbc.ini:
    [dbname]
    Driver = DB2

    The drivers should match in both files.

  5. Connect locally to the database with the instance user credentials. For example:
    isql -v dbname db2inst1 Password1
  6. Log into the DB2 database with the instance user credentials and open the DB2 command line tool.
  7. Enable the required following setting used by the SAM IBM DB2 template:
    UPDATE DBM CFG USING DFT_MON_BUFPOOL ON
    UPDATE DBM CFG USING DFT_MON_LOCK ON
    UPDATE DBM CFG USING DFT_MON_STMT ON
    UPDATE DBM CFG USING DFT_MON_UOW on
  8. Use the GRANT EXECUTE ON FUNCTION statement to grant privileges for the SYSPROC.MON_GET_TABLE function to user, where user is the username that SAM as shown in this example:
    GRANT EXECUTE ON FUNCTION SYSPROC.MON_GET_TABLE TO user

Configure DB2 on AIX devices

Prerequisites

Important: Do not install the 64-bit ODBC drivers; use the 32-bit drivers instead to support Python distributed on the Orion agent.

  • Install IBM DB2 ODBC Driver (© 2018 IBM Corp.; https://www-01.ibm.com; link acquired on August 2, 2018) on AIX devices.
    The commands use the following driver bundle: v11.1.1fp1_aix64_odbc_cli_32.tar.gz.
  • Install the ODBC driver manager package and its dependencies from www.perzl.org/aix (Obtained from www.perzl.org/aix, on February 1, 2018). Dependencies include:
    • info-6.4-1.aix5.1.ppc.rpm

    • libiconv-1.15-1.aix5.1.ppc.rpm

    • libtool-ltdl-1.5.26-2.aix5.1.ppc.rpm

    • readline-7.0-3.aix5.1.ppc.rpm

    • libffi-3.1.1.aix5.1.ppc.rpm (not a direct dependency, but needed by the SAM monitor plugin)

  • The gunzip utility, which is part of the AIX 5L™ default installation setup. You can also download gzip for AIX 5L from http://www.ibm.com/servers/aix/products/aixos/linux/rpmgroups.html (© 2018 IBM Corp.; https://www-01.ibm.com; link acquired on August 2, 2018)
  • Verify that the IBM rpm.rte package exists in the /usr/bin/rpm directory

Configure ODBC for AIX

  1. Log into the AIX-based computer with administrator privileges.

  2. Install the RPM packages.

    Run the following command as root: rpm -ivh unixODBC-2.3.4-1.aix5.1.ppc.rpm

    To install all dependencies, run the following command that expects the dependencies are in the same directory:.
    rpm -ivh *.rpm

  3. Install the DB2 ODBC driver. The example code creates a directory odbc_cli_32 in the /opt directory.

    gunzip v11.1.1fp1_aix64_odbc_cli_32.tar.gz

    tar -x -f v11.1.1fp1_aix64_odbc_cli_32.tar -C /opt

  4. Add the following lines to /opt/freeware/etc/odbcinst.ini:
    [DB2]
    Description = DB2 Driver
    Driver = /opt/odbc_cli_32/clidriver/lib/libdb2.so
    FileUsage = 1
    DontDLClose = 1

    Verify libdb2 has the correct file path.

  5. Add the following settings to /opt/freeware/etc/odbc.ini:

    [dbname]

    Description = IBM DB2 ODBC data source

    Driver = DB2

    DMEnvAttr = SQL_ATTR_UNIXODBC_ENVATTR={/opt/odbc_cli_32/clidriver}

    • In [dbname], enter the name of the database to be monitored.
    • DMEnvAttr value depends on the installation directory for the DB2 ODBC CLI driver.
  6. Run the following command as root. It allows the unixODBC manager driver to find the DB2 ODBC file.

    ar -X32 x libdb2.a shr.o

    mv shr.o libdb2.so

  7. Verify that the SAM python plugin can connect to the DB2 database to monitor it. Save the following script as verifyodbc.py.
    The script is written for Orion agents in the default location /opt/SolarWinds/Agent.

    Important: If you are not using the default port, add server hostname and port number to the connection string.

    import sys

    sys.path.insert(0, "/opt/SolarWinds/Agent/bin/Plugins/SharedPythonLibs")

    import pypyodbc

    # Set Database, Uid, and Pwd to the database name, username, and user's password to access said database respectively.

    # In this example, database name, username, and the user's password are TEST, db2inst1, and Password1 respectively.

    conn_str = 'Driver={DB2};Database=TEST;Uid=db2inst1;Pwd=Password1;'

    You can also use the following script to verify that the SAM python plugin can connect to the DB2 database:

    conn = pypyodbc.connect(conn_str, ansi=True)

    conn.close()

    except Exception, ex:

    print ex

    print "Failure"

  8. Run the script using the following command:
    /opt/SolarWinds/Agent/bin/python2 verifyodbc.py
  9. SolarWinds recommends that you configure the utilities bundled in the DB2 ODBC CLI bundle. Create the db2cli.ini configuration file in /opt/odbc_cli_32/clidriver/cfg/db2cli.ini:

    Replace TEST with your database name.


    TEST
    Database=TEST
    dbalias=TEST
    Protocol=TCPIP
    Hostname=127.0.0.1
    ServiceName=50000

Configure and assign the SAM IBM DB2 template to your Linux/Unix-based computer with the instance user credentials.

  1. In the Orion Web Console, click Settings > All Settings > SAM Settings > Manage Templates.
  2. Search for an IBM DB2 template. SolarWinds recommends creating a copy of the template. Select a template based on version and click Copy.
  3. Modify the settings of the template and component monitors based on the metrics you want to monitor.
  4. To enter credentials, select the component monitor check boxes and click Assign Credentials.
  5. Enter the credentials for the instance user account and click OK.
  6. Modify the connection strings for monitors to match your DB. Use one of the following methods:

    • Specify the actual DB and driver:
      Driver={DB2};Database=TESTDB;Hostname=${IP};Port=50000;Protocol=TCPIP;Uid=${USER};Pwd=${PASSWORD};
    • Specify the existing data source in odbc.ini:
      DSN=testdb;Uid=${USER};Pwd=${PASSWORD};

Component monitors

To learn more about default component monitors included in SAM, see SAM online help.

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.

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.

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.

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.

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.

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.

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.

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'