DB2 10.5 for Linux, UNIX, and Windows

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.


>>-MON_GET_LOCKS--(--search_args--,--member--)-----------------><

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_type
Returns 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_mode
Returns 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_status
Returns 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

One of the following authorities or privilege is required:

SYSADM authority
SYSMON authority

Default PUBLIC privilege

None

Example

In this sample scenario, the MON_GET_LOCKS and MON_GET_APPL_LOCKWAIT table functions are used to investigate the locking situation in the current connected database, on all members.

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

Table 1. Information returned by the MON_GET_LOCKS table function
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