The MON_GET_LOCKS table function returns a list of all locks in the currently connected database.
To get information about locks, use the MON_GET_LOCKS, MON_FORMAT_LOCK_NAME, and MON_GET_APPL_LOCKWAIT table functions, and the MON_LOCKWAIT administrative view instead of the SNAPLOCKWAIT administrative view and SNAP_GET_LOCKWAIT table function, the SNAPLOCK administrative view and SNAP_GET_LOCK table function, and the LOCKS_HELD administrative view which are deprecated in Fix Pack 1 of Version 9.7.
The schema is SYSPROC.
SQLCODE -171 is returned for an invalid key-value pair.
SQLCODE -204 is returned if the table does not exist.
CLOB('<application_handle>123</application_handle>
<lock_object_type>Table:Row</lock_object_type>
<lock_mode>S:X</lock_mode>')
Returns a list of all locks that are currently held or are in the process of being acquired by the specified application handle. Only a single occurrence of the key value can be specified. The value is specified as an INTEGER. For example:
CLOB('<application_handle>145</application_handle>')
Returns a list of all locks that match the specified lock name. Only a single occurrence of the key value can be specified. The value is specified as a string of maximum length 32. For example:
CLOB('<lock_name>00030005000000000280000452</lock_name>')
CLOB('<lock_object_type>Table:Chunk:Plan</lock_object_type>')
For a list of possible input values, see "lock_object_type - Lock object type waited on monitor element".
CLOB('<lock_mode>IS:IN:U</lock_mode>')
For a list of possible input values, see "lock_mode - Lock mode monitor element".
CLOB('<lock_status>W</lock_status>')
For a list of possible input values, see "lock_status - Lock status monitor element".
Returns a list of all locks that are qualified by the specified schema name. The table_name key must also be specified. Only a single occurrence of the key value can be specified. The value is specified as a string of maximum length 128.
Returns a list of all locks that reference the specified table. The table_schema key must also be specified. Only a single occurrence of the key value can be specified. The value is specified as a string of maximum length 128. For example:
CLOB('<table_schema>USER1</table_schema>
<table_name>INVENTORY</table_name>')
CLOB('<lock_object_type>Table:Row</lock_object_type>')
CLOB('<application_handle>123</application_handle>
<table_schema>USER1</table_schema>
<table_name>T1</table_name>')
CLOB('<lock_mode>S</lock_mode>
<lock_status>G</lock_status>
<lock_object_type>Table:Row:Bufferpool</lock_object_type>')
None
SELECT lock_name,
hld_member,
lock_status,
hld_application_handle FROM
TABLE (MON_GET_APPL_LOCKWAIT(NULL, -2))
LOCK_NAME HLD_MEMBER LOCK_STATUS HLD_APPLICATION_HANDLE
-------------------------- ---------- ----------- ----------------------
00030005000000000280000452 -2 W
00030005000000000280000452 -2 W
00030005000000000280000452 -2 W
3 record(s) selected.
The records that show HLD_MEMBER is -2 indicate that the lock 0x00030005000000000280000452 is being held at a remote member.
SELECT lock_name,
member,
lock_status,
application_handle FROM
TABLE (MON_GET_LOCKS(
CLOB('<lock_name>00030005000000000280000452</lock_name>'),
-2))
LOCK_NAME MEMBER LOCK_STATUS APPLICATION_HANDLE
-------------------------- ------ ----------- ------------------
00030005000000000280000452 0 W 12562
00030005000000000280000452 1 W 12562
00030005000000000280000452 2 G 65545
00030005000000000280000452 3 W 12562
4 record(s) selected.
To find out more about the application holding the lock, you can call the WLM_GET_SERVICE_CLASS_WORKLOAD_OCCURRENCES or WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table functions.
Column name | Data type | Description or monitor element |
---|---|---|
APPLICATION_HANDLE | BIGINT | application_handle - Application handle If the LOCK_STATUS column is G, this represents the application that is currently holding the lock. If the LOCK_STATUS column is W or C, this represents the application that is currently waiting to acquire the lock. |
MEMBER | SMALLINT | member - Database member from which the data was retrieved for this row. |
LOCK_NAME | VARCHAR(32) | lock_name - Lock name |
LOCK_OBJECT_TYPE_ID | CHAR(1) FOR BIT DATA | Reserved for future use |
LOCK_OBJECT_TYPE | VARCHAR(32) | lock_object_type - Lock object type If the LOCK_STATUS column is G, this represents the type of object that the application is currently holding. If the LOCK_STATUS column is W or C, then this represents the type of object that the application is currently waiting to acquire. For possible input values, see "lock_object_type - Lock object type waited on monitor element". |
LOCK_MODE | VARCHAR(3) | lock_mode - Lock mode If the LOCK_STATUS column is G, this represents the mode that the application is currently holding the lock in. If the LOCK_STATUS column is W or C, this represents the mode that the application is currently waiting to acquire the lock in. If the mode is unknown, a value of NULL is returned for this column. |
LOCK_CURRENT_MODE | VARCHAR(3) | lock_current_mode - Original Lock Mode
Before Conversion If the mode is unknown, a value of NULL is returned for this column. |
LOCK_STATUS | CHAR(1) | lock_status - Lock status |
LOCK_ATTRIBUTES | CHAR(16) | lock_attributes - Lock attributes |
LOCK_RELEASE_FLAGS | CHAR(16) | lock_release_flags - Lock release flags monitor element |
LOCK_RRIID | BIGINT | Reserved for internal use |
LOCK_COUNT | BIGINT | Lock_count monitor element |
LOCK_HOLD_COUNT | BIGINT | lock_hold_count monitor element |
TBSP_ID | BIGINT | tablespace_id - Table space ID For locks that do not reference a table space, a value of NULL is returned. |
TAB_FILE_ID | BIGINT | table_file_id - Table file ID |