IBM DB2 Server Monitoring


Overview

IBM DB2 is a family of relational database management system (RDBMS) products from IBM that serve a number of different operating system platforms. DB2 is designed to store, analyze and retrieve the data efficiently and is supported across Linux, Unix, and Windows operating systems.

Applications Manager's DB2 Server Monitoring capability helps database administrators (DBAs) monitor the availability and performance of production databases.

Creating a new IBM DB2 monitor

Supported Versions: Applications Manager supports monitoring of IBM DB2 Versions 8, 9, 10 and 11.

Prerequisites for monitoring IBM DB2 metrics: Click here

Using the REST API to add a new IBM DB2 monitor: Click here

To create a IBM DB2 database server Monitor, follow the given steps:

  1. Click on New Monitor link.
  2. Select DB2 DB Server.
  3. Enter the IP Address or hostname of the host.
  4. Enter the SubNetMask of the network.
  5. Enter the port number in which DB2 is running.
  6. Enter the polling interval
  7. If you are adding a new monitor from an Admin Server, select a Managed Server.
  8. Provide the user name and password of user who has permission to access the DB2 database. The user name specified for collecting the data from DB2 Server should have either System Administrator role or the user should be the DB owner for master database.
  9. Specify the Database Name.
  10. Choose the Monitor Group from the combo box with which you want to associate DB2 database server Monitor (optional). You can choose multiple groups to associate your monitor.
  11. Click Add Monitor(s). This discovers DB2 database server from the network and starts monitoring them.

Monitored Parameters

  • Availability tab gives the Availability history for the past 24 hours or 30 days.
  • Performance tab gives the Health Status and events for the past 24 hours or 30 days.
  • List view enables you to perform bulk admin configurations.

Click on the individual monitors listed to view the following information in the below tabs:

Overview

Monitor Information

Parameters Description
Name Specifies the name of IBM DB2 server monitor.
Health Specifies the health (Clear, Warning, Critical) of the IBM DB2 server.
Type Specifies the type you are monitoring.
Version Specifies the version of the database server.
Port Specifies the port number at which the database server is running.
Instance Name The name of the instance in which the database is present
Server Status The current status of the database server itself
Started Time The date and time that the database manager was started using the db2start comma
Host Name Specifies the host at which the database server is running.
Host OS Specifies the OS of the host where the database server is running.
Last Alarm Specifies the last alarm that was generated for the database server.
Last Polled at Specifies the time at which the last poll was performed.
Next Poll at Specifies the time at which the next poll is scheduled.
Availability Shows the current status of the server - available or not available.

Connection Statistics

Parameters Description
Connection Time Time taken to get connected to the IBM DB2 database server
Total Connections The total number of local and remote connections that currently present in the database manager
Local Connections The number of connections initiated from remote clients to the current instance of the database manager
Remote Connections The number of local applications that are currently connected to the database within the database manager

Agents Statistics

Parameters Description
Active Agents The number of agents in the agent pool that are currently active and assigned to an application
Idle Agents The number of agents in the agent pool that are currently unassigned to any application
Number of Agents The number of agents registered in the current database manager instance
Agents Waiting The number of agents waiting for a token so they can execute a transaction in the database manager

Database Information

Parameters Description
Database Name The real name of the database for which information is collected
Health Specifies the health (Clear, Warning, Critical) of the database.
Database Alias The alias of the database provided when calling the snapshot function
Database Path The full path of the location where the database is stored on the monitored system
Database Status The current status of the database
Connected Time The date and time when the activate database was issued
Deadlock Rate The total number of deadlocks that have occurred in the given polling interval
Percentage of Log Utilization The total amount of active log space used in bytes in the database
Percentage of Sorts Overflowed The percentage of sorts that have over flowed

Transaction Statistics

Parameters Description
Successful Queries The total number of successful SQL statements executed at the database in the given polling interval
Failed Queries The number of SQL statements that were attempted, but failed at the database in the given polling interval
Units of Work This represents the total number of sql commits, internal commits, sql roll backs and internal roll backs done by the database manager in the given polling interval

Cache Performance

Parameters Description
Package Cache Hit Ratio The hit ratio is a percentage indicating how well the package cache is helping to avoid reloading packages and sections for static SQL from the system catalogs as well as helping to avoid recompiling dynamic SQL statements.
Catalog Cache Hit Ratio The hit ratio is a percentage indicating how well the catalog cache is helping to avoid actual accesses to the catalog on disk

Buffer Statistics

Parameters Description
Buffer Pool Hit Ratio The buffer pool hit ratio indicates the percentage of time that the database manager loaded the required page from buffer pool in order to service a page request
Index Page Hit Ratio The Index Page hit ratio indicates the percentage of time that the database manager accessed the index pages present in the buffer pools.
Data Page Hit Ratio The Data Page hit ratio indicates the percentage of time that the database manager accessed the data pages present in the buffer pools.
Direct Reads The number of read operations that do not use the buffer pool
Direct Writes The number of write operations that do not use the buffer pool

TableSpace Status

Parameters Description
Name Refers to the name of the Table space.
Allocated Bytes calculated from (tablespace_total_pages)*(tablespace_page_size) and converted to MB
Free Bytes calculated from (tablespace_free_pages)*(tablespace_page_size) and converted to MB
% of Free Bytes calculated from (tablespace_free_pages) /(tablespace_total_pages)*100

Performance

Parameters Description
Top 10 Long Running Query
Program Name Name of the program/application that was running the query.
User Name Name of the user executing the query.
DB Name The database name under which the query has been running.
Run Time Amount of time for which the query has been running (in seconds).
Activity State The current state of the activity.
Total CPU The cumulative total amount of CPU time that has been spent in running the query (in millisecond).
Query The query which consumed the maximum CPU time.

Session

Parameters Description
Top 100 Session Details per Database
PID Displays the process ID of the session running in the DB2 server.
User The login name of the user executing the process.
Host Name/IP The hostname of the machine where the session is running.
Program Name Name of the program/application that has established the session.
DB Name Displays the name of the database currently being used by the process.
Total CPU The cumulative CPU time taken for running the process (in milliseconds).
CPU I/O Amount of CPU time taken by the process to perform I/O operations (in milliseconds).
Network Communication CPU Amount of time spent by the CPU on client-server communications (in milliseconds).
Total Wait Time The total amount of time spent by an application waiting within the DB2 database server (in milliseconds).
Agent Wait Time Amount of time spent by an application queued to wait for an agent under concentrator configurations (in milliseconds).
Lock Wait Time Amount of time spent by an application waiting for locks (in milliseconds).
Client Idle Time Amount of time spent waiting for the client to send its next request (in milliseconds).
Start Time The date and time at which the session started.