Supported Versions
DB2 Version 11.5 or higher
JDBC Version: 3.72.54

This describes the monitoring setup for the IBM DB2 database.

Prerequisite

  • Database and port(50000) can be accessed by the gateway.

  • SELECT and EXECUTE privileges to access the following database tables:

    • SYSIBMADM.MON_TBSP_UTILIZATION
    • SYSIBMADM.MON_DB_SUMMARY
    • SYSIBMADM.MON_BP_UTILIZATION
    • SYSIBMADM.SNAPDB
  • A gateway management profile mapped to a resource.

  • Create credentials with type Database before assigning a template to a resource. The Port, Connection Time Out, and Is Secure values are not used to create credentials.

Discovery using the gateway

The gateway establishes a connection to the database using the Java Database Connectivity (JDBC) Java API and collects metrics using SQL queries. To monitor DB2, install gateway version 5.5.0 or later.

Use one of the following mechanisms to discover DB2 servers to add them to your inventory:

  • WMI
  • SSH
  • SNMP

Optionally, add a database server manually to the infrastructure inventory:

  1. Select Infrastructure and click Resources.
  2. Click the Settings icon on the top-right and click Add.
  3. On the Add Device page, enter the Device Type and any other information you want to enter.
  4. Click Save.

Prepare the device to start monitoring

  • Associate appropriate database credentials to the discovered database.
  • Assign one or more database-specific global monitors or templates on the device.
    Optionally, create custom metrics or monitors using SQL queries and assign them to the database.

Create custom metrics

To create a metric using SQL query for DB2 monitoring, navigate to Monitoring > Metrics > Create Metric. For example, to create a metric to find the user and system CPU time, use:

SELECT AVG_RQST_CPU_TIME / 1000 FROM SYSIBMADM.MON_DB_SUMMARY

For more information, see Create a Metric.

Assign templates from setup

Assign DB2 templates to one or more resources for a selected client and modify the configuration parameters while assigning templates. For more information, see Assign Templates from Setup.

Assign templates at the device level

Applying DB2 templates at the device level helps assign one or more templates to a specific resource. You can change the configuration parameter default values while assigning the templates. For more information, see Assign Templates at the Device Level.

Resource template configuration parameters:

Configuration ParameterDescription
Connection Time OutThe maximum time to connect to the database. The driver waits for the specified time before timing out due to connection failure. The default time is 15000 milliseconds(ms).
Service Transport TypeTo configure the database at a secure end-point. The default type is In-secure. The connection is Secure when the data sent between the client and server is encrypted.
Service PortThe port number where the database is running. The connection is made to the specified port if you specify the port and the database name. The default port is 50000.
DB Instance NameThe name of the database to connect to. The default names are sample and model.
  • If you have multiple database names with different ports, use the following syntax: `DBname1:Port1, DBname2:Port2,..` (Comma-separated). For example, if DBname 1 has credential set A and DBname 2 has credential set B, create separate credentials sets on the resource. Create separate credential sets on the resource if databases consist of different users.
  • If you have multiple database names with the same port, use the following syntax: `DBname1, DBname2,..` (Comma-separated).
Application TypeThe application type value to identify the adapter. For example, DB2.
Do not change the default application type value.

Assign template from device management policies

Device management policies help manage resources. You can assign monitoring templates, knowledge base articles, and custom attributes using device management policies. The device management policy can be applied to one or a set of resources. For more information, see Creating Policies.

View resource metrics

The gateway monitors the application using the assigned templates and displays the results in graphical format. To view resource metrics, click the database resource name > resource Details > Metrics.

Troubleshooting

Verify the following if the graphs are not plotted correctly:

  • The resource is accessible and Telnet is established for the IP address and port.
  • Valid credentials are used for the resource.

If you have DB2 monitoring issues, verify gateway and telnet connectivity:

ping <IP Address>
telnet <IP Address> <Port>

Global templates

