DB2 Version 9.7 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.

Note: If your database was created in Version 9.7 before Fix Pack 1, to run this routine you must have already run the db2updv97 command. If your database was created before Version 9.7, it is not necessary to run the db2updv97 command (because the catalog update is automatically taken care of by the database migration). If you downgrade to Version 9.7, this routine will no longer work.

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

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 application handle 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.