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 - Schema name of the object. For locks that do not reference a table, NULL is returned. Also, for certain tables, such as range partition tables, the TBSPACEID and TABLEID monitor elements can have negative values. In these situations, NULL is returned.
TABNAME	VARCHAR(128)	table_name - Unqualified name of the object For locks that do not reference a table, NULL is returned. Also, for certain tables, such as range partition tables, the TBSPACEID and TABLEID monitor elements can have negative values. In these situations, 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.
TBSPACEID	BIGINT	Internal logical identifier for the primary table space for this object. For certain tables, such as range partition tables, the TBSPACEID monitor element can have a negative value. In these situations, the TABSCHEMA field is null. By using this monitor element when manually joining with, or running a query against, `syscat.tables`, you can retrieve the value for TABSCHEMA.
TABLEID	BIGINT	Internal logical object identifier. For certain tables, such as range partition tables, the TABLEID monitor element can have a negative value. In these situations, the TABNAME field is null. By using this monitor element when manually joining with, or running a query against, `syscat.tables`, you can retrieve the value for TABNAME.
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.
REQ_IS_SYSTEM_APPL	SMALLINT	Whether or not the application waiting to acquire the lock is a system application.
HLD_IS_SYSTEM_APPL	SMALLINT	Whether or not the application holding this lock is a system application.

Usage Notes

The MON_LOCKWAITS administrative view reports system applications that are blocked on a lock. The REQ_IS_SYSTEM_APPL column can be used to control whether or not system applications are to be reported. For example, if only blocked user applications are desired the following query can be used:

select * from sysibmadm.mon_lockwaits
 where req_is_system_appl = 0;