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.
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.
Supported version
- DB2 Version 11.5 or higher.
- JDBC Version: 3.72.54.
Database Discovery and Monitoring
The administrator can deploy a gateway to support IBM DB2 agentless monitoring.
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:
- Select Infrastructure.
- Click the Settings > Resource > Add.
- From the Add Device window, provide the required details such as Device Type and Management Profile.
- Click Save.
Prepare the device to start monitoring
To monitor IBM DB2:
- 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 Type | Template Name |
|---|---|
| Gateway | CouchDB2 - Statistics |
| DB2 Performance Template | |
| DB2 BufferPool and TableSpace Statistics |
Global metrics
| OpsRamp Metric | Metric Display Name | Unit | Description |
|---|---|---|---|
| db2.active.sorts | DB2 Active Sorts | - | The number of sorts in the database with an allocated a sort heap. |
| db2.app.sec.inserts | DB2 SQL Section Inserts | - | The number of SQL sections added per second by an application from its shared SQL work space. |
| db2.app.sec.lookups | DB2 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.time | DB2 Avg CPU Time | milliseconds(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.time | DB2 Avg Direct Read Time | milliseconds(ms) | The average read time = direct read time / direct reads. A high average time indicates an I/O conflict. |
| db2.avg.direct.write.time | DB2 Avg Direct Write Time | milliseconds(ms) | The average write time = direct write time / direct writes. A high average time indicates an I/O conflict. |
| db2.avg.lock.escals | DB2 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.timeouts | 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.lockwait.time | DB2 Avg LockWait Time | milliseconds(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.time | DB2 Buffer Pool Avg Physical Read Time | milliseconds(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.time | DB2 Buffer Pool Avg Write Time | milliseconds(ms) | The average time, in milliseconds, spent physically writing pages from the buffer pool to disk. |
| db2.bufferpool.data.hit.ratio | DB2 Buffer Pool Data Hit Ratio | percentage(%) | The percentage that the database manager did not load a page from disk to service a data page request. |
| db2.bufferpool.index.hit.ratio | DB2 Buffer Pool Index Hit Ratio | percentage(%) | The percentage that the database manager did not load a page from disk to service an index data page request. |
| db2.bufferpool.prefetch.ratio | DB2 Buffer Pool Prefetch Ratio | percentage(%) | 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.reads | DB2 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.writes | DB2 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.ratio | DB2 Catalog Cache Hit Ratio | percentage(%) | 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.inserts | 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.lookups | 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.overflows | DB2 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 Manage Metrics.
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 Parameter | Description |
|---|---|
| Connection Time Out | The 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 Type | To 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 Port | The 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 Name | The name of the database to connect to. The default names are sample and model.
|
| Application Type | The 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 desired 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:
- Verify server and gateway connectivity using Ping.
ping <IP Address>
Example
ping 12.26.105.173
- Verify Telnet-to-server listening port connectivity.
telnet <IP Address> <Port>
Example
telnet 172.26.105.173 50000