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.
SELECT COORD_PARTITION_NUM,
APPLICATION_HANDLE
FROM TABLE(WLM_GET_SERVICE_CLASS_WORKLOAD_OCCURRENCES_V97('','',-2))
WHERE SESSION_USER = 'USER1'
COORD_PARTITION_NUM APPLICATION_HANDLE
------------------------------- ----------------------------
2 131130
1 record(s) selected.
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(WLM_GET_SERVICE_CLASS_AGENTS_V97('','',131130,-2))
ORDER BY AGENT_TYPE, DBPART
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, so we need to investigate which object is being waited for and who is holding the lock on it.
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))
LOCK_NAME MEMBER TID HLD_APP
-------------------------- ------ ------ -------
00030005000000000280000452 0 1234 65564
00030005000000000280000452 1 5478 65564
00030005000000000280000452 2 4678 65564
3 record(s) selected.
SELECT SYSTEM_AUTH_ID,
APPLICATION_NAME AS APP_NAME,
WORKLOAD_NAME AS WORKLOAD,
WORKLOAD_OCCURRENCE_STATE AS WL_STATE
FROM TABLE(WLM_GET_SERVICE_CLASS_WORKLOAD_OCCURRENCES_V97('','',-2))
WHERE APPLICATION_HANDLE = 65564
SYSTEM_AUTH_ID APP_NAME WORKLOAD WL_STATE
-------------- -------- ----------------------- -----------
ZURBIE db2bp SYSDEFAULTUSERWORKLOAD UOWWAIT
1 record(s) selected
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.
REQ_APPLICATION_HANDLE, REQ_AGENT_TID, REQ_MEMBER, REQ_EXECUTABLE_ID
HLD_APPLICATION_HANDLE, HLD_MEMBER, ADDITIONAL_DETAILS
Column name | Data type | Description or monitor element |
---|---|---|
LOCK_WAIT_START_TIME | 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 internal use |
LOCK_OBJECT_TYPE | VARCHAR(32) | lock_object_type - Lock object type
waited on For possible values, see "lock_object_type - Lock object type waited on monitor element" |
LOCK_MODE | VARCHAR(3) | lock_mode - Lock mode If the application holding this lock cannot be found, a value of NULL is returned. |
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 | Reserved for internal use |
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_ |
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_ |
BIGINT | hld_application_handle - Holding application handle If the application holding this lock is unknown or cannot be found then a value of NULL is returned. |
HLD_MEMBER | SMALLINT | hld_member - Holding member |
ADDITIONAL_DETAILS | BLOB(100K) | Reserved for internal use |