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
- SELECT privilege on the MON_LOCKWAITS administrative view
- CONTROL privilege on the MON_LOCKWAITS administrative view
- DATAACCESS authority
Default PUBLIC privilege
None
Information returned
Column name | Data type | Description or Monitor element |
---|---|---|
LOCK_NAME | VARCHAR(32) | 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) |
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. |