DB2 Version 9.7 for Linux, UNIX, and Windows

LOCKWAITS administrative view - Retrieve current lockwaits information

Note: This administrative view has been deprecated and replaced by the MON_LOCKWAITS administrative view - Retrieve metrics for applications that are waiting to obtain locks.

The LOCKWAITS administrative view returns information about DB2 agents working on behalf of applications that are waiting to obtain locks.

The schema is SYSIBMADM.

Authorization

One of the following authorizations is required:

SELECT privilege on the LOCKWAITS administrative view
CONTROL privilege on the LOCKWAITS administrative view
DATAACCESS authority

In addition, to access snapshot monitor data, one of the following authorities is also required:

SYSMON
SYSCTRL
SYSMAINT
SYSADM

Examples

Example 1: List information for all the lock waits for application with agent ID 89.

SELECT SUBSTR(TABSCHEMA,1,8) AS TABSCHEMA, SUBSTR(TABNAME,1,15) AS TABNAME, 
   LOCK_OBJECT_TYPE, LOCK_MODE, LOCK_MODE_REQUESTED, AGENT_ID_HOLDING_LK 
   FROM SYSIBMADM.LOCKWAITS WHERE AGENT_ID = 89

The following example is a sample output for this query.

TABSCHEMA TABNAME     LOCK_OBJECT_TYPE LOCK_MODE  ...
--------- -------...- ---------------- ---------- ...
JESSICAE  T1          ROW_LOCK         X          ...
                                                     
  1 record(s) selected.

Output for this query (continued).

... LOCK_MODE_REQUESTED AGENT_ID_HOLDING_LK  
... ------------------- -------------------- 
... NS                                     7

Example 2: List the total number of outstanding lock requests per table in the database SAMPLE. By sorting the output by number of requests, tables with the highest contention can be identified.

SELECT SUBSTR(TABSCHEMA,1,8) AS TABSCHEMA, SUBSTR(TABNAME, 1, 15) 
   AS TABNAME, COUNT(*) AS NUM_OF_LOCK_REQUESTS_WAITING, 
   DBPARTITIONNUM 
   FROM SYSIBMADM.LOCKWAITS WHERE DB_NAME = 'SAMPLE' 
   GROUP BY TABSCHEMA, TABNAME, DBPARTITIONNUM 
   ORDER BY NUM_OF_LOCK_REQUESTS_WAITING DESC

The following example is a sample output for this query.

TABSCHEMA TABNAME     NUM_OF_LOCK_REQUESTS_WAITING DBPARTITIONNUM
--------- -------...- ---------------------------- --------------
JESSICAE  T3                                     2              0
JESSICAE  T1                                     1              0
JESSICAE  T2                                     1              0
                                                                 
  3 record(s) selected.

Information returned

Table 1. Information returned by the LOCKWAITS administrative view
Column name	Data type	Description or corresponding monitor element
SNAPSHOT_TIMESTAMP	TIMESTAMP	snapshot_timestamp - Snapshot timestamp monitor element
DB_NAME	VARCHAR(128)	db_name - Database name
AGENT_ID	BIGINT	agent_id - Application handle (agent ID)
APPL_NAME	VARCHAR(256)	appl_name - Application name
AUTHID	VARCHAR(128)	auth_id - Authorization ID
TBSP_NAME	VARCHAR(128)	tablespace_name - Table space name
TABSCHEMA	VARCHAR(128)	table_schema - Table schema name
TABNAME	VARCHAR(128)	table_name - Table name
SUBSECTION_NUMBER	BIGINT	ss_number - Subsection number
LOCK_OBJECT_TYPE	VARCHAR(18)	lock_object_type - Lock object type waited on . This interface returns a text identifier based on the defines in sqlmon.h and is one of: AUTORESIZE_LOCK AUTOSTORAGE_LOCK BLOCK_LOCK EOT_LOCK INPLACE_REORG_LOCK INTERNAL_LOCK INTERNALB_LOCK INTERNALC_LOCK INTERNALJ_LOCK INTERNALL_LOCK INTERNALO_LOCK INTERNALQ_LOCK INTERNALP_LOCK INTERNALS_LOCK INTERNALT_LOCK INTERNALV_LOCK KEYVALUE_LOCK ROW_LOCK SYSBOOT_LOCK TABLE_LOCK TABLE_PART_LOCK TABLESPACE_LOCK XML_PATH_LOCK
LOCK_WAIT_START_TIME	TIMESTAMP	lock_wait_start_time - Lock wait start timestamp
LOCK_NAME	VARCHAR(32)	lock_name - Lock name
LOCK_MODE	VARCHAR(10)	lock_mode - Lock mode . This interface returns a text identifier based on the defines in sqlmon.h and is one of: IN IS IX NON (if no lock) NS NW S SIX U X Z
LOCK_MODE_REQUESTED	VARCHAR(10)	lock_mode_requested - Lock mode requested . This interface returns a text identifier based on the defines in sqlmon.h and is one of: IN IS IX NON (if no lock) NS NW S SIX U X Z
AGENT_ID_HOLDING_LK	BIGINT	agent_id_holding_lock - Agent ID holding lock
APPL_ID_HOLDING_LK	VARCHAR(128)	appl_id_holding_lk - Application ID holding lock
LOCK_ESCALATION	SMALLINT	lock_escalation - Lock escalation
DBPARTITIONNUM	SMALLINT	The database partition from which the data was retrieved for this row.