DB2 Version 10.1 for Linux, UNIX, and Windows

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. It is a useful query for identifying locking problems. This administrative view replaces the SNAPLOCKWAIT administrative view which is deprecated in DB2® 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.