LOCKWAITS administrative view - Retrieve current lockwaits information

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

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 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

Default PUBLIC privilege

In a non-restrictive database, SELECT privilege is granted to PUBLIC when the view is automatically created.

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 is an example of 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 is an example of 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 Date and time the report was generated.
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
  • COL_TABLE_SERIALIZE_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 dbpartitionnum - Database partition number monitor element
MEMBER SMALLINT member - Database member monitor element