MON_GET_LOCKS table function - List all locks in the currently connected database
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.
Table function parameters
- search_args
- An input parameter of type CLOB(1K) that represents a list of key-value pairs. If the list is empty or NULL, all locks in the currently connected database are returned. Otherwise, all locks that match all of the conditions represented by the list of key-value pairs are returned. A key-value pair must follow this format:
- A key is a string that consists of an opening tag, followed by the value, followed by a closing tag.
- An opening tag consists of an opening angle bracket, followed by the key name, followed by a closing angle bracket. No spaces are allowed.
- A closing tag consists of an opening angle bracket, followed by a forward slash, followed by the key name, followed by a closing angle bracket. No spaces are allowed.
- All keys are case-sensitive and can only be specified once in the search_args parameter.
- The order of the keys does not matter.
SQLCODE -171 is returned for an invalid key-value pair.
SQLCODE -204 is returned if the table does not exist.
An AND operation is performed between different keys. An OR operation is performed between multiple values of the same key. For example, the following use of the search_args parameter returns a list of all locks of type Table or Row, that are held, or waiting to be acquired, in either Shared or Exclusive mode, by the application with the handle 123:CLOB('<application_handle>123</application_handle> <lock_object_type>Table:Row</lock_object_type> <lock_mode>S:X</lock_mode>')
The available keys for the MON_GET_LOCKS table function are as follows:- application_handle
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>')
- lock_name
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>')
- lock_object_typeReturns a list of all locks that match the specified lock object type. Multiple occurrences of the key value can be specified (to a maximum of 5). Each value (case insensitive) must be separated by a colon (:) and is specified as a string of a maximum length of 32 characters. For example:
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".
- lock_modeReturns a list of all locks that match the specified lock mode. Multiple occurrences of the key value can be specified (to a maximum of 5). Each value (case insensitive) is separated by a colon (:) and is specified as a string of maximum length 3. For example:
CLOB('<lock_mode>IS:IN:U</lock_mode>')
For a list of possible input values, see "lock_mode - Lock mode monitor element".
- lock_statusReturns a list of all locks in the specified status. Only a single occurrence of the key value can be specified. The value is specified as a character.
CLOB('<lock_status>W</lock_status>')
For a list of possible input values, see "lock_status - Lock status monitor element".
- table_schema
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.
- table_name
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>')
The following examples demonstrate how to use key-value pairs in the search_args parameter.- To search for all ROW and TABLE locks:
CLOB('<lock_object_type>Table:Row</lock_object_type>')
- To search for all locks that application handle 123 is holding
or waiting to acquire that reference table T1, and were created by
user USER1:
CLOB('<application_handle>123</application_handle> <table_schema>USER1</table_schema> <table_name>T1</table_name>')
- To search for all TABLE, ROW, and BUFFERPOOL locks that are currently
held in Shared mode:
CLOB('<lock_mode>S</lock_mode> <lock_status>G</lock_status> <lock_object_type>Table:Row:Bufferpool</lock_object_type>')
- member
- An input argument of type INTEGER that specifies from which member the data is returned. Specify -1 for the current member, and -2 for all active members.
Authorization
- SYSADM authority
- SYSMON authority
Default PUBLIC privilege
None
Example
- Call the MON_GET_APPL_LOCKWAIT table function to determine all
the locks that are waiting to be acquired in the current connected
database, on all members:
SELECT lock_name, hld_member, lock_status, hld_application_handle FROM TABLE (MON_GET_APPL_LOCKWAIT(NULL, -2))
This query returns the following output: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.
- Call the MON_GET_LOCKS table function to determine the holder
of the lock, by specifying the lock name, 0x00030005000000000280000452,
as the search argument:
SELECT lock_name, member, lock_status, application_handle FROM TABLE (MON_GET_LOCKS( CLOB('<lock_name>00030005000000000280000452</lock_name>'), -2))
This query returns the following output: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.
Information returned
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 |