IBM DB2

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

This template is compatible with the SolarWinds Platform Agent for Linux.

Prerequisites

IBM DB2 9.7 or later is installed on the target server.

Install the required version of IBM DB2 ODBC Driver on the SAM Server and create a new data source to the target DB2 database server.

Access to SYSIBMADM tables in the IBM DB2 database.

Credentials

Database user name and password.

Configure SSL/TLS for the DB2 ODBC driver

See IBM documentation for reference.

Prepare the database server

To prepare the IBM DB2 database server:

Execute the following commands in the Command Editor:

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

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 will utilize to monitor the IBM DB2 database, as shown in this example:
GRANT EXECUTE ON FUNCTION SYSPROC.MON_GET_TABLE TO user
Restart the database server.

Configuration for SolarWinds Platform 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.

Log in to the Linux-based computer with administrator privileges.

Use the following command to install the unixODBC package:

yum -y install unixODBC

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.

Add the following settings to /etc/odbc.ini:
[dbname]
Driver = DB2
The drivers should match in both files.

Connect locally to the database with the instance user credentials. For example:
isql -v dbname UserID Password

Log in to the DB2 database with the user credentials for the instance, and open the DB2 command line tool.

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

Use the GRANT EXECUTE ON FUNCTION statement to grant privileges for the SYSPROC.MON_GET_TABLE function to user, where user is the username, as shown in this example:
GRANT EXECUTE ON FUNCTION SYSPROC.MON_GET_TABLE TO user

Configure DB2 on AIX devices

Install the IBM DB2 ODBC Driver on AIX devices. The commands use the following driver bundle:
v11.1.1fp1_aix64_odbc_cli.tar.gz.

Install the ODBC driver manager package and dependencies from AIX Open Source Packages.
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)

Verify that the IBM rpm.rte package exists in the /usr/bin/rpm directory

Configure ODBC for AIX
Log in to the AIX-based computer with administrator privileges.
Install the Red Hat Package Manager (RPM) files from the AIX Open Source Package.

Run the following command as root:
rpm -ivh unixODBC-2.3.4-1.aix5.1.ppc.rpm
To install all dependencies, run the rpm -ivh command for each of them. For example:
rpm -ivh info-6.4-1.aix5.1.ppc.rpm
Install the DB2 ODBC driver. The example code creates a directory odbc_cli in the /opt directory.
gunzip v11.1.1fp1_aix64_odbc_cli.tar.gz
tar -x -f v11.1.1fp1_aix64_odbc_cli.tar -C /opt
Add the following lines to /opt/freeware/etc/odbcinst.ini:
[DB2]
Description = DB2 Driver
Driver = /opt/IBM/db2/V11.1/lib64/db2o.o
FileUsage = 1
DontDLClose = 1
Verify libdb2 has the correct file path.
Add the following settings to /opt/freeware/etc/odbc.ini:
[dbname]
Description = IBM DB2 ODBC data source
Driver = DB2
DMEnvAttr = SQL_ATTR_UNIXODBC_ENVATTR={DB2INSTANCE=dbname}
The “dbname” is the name of the database to be monitored (in square brackets and as the value of DB2INSTANCE).
(Optional) Verify that you can connect to the DB2 instance using isql utility.
Sample output:
user@aix-host:/$ /opt/freeware/bin/isql SAMPLE
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>
(Optional) 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 SolarWinds Platform 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 password to access said database respectively.
# In this example, database name, username, and the user's password are TEST, UserID, and Password respectively.
try:
  conn_str = 'Driver={DB2};Database=TEST;Uid=UserID;Pwd=Password;'
  conn = pypyodbc.connect(conn_str, ansi=True)
  conn.close()
  print("ODBC connection successful!")
except Exception as ex:
  print(ex)
  print("ODBC connection failed.")
Run the script using the following command:
/opt/SolarWinds/Agent/bin/python3 verifyodbc.py

The script will show output in the terminal informing if the connection was successful or if it failed (with error reason).

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

Click Settings > All Settings > SAM Settings > Manage Templates.

Search for an IBM DB2 template. SolarWinds recommends creating a copy of the template. Select a template based on version and click Copy.

Modify the settings of the template and component monitors based on the metrics you want to monitor.

To enter credentials, select the component monitor check boxes and click Assign Credentials.

Enter the credentials for the instance user account and click OK.

Modify 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};

Usage tips

Monitoring multiple AIX DB2 instances on a single AIX host

In odbc.ini on the system hosting multiple DB2 instances, add the DMEnvAttr property for each AIX DB instance. The property should contain SQL_ATTR_UNIXODBC_ENVATTR, which creates an environmental variable for each DB2 context.

Modify your odbc.ini, as per example below:
[SAMPLE1]
Description=IBM DB2 ODBC data source 1
Driver=DB2
DMEnvAttr=SQL_ATTR_UNIXODBC_ENVATTR={DB2INSTANCE=sample1}
[SAMPLE2]
Description=IBM DB2 ODBC data source 2
Driver=DB2
DMEnvAttr=SQL_ATTR_UNIXODBC_ENVATTR={DB2INSTANCE=sample2}
Troubleshooting

ODBC tracing

To enable ODBC tracing for more detailed log, add the following section to your odbcinst.ini file:
[ODBC]
Trace=yes
TraceFile=/tmp/odbc.log
UsageCount=1
TraceOptions=3
The log should then be available in /tmp/odbc.log.

Common errors
File "/opt/SolarWinds/Agent/bin/Plugins/SharedPythonLibs/pypyodbc.py", line 427, in <module>
(...)
OSError: Could not load module.
System error: No such file or directory
The pypyodbc component code could not load libodbc from a known path.

Add a symlink to libodbc.a in /usr/lib ln -s /opt/freeware/lib/libodbc.a /usr/lib/libodbc.a.
[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified
Incorrect or non-existent library defined in odbcinst.ini.
[ ] [IBM][CLI Driver] SQL10007N Message "0" could not be retrieved. Reason code: "3".
Most likely caused by incorrect ODBC driver version or architecture. For example, a 64-bit process calls a 32-bit driver.
[08001] [IBM][CLI Driver] SQL30082N Security processing failed with reason "24" ("USERNAME AND/OR PASSWORD INVALID"). SQLSTATE=08001
Review user name, password, and permissions.

Component monitors

Click here for an overview about SAM application monitor templates and component monitors. SAM API Poller templates are also available.

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.

Search SolarWinds Support

IBM DB2

Configure DB2 on AIX devices

Usage tips

Troubleshooting

Component monitors