Monitor DB2 Blocking sessions with DX UIM

Products

DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

We are using DX UIM and the DB2 probe and want to monitor DB2 Blocking session. Is there a checkpoint to monitor this?

Environment

DX UIM 23.4.*

DB2 probe any version

Cause

Guidance

Resolution

The DB2 probe will help you monitor blocking sessions. Please review the probe Metrics:

db2 Metrics (broadcom.com)

Metrics related to monitoring blocking sessions in a DB2 server typically include those that track lock-related activities and resource contention. Here are some metrics from above link that are related to monitoring blocking sessions:

app_lock_wait_time: Monitors the total elapsed time the application has waited for a lock in milliseconds. High values here could indicate blocking sessions.
app_lock_waits: Tracks the number of times the application has waited for locks. This can help identify instances where sessions are blocked.
app_locks_held: Monitors the number of locks currently held by the application. If a session is holding a lock that is causing others to wait, it could indicate a blocking scenario.
db_lock_wait_time: Similar to app_lock_wait_time, but at the database level. Monitors the total time the database has waited on locks in milliseconds.
db_lock_waits: Tracks the number of times the database has waited for locks. Like app_lock_waits, this can indicate potential blocking scenarios.
db_locks_held: Monitors the number of locks currently held by the database. High values may suggest potential blocking situations.

These metrics provide insights into lock contention and waiting times, which are often indicators of blocking sessions in a database server.

Additional Information

If the out-of-the-box metrics provided by the probe and described above do not match the requirement it is possible to create a custom query.

For example, In DB2, you can query the system catalog tables to retrieve information about blocking sessions.

Here's a query that may help you identify blocking sessions:

SELECT
AGENT_ID,
APPLICATION_HANDLE,
APPLICATION_NAME,
APPL_STATUS,
MEMBER,
LOCK_WAIT_START_TIME,
LOCK_WAIT_END_TIME,
LOCK_WAIT_TIME,
LOCK_WAIT_STATE,
LOCK_WAIT_TYPE,
LOCK_NAME,
LOCK_MODE,
LOCK_OBJECT_TYPE,
LOCK_MODE_REQUESTED,
LOCK_STATUS,
LOCK_ESCALATION,
NUM_LOCKS_HELD,
ROWS_READ,
ROWS_MODIFIED,
UOW_LOG_SPACE_USED
FROM
SYSIBMADM.SNAPAPPL_INFO
WHERE
APPL_STATUS = 'UOWWAIT'
AND LOCK_WAIT_TIME > 0;

This query retrieves information about application agents that are currently waiting on locks (APPL_STATUS = 'UOWWAIT') and have been waiting for a non-zero amount of time (LOCK_WAIT_TIME > 0).

It provides details such as the agent ID, application handle, application name, lock wait start time, lock wait end time, lock name, lock mode, lock object type, lock mode requested, and various other relevant information.

You can run this query in your DB2 instance to identify blocking sessions and analyze the locking situation in your database.

Once you test this and if it matches your requirements, you can create a custom checkpoint:

See how you can create a checkpoint:

db2 IM Configuration (broadcom.com) > Create a Checkpoint