AM_GET_LOCK_RPT procedure - Retrieve application lock details

The AM_GET_LOCK_RPT procedure returns lock details for an application in three output result sets.

Syntax

Important: The related activity monitor routines is deprecated and might be removed in a future release. For more information, see Activity monitor routines have been deprecated.

The schema is SYSPROC.

Procedure parameter

agent_id

An input argument of type BIGINT that specifies the agent ID of the application whose lock details are to be returned.

Authorization

SYSMON authority
EXECUTE privilege on the AM_GET_LOCK_RPT procedure.

Default PUBLIC privilege

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

Example

CALL SYSPROC.AM_GET_LOCK_RPT(68)

Usage note

The DFT_MON_LOCK monitor switch must be turned on for this procedure to return any information.

Information returned

The procedure returns three result sets: one for application general information; one for locks that the application holds; and one for locks that the application is waiting for.

Table 1. General application information returned by the AM_GET_LOCK_RPT procedure
Column name	Data Type	Description
AGENT_ID	BIGINT	agent_id - Application handle (agent ID)
APPL_NAME	VARCHAR(256)	appl_name - Application name
PRIMARY_AUTH_ID	VARCHAR(128)	auth_id - Authorization ID
APPL_ID	VARCHAR(128)	appl_id - Application ID
APPL_STATUS	VARCHAR(22)	appl_status - Application status . This interface returns a text identifier based on the defines in sqlmon.h, and is one of: `BACKUP` `COMMIT_ACT` `COMP` `CONNECTED` `CONNECTPEND` `CREATE_DB` `DECOUPLED` `DISCONNECTPEND` `INTR` `IOERROR_WAIT` `LOAD` `LOCKWAIT` `QUIESCE_TABLESPACE` `RECOMP` `REMOTE_RQST` `RESTART` `RESTORE` `ROLLBACK_ACT` `ROLLBACK_TO_SAVEPOINT` `TEND` `THABRT` `THCOMT` `TPREP` `UNLOAD` `UOWEXEC` `UOWWAIT` `WAITFOR_REMOTE`
COORD_NODE_NUM	SMALLINT	coord_node - Coordinating node
SEQUENCE_NO	VARCHAR(4)	sequence_no - Sequence number
CLIENT_PRDID	VARCHAR(128)	client_prdid - Client product/version ID
CLIENT_PID	BIGINT	client_pid - Client process ID
CLIENT_PLATFORM	VARCHAR(12)	client_platform - Client operating platform . This interface returns a text identifier based on the defines in sqlmon.h, `AIX` `AIX64` `AS400_DRDA` `DOS` `HP` `HP64` `HPIA` `HPIA64` `LINUX` `LINUX390` `LINUXIA64` `LINUXPPC` `LINUXPPC64` `LINUXX8664` `LINUXZ64` `MAC` `MVS_DRDA` `NT` `NT64` `OS2` `OS390` `SCO` `SNI` `SUN` `SUN64` `UNKNOWN` `UNKNOWN_DRDA` `VM_DRDA` `VSE_DRDA` `WINDOWS` `WINDOWS95`
CLIENT_PROTOCOL	VARCHAR(10)	client_protocol - Client communication protocol . This interface returns a text identifier based on the defines in sqlmon.h, `CPIC` `LOCAL` `NPIPE` `TCPIP` (for Db2®) `TCPIP4` `TCPIP6`
CLIENT_NNAME	VARCHAR(128)	client_nname - Client name monitor element
LOCKS_HELD	BIGINT	locks_held - Locks held
LOCK_WAIT_START_TIME	TIMESTAMP	lock_wait_start_time - Lock wait start timestamp
LOCK_WAIT_TIME	BIGINT	lock_wait_time - Time waited on locks
LOCK_WAITS	BIGINT	lock_waits - Lock waits
LOCK_TIMEOUTS	BIGINT	lock_timeouts - Number of lock timeouts
LOCK_ESCALS	BIGINT	lock_escals - Number of lock escalations
X_LOCK_ESCALS	BIGINT	x_lock_escals - Exclusive lock escalations
DEADLOCKS	BIGINT	deadlocks - Deadlocks detected

Table 2. Locks held information returned by the AM_GET_LOCK_RPT procedure
Column name	Data Type	Description
TBSP_NAME	VARCHAR(128)	tablespace_name - Table space name
TABSCHEMA	VARCHAR(128)	table_schema - Table schema name
TABNAME	VARCHAR(128)	table_name - Table name
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_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
LOCK_NAME	VARCHAR(32)	lock_name - Lock name
DBPARTITIONNUM	SMALLINT	dbpartitionnum - Database partition number monitor element

Table 3. Locks wait information returned by the AM_GET_LOCK_RPT procedure
Column name	Data Type	Description
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_WAIT_START_TIME	TIMESTAMP	lock_wait_start_time - Lock wait start timestamp
DBPARTITIONNUM	SMALLINT	dbpartitionnum - Database partition number monitor element
TBSP_NAME	VARCHAR(128)	tablespace_name - Table space name
TABSCHEMA	VARCHAR(128)	table_schema - Table schema name
TABNAME	VARCHAR(128)	table_name - Table name
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_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`
LOCK_ESCALATION	SMALLINT	lock_escalation - Lock escalation