MON_LOCKWAITS administrative view - Retrieve metrics for applications that are waiting to obtain locks

The MON_LOCKWAITS administrative view returns information about agents working on behalf of applications that are waiting to obtain locks in the currently connected database.

This administrative view is useful for identifying locking problems.

This administrative view replaces the SNAPLOCKWAIT administrative view, which is deprecated.version 9.7 Fix Pack 1 and might be discontinued in a future release.

The schema is SYSIBMADM.

Authorization

One of the following authorizations is required:
  • SELECT privilege on the MON_LOCKWAITS administrative view
  • CONTROL privilege on the MON_LOCKWAITS administrative view
  • DATAACCESS authority

Default PUBLIC privilege

None

Information returned

Table 1. Information returned by the MON_LOCKWAITS administrative view
Column name Data type Description or Monitor element
LOCK_NAME VARCHAR(32)

lock_name - Lock name

You can use the MON_FORMAT_LOCK_ NAME routine to format this internal binary lock name and obtain more details regarding the lock, such as the table and table space that a table lock references.
LOCK_OBJECT_TYPE VARCHAR(32) lock_object_type - Lock object type waited on
LOCK_WAIT_ELAPSED_TIME INTEGER The time elapsed since the agent started waiting to obtain the lock. This value is given in seconds.
TABSCHEMA VARCHAR(128)

table_schema - Table schema name

For locks that do not reference a table, NULL is returned.
TABNAME VARCHAR(128)

table_name - Table name

For locks that do not reference a table, NULL is returned.
DATA_PARTITION_ID INTEGER

data_partition_id - Data Partition identifier

This element is only applicable to partitioned tables and partitioned indexes. When returning lock level information, a value of -1 represents a lock which controls access to the whole table.
LOCK_MODE VARCHAR(10) lock_mode - Lock mode
LOCK_CURRENT_MODE VARCHAR(10)

lock_current_mode - Original lock mode before conversion

If the LOCK_STATUS is not "C" (converting), then a value of NULL is returned.
LOCK_MODE_REQUESTED VARCHAR(10) lock_mode_requested - Lock mode requested
REQ_APPLICATION_HANDLE BIGINT req_application_handle - Requesting application handle
REQ_AGENT_TID BIGINT req_agent_tid - Requesting agent TID
REQ_MEMBER SMALLINT req_member - Requesting member
REQ_APPLICATION_NAME VARCHAR(128) The name of the application running at the client that is waiting to acquire this lock.
REQ_USERID VARCHAR(128) The current authorization ID for the session being used by the application that is waiting to acquire this lock.
REQ_STMT_TEXT CLOB(2MB)

SQL statement section that the application waiting to acquire the lock is executing.

For non-SQL activities, a 0-length string value is returned.
HLD_APPLICATION_HANDLE BIGINT

hld_application_handle - Holding applicationHANDLE

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
HLD_APPLICATION_NAME VARCHAR(128)

The name of the application running at the client that is holding this lock.

If the application holding this lock is unknown or cannot be found then a 0-length string value is returned.
HLD_USERID VARCHAR(128) The current authorization ID for the session being used by the application that is holding this lock.
HLD_CURRENT_STMT_TEXT CLOB(2MB) SQL statement text that is currently associated with the application that is holding the lock. Note that this is not necessarily the statement that is causing the lock.