LOCKS_HELD administrative view - Retrieve information about locks held

The LOCKS_HELD administrative view returns information about the current 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 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

Default PUBLIC privilege

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

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 is an example of 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 is an example of 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 is an example of 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	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
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` `COL_TABLE_SERIALIZE_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	dbpartitionnum - Database partition number monitor element
MEMBER	SMALLINT	member - Database member monitor element