Blocking and waiting connections
Displays the blocker and waiter pair in the console.
Repository table
The repository table that is used to display information about blocking and waiting connections is <SCHEMA>.LOCKPAIR
Source
The data that is found in the repository table LOCKPAIR comes from the table functions MON_GET_APPL_LOCKWAIT, MON_GET_CONNECTION, MON_GET_ACTIVITY, and MON_GET_PKG_CACHE_STMT.
The table function MON_GET_APPL_LOCKWAIT is called with parameters (NULL, -2) which retrieves locks for all applications that are currently waiting for locks to be acquired for all active database members.
To retrieve connection information for BLOCKER connections, information is gathered using the table function MON_GET_CONNECTION with parameters (HLD_APPLICATION_HANDLE, and HLD_MEMBER) which is collected from MON_GET_APPL_LOCKWAIT.
To retrieve connection information for WAITER connections, information is gathered using the table function MON_GET_CONNECTION with parameters (REQ_APPLICATION_HANDLE, and REQ_MEMBER) which is collected from MON_GET_APPL_LOCKWAIT.
To get the SQL statement of the application holding the lock, MON_GET_PKG_CACHE_STMT with parameters (NULL, LAST_EXECUTABLE_ID, NULL, and HLD_MEMBER) is used, where LAST_EXECUTABLE_ID is collected from MON_GET_CONNECTION and HLD_MEMBER is collected from MON_GET_APPL_LOCKWAIT.
To get the SQL statement for the waiting application, MON_GET_ACTIVITY with parameters (REQ_APPLICATION_HANDLE, and REQ_MEMBER) is used, which is collected from MON_GET_APPL_LOCKWAIT.
Columns
Column Name | Type | Description |
dbconn_int | SMALLINT | The internal ID for connection name. dbconn_int maps to a unique connection name, which is the name specified for the connection when a user configures a database to be monitored. The mapping of dbconn_int to connection name is stored in the repository table <SCHEMA>.RTMON_MAP_DBCONN |
timestamp | BIGINT | Timestamp of end of the collection interval stored as Bigint. This time is stored as Unix Epoch time. Use this link to convert this number into readable format https://www.ibm.com/support/pages/changing-unix-time-readable-timestamp-db2. |
collected | TIMESTAMP | Timestamp of the end of the collection interval stored as UTC time. |
level_tag | BIGINT | The level_tag is used to filter out data. |
blocker_application_handle | BIGINT | System-wide unique ID for the application that is holding the lock. If the application holding this lock is unknown or cannot be found, then a value of NULL is returned. This value is collected from the table function MON_GET_APPL_LOCKWAIT. |
blocker_application_name | VARCHAR(128) | The name of the application running at the client and holding the lock. This value is collected from the table function MON_GET_CONNECTION for the blocker_application_handle and the member. |
member | SMALLINT | Database member where the lock is being held by the holding application. This value is collected from the table function MON_GET_APPL_LOCKWAIT. |
lock_start_time | BIGINT | The date and time that this application started waiting to obtain a lock on the object that is currently locked by another application. This value is collected from the table function MON_GET_APPL_LOCKWAIT and stored as Unix Epoch time. |
blocker_session_auth_id | VARCHAR(128) | The current authorization ID for the session being used by this application. This value is collected from the table function MON_GET_CONNECTION for the blocker_application_handle and the member. |
blocker_lock_mode | VARCHAR(3) | The type of lock being held. If the mode is unknown, the value of this monitor element is NULL. This value is collected from the table function MON_GET_APPL_LOCKWAIT. |
waiter_lock_mode | VARCHAR(3) | The mode in which the lock was requested by the application waiting to acquire the lock. This value is collected from the table function MON_GET_APPL_LOCKWAIT. |
lock_name | VARCHAR(32) | Internal binary lock name. This element serves as a unique identifier for locks. This value is collected from the table function MON_GET_APPL_LOCKWAIT. |
blocker_workload_occurrence_state | VARCHAR(32) | The state of the workload occurrence. This value is collected from the table function MON_GET_CONNECTION for the blocker_application_handle and the member. |
blocker_lock_waits | BIGINT | The total number of times that the applications or connections waited for locks. This value is collected from the table function MON_GET_CONNECTION for the blocker_application_handle and the member. |
blocker_num_locks_held | BIGINT | The number of locks that is currently held. This value is collected from the table function MON_GET_CONNECTION for the blocker_application_handle and the member. |
blocker_lock_wait_time | BIGINT | The total elapsed time spent waiting for locks. The value is given in milliseconds. This value is collected from the table function MON_GET_CONNECTION for the blocker_application_handle and the member. |
waiter_application_handle | BIGINT | System-wide unique ID for the application that is waiting to acquire the lock. This value is collected from the table function MON_GET_APPL_LOCKWAIT. |
waiter_application_name | VARCHAR(128) | The name of the application that is running at the client and waiting to acquire the lock. This value is collected from the table function MON_GET_CONNECTION for the waiter_application_handle and the waiter_member. |
waiter_member | SMALLINT | Database member where the application waiting to acquire this lock is located. This value is collected from the table function MON_GET_APPL_LOCKWAIT. |
waiter_session_auth_id | VARCHAR(128) | The current authorization ID for the session that is being used by this application. This value is collected from the table function MON_GET_CONNECTION for the waiter_application_handle and the waiter_member. |
waiter_workload_occurrence_state | VARCHAR(32) | The state of the workload occurrence. This value is collected from the table function MON_GET_CONNECTION for the waiter_application_handle and the waiter_member. |
waiter_lock_waits | BIGINT | The total number of times that applications or connections waited for locks. This value is collected from the table function MON_GET_CONNECTION for the waiter_application_handle and the waiter_member. |
waiter_num_locks_held | BIGINT | The number of locks currently held. This value is collected from the table function MON_GET_CONNECTION for the waiter_application_handle and the waiter_member. |
waiter_lock_wait_time | BIGINT | The total elapsed time spent waiting for locks. The value is given in milliseconds. This value is collected from the table function MON_GET_CONNECTION for the waiter_application_handle and the waiter_member. |
blocker_sql_hash_id | BIGINT | The text of the SQL statement of the application holding the lock. This value is collected from the table function MON_GET_PKG_CACHE_STMT for last_executable_id and the member. The SQL statement is stored as Hash ID. The full SQL statement text can be looked up in the SQL_DIM table by the corresponding Hash ID. |
waiter_sql_hash_id | BIGINT | The text of the SQL statement of the application waiting for the lock. This value is collected from the table function MON_GET_ACTIVITY where executable_id equals req_executable_id from MON_GET_APPL_LOCKWAIT table function. The SQL statement is stored as Hash ID. The full SQL statement text can be looked up in the SQL_DIM table by the corresponding Hash ID. |
- String format dd:hh:mm:ss.SSS, where 's' is milliseconds. For example, 2d:09:41:57.157.
- Absolute format in the columns where unit is specified. For example, 100 'Average log write time (ms)' is represented as 100 ms.
How metrics are calculated:
The Blocking and waiting connections page allows you to see the applications (Waiter) that are requesting locks from an application (Blocker) that is currently holding the lock.
Data is collected in real time when the page is viewed. The MON_GET_APPL_LOCKWAIT function is called to retrieve the waiting and blocking applications information, and joined with MON_GET_CONNECTIONS, MON_GET_ACTIVITY, and MON_GET_PKG_CACHE_STMT functions to gather subsidiary information.
For lock details of the blocking application, you can view the Locked objects with waiting connections page. You can find the details on locks that is held by the blocker.
By default, a snapshot to collect blocking and waiting connections information is done at each collection interval (5 minutes default). You can look at the historical mode to get an idea on blocking and waiting situation at a moment in time in the past. However, collection is done as a snapshot, so, you may not get all blocking and waiting applications if it happened within the collection interval. Use the locking event monitor to collect continuous locking information.
Metric Name | Repository Table column |
Type | The type is returned as either BLOCKER or WAITER based on columns blocker_application_handle and waiter_application_handle |
Application handle | blocker_application_handle for the BLOCKER waiter_application_handle for the WAITER |
Application name | blocker_application_name for the BLOCKER waiter_application_name for the WAITER |
User ID | blocker_session_auth_id for BLOCKER waiter_session_auth_id for WAITER |
Lock wait time | Aggregation of blocker_lock_wait_time for BLOCKER Aggregation of waiter_lock_wait_time for WAITER |
Number of locks held | blocker_num_locks_held for BLOCKER waiter_num_locks_held for WAITER |
Lock mode | blocker_lock_mode for BLOCKER waiter_lock_mode for WAITER |
Event timestamp | bigint converted into readable format |
Number of waiting connections | Count of the total number of waiting connections that is added as children |
Database member | member for BLOCKER waiter_member for WAITER |