IBM DB2 for LUW Monitoring

Introduction:

The DB2 database, a product of IBM, is one of the most powerful database engines in the world. It is an SQL database designed for ZOS and LUW (Linux, Unix, Windows) environments. This commercial database requires appropriate licenses from IBM for utilization. Due to its high performance, DB2 is currently used by many enterprise-class companies and banks.

Utilizing any database requires an understanding of its architecture and operation, which are provided through documentation and training by the producing companies. One of the critical aspects of utilizing DB2, as a database administrator, is continuous monitoring and performance optimization. For this purpose, the administrator must have a proper understanding of the essential DB2 database metrics.

Various tools have been developed worldwide for monitoring this database, such as SolarWinds DPA and ManageEngine APM. The Moein monitoring platform also currently supports monitoring this database, with details and key monitored metrics available through a link. In any database, apart from host-related parameters and resource consumption like CPU, memory, and I/O, there are important database-specific metrics that every administrator should be aware of. Monitoring these metrics and adjusting them if necessary is crucial.

In this brief article, we aim to introduce some important parameters of the DB2 database in the LUW environment for administrators to consider during monitoring. An examination of the DB2 database in the ZOS environment will be conducted in another article.

Database Availability and Status:

For any administrator, the availability of the database is of paramount importance. Database availability means that applications can connect to the database and execute queries. In case of an issue, the first action administrators usually take is to test the database port availability. However, this test does not necessarily mean the database is available for applications, as the port may be accessible, but query execution might still be impossible. Therefore, testing database availability should go beyond port testing. Executing a simple query with minimal overhead can assure the administrator that the database is accessible from the application's perspective. Periodically running a query and sending notifications if it fails can alert administrators to any database issues. This is currently implemented in many global monitoring tools.
Knowing the current status of the database by querying the database performance tables can also help administrators understand the current state of the database.

Connections:

One of the most important parameters in the DB2 database is connection statistics. Connections to this database include both local and remote connections. Local connections involve interactions between processes within the database and the database administrator. Remote connections involve interactions between applications or clients that connect to the database administrator from afar. The total number of local and remote connections determines the overall connections to the DB2 database, which is used to configure the max_connections and max_coordagents parameters.

Database Capacity:

The capacity used by the DB2 database is essentially the total capacity utilized by the Table Spaces. This parameter indicates the storage volume used by the database. In addition to the used capacity, the total available capacity is also a crucial parameter that administrators need to be aware of to understand the remaining capacity. Currently, these two parameters and their ratio can be monitored and are tracked in the Moein platform as well. Note that calculating the used capacity imposes a significant overhead on the database, so its value should be measured at longer intervals compared to other metrics. Currently, in the Moein platform, these metrics are calculated once every 24 hours.

Agents

Agents in the IBM DB2 database (LUW) are responsible for establishing communication between applications and the database, executing application requests. Essentially, DB2 database agents are threads within the database manager that handle application requests.

There are three types of agents in the DB2 database:

1. Idle Agents: These agents do not execute any requests and are inactive. They become active when Coordinators send requests to them. Idle agents reside in the agent pool.
2. Active Coordinator Agents: Every connection to the database from a client or application has an active coordinator agent. This type of agent forwards the requests from the application to the Worker agents for execution. Coordinators have the most interaction with the applications.
3. Subagents: Subagents, also known as Worker agents, execute the application requests that are forwarded to them by the Coordinator agents.

The number of available agents is dependent on the NUM_POOLAGENTS variable in the database management configuration section. To view the configured value in the DB2 database, the command db2 get database manager configuration can be used. The output of this command displays the configured parameters in the database, with the section related to agent settings shown in Figure 1.

As shown in the figure, the maximum number of coordinating agents is set to 200. Similarly, the maximum number of client connections (applications) is set equal to the maximum number of coordinating agents. The parameter for the maximum number of coordinating agents should be configured based on the communication load between applications and the DB2 database. According to the default settings, a maximum of 200 applications can connect to the database.

It is recommended to continuously monitor this parameter to assess the communication load on the database and perform tuning if necessary. Keep in mind that if the number of connections reaches the limit of coordinating agents, no additional connections from any applications will be accepted. To view the list of agents and their summary status, the command db2pd -agents can be used.

Concentrator Mode:

If the MAX_CONNECTIONS parameter is set higher than the MAX_COORDAGENTS parameter in the database configuration, the concentrator mode is enabled. In this mode, requests may wait for agents due to the higher number of connections compared to the number of coordinator agents. The agent_wait_time parameter reflects the wait time for queued applications awaiting agent allocation. Therefore, if the database is configured in concentrator mode, the agent wait time should also be monitored to understand the duration spent in the agent wait queue. If this value is high, the number of agents should be increased proportionally to the number of connections.

Currently, the Moein monitoring platform tracks the statistics of active agents, idle agents, agent wait times, and the frequency of agent waits. Administrators can monitor the usage trends of agents in the DB2 database. For a detailed list of metrics monitored by the Moein platform, refer to the provided link.

Deadlock Statistics:

A deadlock in the database occurs when two or more transactions or processes are waiting for resources held by each other. In other words, multiple transactions simultaneously request locks on resources that are already locked by another transaction. This results in both transactions being blocked and unable to proceed. While databases have mechanisms to detect deadlocks, knowing the statistics of deadlocks can help administrators and application development teams identify issues causing application slowdowns. Currently, this parameter (deadlock statistics) is periodically collected by the Moein platform and other monitoring tools.

Status of Table Spaces:

A Table Space in DB2 is a storage structure that holds the data of tables. Each table is stored within a table space. Monitoring the status of table spaces is crucial as it provides insight into the storage utilization and helps ensure that there is adequate space for data growth.

Database Throughput:

The volume of requests executed by the database over time can inform administrators about the number of transactions that the database can handle given the allocated resources. One of the metrics that indicate transaction volume in the database is the Unit of Work (UOW). A UOW represents a recoverable sequence of operations within an application process and is also known as a logical unit of work. Each application process has one UOW at any given time. According to IBM's official site, the UOW in DB2 can be calculated by summing the following parameters:

• Commit SQL Statements
• Internal Commits
• Rollback SQL Statements
• Internal Rollbacks

The total of these four parameters indicates the number of units of work performed by the DB2 database. Currently, this metric is calculated as a rate in the Moein platform.

In addition to this metric, the statistics of successful and unsuccessful queries also provide administrators with insights into the volume of requests processed by the database, which in turn reflects the database's request handling capacity.

Statistics of High Resource-Consuming Queries:

During an issue, one of the most critical pieces of information that helps administrators identify the root cause is knowing the most resource-intensive queries over a given period. Queries with the highest CPU, memory, and I/O consumption, frequent queries, and slow queries are typically used by administrators to identify high-consumption queries. These statistics are currently monitored by the Moein monitoring platform.

Backup:

Database backup is one of the most common activities performed by database administrators. Given the number of databases, monitoring the successful completion of backups is crucial for administrators. Hence, monitoring tools must track the successful execution of backup operations and notify administrators in case of any issues. Currently, the success or failure of backups is monitored by many monitoring tools.

Finally, it should be noted that the parameters and metrics presented are part of the metrics monitored by DB2 database monitoring tools. Future articles will aim to introduce other impactful metrics.