DB2 Version 9.7 for Linux, UNIX, and Windows

LOCKS_HELD administrative view - Retrieve information about the locks held

Note: This administrative view has been deprecated and replaced by the MON_GET_APPL_LOCKWAIT table function - get information about locks for which an application is waiting, MON_GET_LOCKS table function - list all locks in the currently connected database, and MON_FORMAT_LOCK_NAME table function - format the internal lock name and return details.

The LOCKS_HELD administrative view returns information about the current locks held.

The schema is SYSIBMADM.

Authorization

One of the following authorizations is required:

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

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

SYSMON
SYSCTRL
SYSMAINT
SYSADM

Example

Example 1: List the total number of locks held by each table in the database SAMPLE.

SELECT TABSCHEMA, TABNAME, COUNT(*) AS NUMBER_OF_LOCKS_HELD 
   FROM SYSIBMADM.LOCKS_HELD WHERE DB_NAME = 'SAMPLE' 
   GROUP BY DBPARTITIONNUM, TABSCHEMA, TABNAME

The following example is a sample output for this query.

TABSCHEMA      TABNAME       NUMBER_OF_LOCKS_HELD
----------...- ---------...- --------------------
JESSICAE       EMPLOYEE                         5
JESSICAE       EMP_RESUME                       1
JESSICAE       ORG                              3

Example 2: List all the locks that have not escalated in the currently connected database, SAMPLE.

SELECT AGENT_ID, TABSCHEMA, TABNAME, LOCK_OBJECT_TYPE, LOCK_MODE, 
   LOCK_STATUS FROM SYSIBMADM.LOCKS_HELD WHERE LOCK_ESCALATION = 0 
   AND DBPARTITIONNUM = 0

The following example is a sample output for this query.

AGENT_ID     TABSCHEMA     TABNAME      LOCK_OBJECT_TYPE   LOCK_MODE  LOCK_STATUS
--------...- ---------...- --------...- ------------------ ---------- -----------
         680 JESSICAE      EMPLOYEE     INTERNALV_LOCK     S          GRNT
         680 JESSICAE      EMPLOYEE     INTERNALP_LOCK     S          GRNT

Example 3: List lock information for the locks that are currently held by the application with agent ID 310.

SELECT TABSCHEMA, TABNAME, LOCK_OBJECT_TYPE, LOCK_MODE, LOCK_STATUS, 
   LOCK_ESCALATION FROM SYSIBMADM.LOCKS_HELD WHERE AGENT_ID = 310

The following example is a sample output for this query.

TABSCHEMA     TABNAME      LOCK_OBJECT_TYPE   LOCK_MODE  LOCK_STATUS
---------...- --------...- ------------------ ---------- -----------
JESSICAE      EMP_RESUME   TABLE_LOCK         S          GRNT
JESSICAE      EMPLOYEE     ROW_LOCK           S          GRNT

Information returned

Table 1. Information returned by the LOCKS_HELD 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
TAB_FILE_ID	BIGINT	table_file_id - Table file identification
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_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_STATUS	VARCHAR(10)	lock_status - Lock status . This interface returns a text identifier based on the defines in sqlmon.h and is one of: CONV GRNT
LOCK_ESCALATION	SMALLINT	lock_escalation - Lock escalation
DBPARTITIONNUM	SMALLINT	The database partition from which the data was retrieved for this row.