References > Template Reference > IBM > IBM DB2

IBM DB2

This template assesses the performance of an IBM DB2 database by retrieving performance data from the built-in SYSIBMADM tables.

You should also have access to SYSIBMADM tables.

Execute the following commands in the Command Editor before using this 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
  • grant execute on function SYSPROC.MON_GET_TABLE to username where username is the user that will be used in SAM for monitoring IBM DB2. 

After these commands have been executed, restart the database server.

Works with the Orion Agent for Linux.

Prerequisites

IBM DB2 9.7 or later installed on the target server. IBM DB2 ODBC Driver. The driver can be found on the IBM DB2 installation media.

Credentials

Database user name and password.

Configuration for Orion agent for Linux monitoring

For the following instructions, you should have IBM DB2 installed on a Linux-based computer. 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. Login to the Linux-based computer with administrator privileges.

    You will need to create a user account on the server and in the IBM2 database.

  2. Install the unixODBC package.

    Command: yum -y install unixODBC

  3. Update the ODBC config files. Add 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 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. Grant execute on function SYSPROC.MON_GET_TABLE to the instance user account.

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

  1. On the Web Console, click Settings > All Settings > SAM Settings > Manage Templates.
  2. Search for a 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 checkboxes 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};

Monitored Components

For details on monitors, see SAM Component Monitor Types.

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.

Database Used Space (MB)

This component monitor returns the used space, in MB, of the current database.

By default, this monitor returns a value for the database named, SAMPLE. To change the database, find the following parameter in the ODBC driver string and replace SAMPLE with your database name:

Database=SAMPLE;

Log File Used Space in Specified Database (MB)

This component monitor returns the used space, in MB, of the log file in the specified database.

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

WHERE DB_NAME = 'SAMPLE'

Log File Free Space in Specified Database (MB)

This component monitor returns the free space, in MB, of the log file in the specified database.

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

WHERE DB_NAME = 'SAMPLE'

Average Buffer Total Hit Ratio (%)

This component monitor returns the average Total Hit Ratio (index, XDA, and data reads) for all buffers. To see the Hit Ratio for all buffers, you should manually execute the following SQL command:

SELECT * FROM SYSIBMADM.BP_HITRATIO

If you want to see the Hit Ratio for a specific buffer, you can use the following SQL construction:

SELECT REAL(AVG(TOTAL_HIT_RATIO_PERCENT))
FROM SYSIBMADM.BP_HITRATIO
WHERE BP_NAME = 'IBMDEFAULTBP'

The returned value should be as high as possible.

Average Data Hit Ratio (%)

This component monitor returns the average Data Hit Ratio for all buffers. To see the Hit Ratio for all buffers, you should manually execute the following SQL command:

SELECT * FROM SYSIBMADM.BP_HITRATIO

If you want to see the Hit Ratio for a specific buffer, you can use the following SQL construction:

SELECT REAL(AVG(DATA_HIT_RATIO_PERCENT))
FROM SYSIBMADM.BP_HITRATIO
WHERE BP_NAME = 'IBMDEFAULTBP'

The returned value should be as high as possible.

Average Index Hit Ratio (%)

This component monitor returns the average Index Hit Ratio for all buffers. To see the Hit Ratio for all buffers, you should manually execute the following SQL command:

SELECT * FROM SYSIBMADM.BP_HITRATIO

If you want to see the Hit Ratio for a specific buffer, you can use the following SQL construction:

SELECT REAL(AVG(INDEX_HIT_RATIO_PERCENT))
FROM SYSIBMADM.BP_HITRATIO
WHERE BP_NAME = 'IBMDEFAULTBP'

The returned vale should be as high as possible.

Number of Locks Held in Specified Database

This component monitor returns the number of Locks currently held in the specified database.

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

WHERE DB_NAME = 'SAMPLE'

The returned value should be as low as possible.

Average Read Time (ms)

This component monitor returns the average read time from the database in milliseconds. The returned value should be as low as possible.

Connected applications to Specified Database

This component monitor returns the number of connected applications to the specified database.

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

WHERE DB_NAME = 'SAMPLE'

Number of Long Running Queries

This component monitor returns the number of Long Running Queries. You should set the threshold according to your environment.

Number of Table Scans

This component monitor returns the number of table scans currently in progress in the specified database.

Table with the Biggest Table Scans Value

This component monitor returns the table name with the Biggest Value of Table Scans and the value itself.

Used Space of the Biggest Table (MB)

This component monitor returns the name of the Biggest Table and its size, in MB.