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.
Note:
The timestamp is represented in one of the following formats:
  • 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.

Note: The statement that is collected for waiting application is the correct statement. However, the statement that is collected for the blocking application might not be correct, because the statement executed on the blocking application might have been finished, and a new statement might be running when the snapshot is taken.

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.

The metrics are mapped to the following columns in the repository table:
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