The LOCKWAITS administrative view returns information about DB2® agents working on behalf of applications that are waiting to obtain locks.
The schema is SYSIBMADM.
In a non-restrictive database, SELECT privilege is granted to PUBLIC when the view is automatically created.
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
TABSCHEMA TABNAME LOCK_OBJECT_TYPE LOCK_MODE ...
--------- -------...- ---------------- ---------- ...
JESSICAE T1 ROW_LOCK X ...
1 record(s) selected.
... LOCK_MODE_REQUESTED AGENT_ID_HOLDING_LK
... ------------------- --------------------
... NS 7
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
TABSCHEMA TABNAME NUM_OF_LOCK_REQUESTS_WAITING DBPARTITIONNUM
--------- -------...- ---------------------------- --------------
JESSICAE T3 2 0
JESSICAE T1 1 0
JESSICAE T2 1 0
3 record(s) selected.
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:
|
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:
|
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:
|
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 |