MON_GET_APPL_LOCKWAIT table function - Get information about locks for which an application is waiting
The MON_GET_APPL_LOCKWAIT table function returns information about all locks that each application's agents (that are connected to the current database) are waiting to acquire.
To get information about locks, use the MON_GET_APPL_LOCKWAIT, MON_FORMAT_LOCK_NAME, and MON_GET_LOCKS, table functions instead of the SNAPLOCKWAIT administrative view and SNAP_GET_LOCKWAIT table function, and the SNAPLOCK administrative view and SNAP_GET_LOCK table function, which are deprecated in Fixpack 1 of version 9.7.
The schema is SYSPROC.
Routine parameters
- application_handle
- An optional input parameter of type BIGINT that specifies a valid application handle in the same database as the one to which you are currently connected. If the argument is null, locks are retrieved for all applications that are currently waiting for locks to be acquired.
- member
- An input parameter of type INTEGER that specifies a valid member in the same instance as the currently connected database. Specify -1 for the current database member, or -2 for all active members. If the NULL value is specified, -1 is set.
- system_appls
- An input parameter of type SMALLINT that specifies whether information
for system applications is to be returned. The following values are
valid:
- 0 or NULL: System application information is not returned. NULL is the default if a value for system_appls is not provided.
- 1: User and system application information is returned.
Authorization
- EXECUTE privilege on the routine
- DATAACCESS authority
- DBADM authority
- SQLADM authority
Default PUBLIC privilege
None
Information returned
- The following columns represent details about the lock that the
application is currently waiting to acquire:
LOCK_WAIT_START_TIME, LOCK_NAME, LOCK_OBJECT_TYPE, LOCK_MODE, LOCK_CURRENT_MODE, LOCK_MODE_REQUESTED, LOCK_STATUS, LOCK_ESCALATION, LOCK_ATTRIBUTES, LOCK_RRIID, LOCK_COUNT, TBSP_ID, TAB_FILE_ID, SUBSECTION_NUMBER.
- The following columns represent details about the application
that is waiting to acquire this lock.
REQ_APPLICATION_HANDLE, REQ_AGENT_TID, REQ_MEMBER, REQ_EXECUTABLE_ID
- The following columns represent details about the application
that is currently holding the lock.
HLD_APPLICATION_HANDLE, HLD_MEMBER, ADDITIONAL_DETAILS
Column name | Data type | Description or monitor element |
---|---|---|
LOCK_WAIT_START_TIME | TIMESTAMP | lock_wait_start_time - Lock Wait Start Timestamp |
LOCK_NAME | VARCHAR(32) | lock_name - Lock name The internal name can be formatted using the MON_FORMAT_LOCK_NAME table function to obtain details regarding the lock. For example, the table and table space that the lock references can be found, if this is a table lock. |
LOCK_OBJECT_TYPE_ID | CHAR(1) FOR BIT DATA | Reserved for future use |
LOCK_OBJECT_TYPE | VARCHAR(32) | lock_object_type - Lock object type
waited on For possible values, see |
LOCK_MODE | VARCHAR(3) | lock_mode - Lock mode If the application holding this lock cannot be found, a value of NULL is returned. For a global lockwait, this value is NULL. |
LOCK_CURRENT_MODE | VARCHAR(3) | lock_current_mode - Original Lock Mode
Before Conversion If no conversion took place, then a value of NULL is returned. |
LOCK_MODE_REQUESTED | VARCHAR(3) | lock_mode_requested - Lock mode requested |
LOCK_STATUS | CHAR(1) | lock_status - Lock status |
LOCK_ESCALATION | CHAR(1) | lock_escalation - Lock escalation |
LOCK_ATTRIBUTES | CHAR(16) | lock_attributes - Lock attributes |
LOCK_RRIID | BIGINT | lock_count - Lock count monitor element |
LOCK_COUNT | BIGINT | lock_count - Lock count monitor element |
TBSP_ID | BIGINT | tablespace_id - Table space ID |
TAB_FILE_ID | BIGINT | table_file_id - Table file ID |
SUBSECTION_NUMBER | BIGINT | ss_number - Subsection Number If the subsection number is not available, then a value of NULL is returned. |
REQ_APPLICATION_HANDLE | BIGINT | |
REQ_AGENT_TID | BIGINT | req_agent_tid - Requesting agent TID |
REQ_MEMBER | SMALLINT | req_member - Requesting member |
REQ_EXECUTABLE_ID | VARCHAR (32) FOR BIT DATA | req_executable_id - Requesting executable ID |
HLD_APPLICATION_HANDLE | BIGINT | hld_application_handle - Holding application handle The value 0 indicates that the application no longer exists. The value can appear when incomplete transactions are on the database after a system crash. It means that the lock is held by either a transaction that is being rolled back or by an indoubt transaction that must be resolved. If the application holding this lock is unknown or cannot be found then a value of NULL is returned. For a global lockwait, this value is NULL. |
HLD_MEMBER | SMALLINT | hld_member - Holding member |
IS_SYSTEM_APPL | SMALLINT | is_system_appl - Is System Application |
Example
- Use
the MON_GET_CONNECTION table function to look up the application handle
for all connections with the SESSION_USER value of USER1:
SELECT COORD_PARTITION_NUM, APPLICATION_HANDLE FROM TABLE(MON_GET_CONNECTION(NULL,-2)) WHERE SESSION_USER = 'USER1'
This query returns the following output:COORD_PARTITION_NUM APPLICATION_HANDLE ------------------------------- ---------------------------- 2 131130 1 record(s) selected.
- Use
the MON_GET_AGENT table function to obtain current information about
all agents working for this connection, on all database partitions:
SELECT SUBSTR(CHAR(DBPARTITIONNUM),1,3) AS DBPART, SUBSTR(CHAR(APPLICATION_HANDLE),1,7) AS APP_ID, SUBSTR(CHAR(WORKLOAD_OCCURRENCE_ID),1,7) AS WLO_ID, SUBSTR(CHAR(AGENT_TID),1,7) AS AGENT_ID, SUBSTR(CHAR(AGENT_TYPE),1,12) AS AGENT_TYPE, SUBSTR(AGENT_STATE,1, 8) AS STATE, SUBSTR(EVENT_TYPE,1, 8) AS EV_TYPE, SUBSTR(EVENT_OBJECT,1,12) AS EV_OBJECT FROM TABLE(MON_GET_AGENT('','',131130,-2)) ORDER BY AGENT_TYPE, DBPART
This query returns the following output:DBPART APP_ID WLO_ID AGENT_ID AGENT_TYPE STATE EV_TYPE EV_OBJECT ------ ------- ------- --------- ----------- ------ -------- ---------- 2 131130 1 3110 COORDINATOR ACTIVE WAIT REQUEST 0 131130 1 7054 PDBSUBAGENT ACTIVE ACQUIRE LOCK 1 131130 1 5709 PDBSUBAGENT ACTIVE ACQUIRE LOCK 2 131130 1 5960 PDBSUBAGENT ACTIVE ACQUIRE LOCK 4 record(s) selected.
An event of type ACQUIRE on an event object of type LOCK indicates a lock wait scenario. Now you can investigate which object is being waited for and which process is holding the lock on it.
- To determine all locks that the application is waiting for, call
the MON_GET_APPL_LOCKWAIT table function with application handle 131130 and
member -2 as input parameters.
SELECT lock_name, hld_member AS member, hld_agent_tid as TID, hld_application_handle AS HLD_APP FROM TABLE (MON_GET_APPL_LOCKWAIT(131130, -2))
This query returns the following output:LOCK_NAME MEMBER TID HLD_APP -------------------------- ------ ------ ------- 00030005000000000280000452 0 1234 65564 00030005000000000280000452 1 5478 65564 00030005000000000280000452 2 4678 65564 3 record(s) selected.
- Call
the MON_GET_CONNECTION table function to find out more about the application
that is holding the lock (this application has an application handle
of 65564).
SELECT SYSTEM_AUTH_ID, APPLICATION_NAME AS APP_NAME, WORKLOAD_OCCURRENCE_STATE AS WL_STATE FROM TABLE(MON_GET_CONNECTION(NULL,-2)) WHERE APPLICATION_HANDLE = 65564
This query returns the following output:SYSTEM_AUTH_ID APP_NAME WL_STATE -------------- -------- ----------- ZURBIE db2bp UOWWAIT