Collector TypeTemplate Name
GatewayCouchDB2 - Statistics
DB2 Performance Template
DB2 BufferPool and TableSpace Statistics

Global metrics

OpsRamp MetricMetric Display NameUnit
db2.active.sorts

The number of sorts in the database with an allocated a sort heap.
DB2 Active Sorts-
db2.app.sec.inserts

The number of SQL sections added per second by an application from its shared SQL work space.
DB2 SQL Section Inserts-
db2.app.sec.lookups

The number of SQL section look-ups by an application from its shared SQL work space per second.
DB2 SQL Section Look-ups-
db2.avg.cpu.time

The average amount of CPU time, in microseconds, used by all external requests that completed successfully. It represents the total user and system CPU time.
DB2 Avg CPU Timemilliseconds(ms)
db2.avg.direct.read.time

The average read time = direct read time / direct reads. A high average time indicates an I/O conflict.
DB2 Avg Direct Read Timemilliseconds(ms)
db2.avg.direct.write.time

The average write time = direct write time / direct writes. A high average time indicates an I/O conflict.
DB2 Avg Direct Write Timemilliseconds(ms)
db2.avg.lock.escals

The average number of times the locks are escalated from several row locks to a table lock per coordinator activities (successful and aborted).
DB2 Avg Lock Escalations-
db2.avg.lock.timeouts

The average number of times a request to lock an object timed out per coordinator activities (successful and aborted).
DB2 Avg Lock Timeouts-
db2.avg.lockwait.time

The average write time = LOCK WAIT TIME / LOCK WAITS at database level. High wait time indicates degrading performance of the application.
DB2 Avg LockWait Timemilliseconds(ms)
db2.bufferpool.avg.physical.read.time

The average time, in milliseconds, spent reading pages from the table space containers (physical) for all types of table spaces.
DB2 Buffer Pool Avg Physical Read Timemilliseconds(ms)
db2.bufferpool.avg.write.time

The average time, in milliseconds, spent physically writing pages from the buffer pool to disk.
DB2 Buffer Pool Avg Write Timemilliseconds(ms)
db2.bufferpool.data.hit.ratio

The percentage that the database manager did not load a page from disk to service a data page request.
DB2 Buffer Pool Data Hit Ratiopercentage(%)
db2.bufferpool.index.hit.ratio

The percentage that the database manager did not load a page from disk to service an index data page request.
DB2 Buffer Pool Index Hit Ratiopercentage(%)
db2.bufferpool.prefetch.ratio

The percentage of pages read asynchronously (with prefetching). If many applications are reading data synchronously without prefetching, your system might not be tuned optimally.
DB2 Buffer Pool Prefetch Ratiopercentage(%)
db2.bufferpool.total.physical.reads

The number of data pages, index pages, and data pages for XML storage objects (XDAs) read from the table space containers (physical) for temporary, regular, and large table spaces.
DB2 Buffer Pool Total Physical Reads-
db2.bufferpool.total.writes

The number of times a data, index, or data page for an XML storage object (XDA) was physically written to disk.
DB2 Buffer Pool Total Writes-
db2.catalog.cache.hit.ratio

Catalog cache hit ratio = 1-(cat_cache_inserts /cat_cache_lookups))*100. The hit ratio is a percentage indicating how the catalog cache is helping to avoid actual accesses to the catalog on disk. A high ratio indicates it is successful in avoiding actual disk I/O accesses.
DB2 Catalog Cache Hit Ratiopercentage(%)
db2.catalog.cache.inserts

The number of times a requested section is not available for use and had to be loaded into the package cache per second.
DB2 Catalog Cache Inserts-
db2.catalog.cache.lookups

The number of times an application looked for a section or package in the package cache per second.
DB2 Catalog Cache Lookups-
db2.catalog.cache.overflows

The number of times that the package cache overflowed the bounds of its allocated memory per second.
DB2 Catalog Cache Overflows-