DB2 Version 9.7 for Linux, UNIX, and Windows

SNAPSHOT_LOCKWAIT table function

Returns lock waits information from an application snapshot.

Note: This table function has been deprecated and replaced by the SNAPLOCKWAIT administrative view and SNAP_GET_LOCKWAIT table function - Retrieve lockwait logical data group snapshot information.


>>-SNAPSHOT_LOCKWAIT--(--dbname--,--dbpartitionnum--)----------><

The schema is SYSPROC.

Table function parameters

dbname: An input argument of type VARCHAR(255) that specifies a valid database name in the same instance as the currently connected database when calling this function. Specify a database name that has a directory entry type of either "Indirect" or "Home", as returned by the LIST DATABASE DIRECTORY command. Specify the null value to take the snapshot from all databases under the database instance.
dbpartitionnum: An input argument of type INTEGER that specifies a valid database partition number. Specify -1 for the current database partition, or -2 for all active database partitions. An active database partition is a partition where the database is available for connection and use by applications.
If the null value is specified, -1 is set implicitly.

If both parameters are set to NULL, the snapshot is taken only if a file has not previously been created by the SNAPSHOT_FILEW stored procedure for the corresponding snapshot API request type.

Authorization

One of the following authorities is required to execute the function:

EXECUTE privilege on the function
DATAACCESS authority

To access snapshot monitor data, one of the following authorities is required:

SYSMON authority
SYSCTRL authority
SYSMAINT authority
SYSADM authority

The function returns a table as shown in the following section.

Table 1. Information returned by the SNAPSHOT_LOCKWAIT table function
Column name	Data type	Description or corresponding monitor element
SNAPSHOT_TIMESTAMP	TIMESTAMP	snapshot_timestamp - Snapshot timestamp
AGENT_ID	BIGINT	agent_id - Application handle (agent ID)
SUBSECTION_NUMBER	BIGINT	ss_number - Subsection number
LOCK_MODE	BIGINT	lock_mode - Lock mode
LOCK_OBJECT_TYPE	BIGINT	lock_object_type - Lock object type waited on
AGENT_ID_HOLDING_LK	BIGINT	agent_id_holding_lock - Agent ID holding lock
LOCK_WAIT_START_TIME	TIMESTAMP	lock_wait_start_time - Lock wait start timestamp
LOCK_MODE_REQUESTED	BIGINT	lock_mode_requested - Lock mode requested
PARTITION_NUMBER	SMALLINT	node_number - Node number
LOCK_ESCALATION	SMALLINT	lock_escalation - Lock escalation
TABLE_NAME	VARCHAR(128)	table_name - Table name
TABLE_SCHEMA	VARCHAR(128)	table_schema - Table schema name
TABLESPACE_NAME	VARCHAR(128)	tablespace_name - Table space name
APPL_ID_HOLDING_LK	VARCHAR(128)	appl_id_holding_lk - Application ID holding lock