IBM DB2 Database is a relational database that delivers advanced data management and analytics capabilities for your transactional workloads. Monitoring your DB2 database includes:

  • Increased database performance.
  • Increased data availability and reliability.
  • Tracking the health status of the database.

Supported version

  • DB2 Version 11.5 or higher.
  • JDBC Version: 3.72.54.

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.

To add a resource manually to the inventory:

  1. Select Infrastructure.
  2. Click the Settings > Resource > Add.
  3. From the Add Device window, provide the required details such as Device Type and Management Profile.
  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.

Global templates and metrics

Global templates

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

Global metrics

OpsRamp MetricMetric Display NameUnitDescription
db2.active.sortsDB2 Active Sorts-The number of sorts in the database with an allocated a sort heap.
db2.app.sec.insertsDB2 SQL Section Inserts-The number of SQL sections added per second by an application from its shared SQL work space.
db2.app.sec.lookupsDB2 SQL Section Look-ups-The number of SQL section look-ups by an application from its shared SQL work space per second.
db2.avg.cpu.timeDB2 Avg CPU Timemilliseconds(ms)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.direct.read.timeDB2 Avg Direct Read Timemilliseconds(ms)The average read time = direct read time / direct reads. A high average time indicates an I/O conflict.
db2.avg.direct.write.timeDB2 Avg Direct Write Timemilliseconds(ms)The average write time = direct write time / direct writes. A high average time indicates an I/O conflict.
db2.avg.lock.escalsDB2 Avg Lock Escalations-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.timeoutsDB2 Avg Lock Timeouts-The average number of times a request to lock an object timed out per coordinator activities (successful and aborted).
db2.avg.lockwait.timeDB2 Avg LockWait Timemilliseconds(ms)The average write time = LOCK WAIT TIME / LOCK WAITS at database level. High wait time indicates degrading performance of the application.
db2.bufferpool.avg.physical.read.timeDB2 Buffer Pool Avg Physical Read Timemilliseconds(ms)The average time, in milliseconds, spent reading pages from the table space containers (physical) for all types of table spaces.
db2.bufferpool.avg.write.timeDB2 Buffer Pool Avg Write Timemilliseconds(ms)The average time, in milliseconds, spent physically writing pages from the buffer pool to disk.
db2.bufferpool.data.hit.ratioDB2 Buffer Pool Data Hit Ratiopercentage(%)The percentage that the database manager did not load a page from disk to service a data page request.
db2.bufferpool.index.hit.ratioDB2 Buffer Pool Index Hit Ratiopercentage(%)The percentage that the database manager did not load a page from disk to service an index data page request.
db2.bufferpool.prefetch.ratioDB2 Buffer Pool Prefetch Ratiopercentage(%)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.bufferpool.total.physical.readsDB2 Buffer Pool 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.bufferpool.total.writesDB2 Buffer Pool Total Writes-The number of times a data, index, or data page for an XML storage object (XDA) was physically written to disk.
db2.catalog.cache.hit.ratioDB2 Catalog Cache Hit Ratiopercentage(%)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.insertsDB2 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.lookupsDB2 Catalog Cache Lookups-The number of times an application looked for a section or package in the package cache per second.
db2.catalog.cache.overflowsDB2 Catalog Cache Overflows-The number of times that the package cache overflowed the bounds of its allocated memory per second.

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.
    Important! 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.

Warning: 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 experience DB2 monitoring issues, follow these troubleshooting steps:

  1. Verify server and gateway connectivity using Ping.

    ping <IP Address>
    
  2. Verify Telnet-to-server listening port connectivity.

    telnet <IP Address> <Port>