DB2 Version 9.7 for Linux, UNIX, and Windows

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


>>-AM_GET_LOCK_RPT--(--agent_id--)-----------------------------><

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.

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 DYNIX HP HP64 HPIA HPIA64 LINUX LINUX390 LINUXIA64 LINUXPPC LINUXPPC64 LINUXX8664 LINUXZ64 MAC MVS_DRDA NT NT64 OS2 OS390 SCO SGI 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 NETBIOS NPIPE TCPIP (for DB2 Universal Database™, or DB2 UDB) TCPIP4 TCPIP6
CLIENT_NNAME	VARCHAR(128)	The client_nname monitor element is deprecated. The value returned is not a valid value.
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
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	The database partition from which the data was retrieved for this row.

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	The database partition from which the data was retrieved for this row.
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