DB2 Version 9.7 for Linux, UNIX, and Windows

Information written to relational tables for a locking event monitor

Information written for a locking event monitor from the EVMON_FORMAT_UE_TO_TABLES table function. This is also documented in the sqllib/misc/DB2EvmonLocking.xsd file.

Table 1. Information returned for a locking event monitor: Table name: LOCK_EVENT
Column Name	Data Type	Description
XMLID	VARCHAR(1000) NOT NULL
EVENT_ID	BIGINT NOT NULL
EVENT_TYPE	VARCHAR(128) NOT NULL
EVENT_TIMESTAMP	TIMESTAMP NOT NULL
MEMBER	SMALLINT NOT NULL	member - Database member monitor element
DL_CONNS	INTEGER	dl_conns - Connections involved in deadlock monitor element
ROLLED_BACK_PARTICIPANT_NO	INTEGER	rolled_back_participant_no - Rolled back application participant monitor element

Table 2. Information returned for a locking event monitor: Table name: LOCK_PARTICIPANTS
Column Name	Data Type	Description
XMLID	VARCHAR(1000) NOT NULL
PARTICIPANT_NO	INTEGER	participant_no - Participant within Deadlock monitor element
PARTICIPANT_TYPE	VARCHAR(10)
PARTICIPANT_NO_HOLDING_LK	INTEGER	participant_no_holding_lk - Participant Holding a Lock on the Object Required by Application monitor element
APPLICATION_HANDLE	BIGINT	application_handle - Application handle monitor element
APPL_ID	VARCHAR(128)	appl_id - Application ID monitor element
APPL_NAME	VARCHAR(128)	appl_name - Application name monitor element
AUTH_ID	VARCHAR(128)	auth_id - Authorization ID monitor element
AGENT_TID	BIGINT
COORD_AGENT_TID	BIGINT
AGENT_STATUS	INTEGER	appl_status - Application Status monitor element
LOCK_TIMEOUT_VAL	BIGINT	lock_timeout_val - Lock timeout value monitor element
LOCK_WAIT_VAL	BIGINT	The amount of time spent in lock wait (in milliseconds) before an event for mon_lockwait is generated.
WORKLOAD_ID	INTEGER	workload_id - Workload ID monitor element
WORKLOAD_NAME	VARCHAR(128)	workload_name - Workload name monitor element
SERVICE_CLASS_ID	INTEGER	service_class_id - Service class ID monitor element
SERVICE_SUBCLASS_NAME	VARCHAR(128)	service_subclass_name - Service subclass name monitor element
CURRENT_REQUEST	VARCHAR(32)
LOCK_ESCALATION	CHAR(3)	lock_escalation - Lock escalation monitor element
PAST_ACTIVITIES_WRAPPED	CHAR(3)
CLIENT_USERID	VARCHAR(255)	client_userid - Client user ID monitor element
CLIENT_WRKSTNNAME	VARCHAR(255)	client_wrkstnname - Client workstation name monitor element
CLIENT_APPLNAME	VARCHAR(255)	client_applname - Client application name monitor element
CLIENT_ACCTNG	VARCHAR(255)	client_acctng - Client accounting string monitor element
OBJECT_REQUESTED	VARCHAR(10)
LOCK_NAME	CHAR(32)	lock_name - Lock name monitor element
LOCK_OBJECT_TYPE	VARCHAR(32)	lock_object_type - Lock object type waited on monitor element
LOCK_OBJECT_TYPE_ID	CHAR(1) FOR BIT DATA
LOCK_ATTRIBUTES	CHAR(8)	lock_attributes - Lock attributes monitor element
LOCK_CURRENT_MODE	BIGINT	lock_current_mode - Original lock mode before conversion monitor element
LOCK_MODE_REQUESTED	BIGINT	lock_mode_requested - Lock mode requested monitor element
LOCK_MODE	BIGINT	lock_mode - Lock mode monitor element
LOCK_COUNT	BIGINT	lock_count - Lock count monitor element
LOCK_HOLD_COUNT	BIGINT	lock_hold_count - Lock hold count monitor element
LOCK_RRIID	BIGINT
LOCK_STATUS	BIGINT	lock_status - Lock status monitor element
LOCK_RELEASE_FLAGS	CHAR(8)	lock_release_flags - Lock release flags monitor element
LOCK_WAIT_START_TIME	TIMESTAMP	lock_wait_start_time - Lock wait start timestamp monitor element
LOCK_WAIT_END_TIME	TIMESTAMP
TABLE_FILE_ID	BIGINT	table_file_id - Table file ID monitor element
TABLE_NAME	VARCHAR(128)	table_name - Table name monitor element
TABLE_SCHEMA	VARCHAR(128)	table_schema - Table schema name monitor element
TABLESPACE_NAME	VARCHAR(128)	tablespace_name - Table space name monitor element
THRESHOLD_ID	INTEGER
THRESHOLD_NAME	VARCHAR(128)	threshold_name - Threshold name monitor element

Table 3. Information returned for a locking event monitor: Table name: LOCK_PARTICIPANT_ACTIVITIES
Column Name	Data Type	Description
XMLID	VARCHAR(1000) NOT NULL
PARTICIPANT_NO	INTEGER	participant_no - Participant within Deadlock monitor element
ACTIVITY_ID	INTEGER	activity_id - Activity ID monitor element
ACTIVITY_TYPE	VARCHAR(10)	activity_type - Activity type monitor element
UOW_ID	INTEGER	uow_id - Unit of work ID monitor element
PACKAGE_NAME	VARCHAR(128)	package_name - Package name monitor element
PACKAGE_SCHEMA	VARCHAR(128)	package_schema - Package schema monitor element
PACKAGE_VERSION_ID	VARCHAR(64)	package_version_id - Package version monitor element
CONSISTENCY_TOKEN	VARCHAR(8)	consistency_token - Package consistency token monitor element
SECTION_NUMBER	BIGINT	section_number - Section number monitor element
REOPT	VARCHAR(10)
INCREMENTAL_BIND	CHAR(3)
EFFECTIVE_ISOLATION	CHAR(2)	effective_isolation - Effective isolation monitor element
EFFECTIVE_QUERY_DEGREE	BIGINT	effective_query_degree - Effective query degree monitor element
STMT_LOCK_TIMEOUT	INTEGER	stmt_lock_timeout - Statement lock timeout monitor element
STMT_TYPE	BIGINT	stmt_type - Statement type monitor element
STMT_QUERY_ID	BIGINT	stmt_query_id - Statement query identifier monitor element
STMT_NEST_LEVEL	BIGINT	stmt_nest_level - Statement nesting level monitor element
STMT_INVOCATION_ID	BIGINT	stmt_invocation_id - Statement invocation identifier monitor element
STMT_SOURCE_ID	BIGINT	stmt_source_id - Statement source identifier monitor element
STMT_PKGCACHE_ID	BIGINT	stmt_pkgcache_id - Statement package cache identifier monitor element
STMT_FIRST_USE_TIME	TIMESTAMP	stmt_first_use_time - Statement first use timestamp monitor element
STMT_LAST_USE_TIME	TIMESTAMP	stmt_last_use_time - Statement last use timestamp monitor element
STMT_TEXT	CLOB(2097152)	stmt_text - SQL statement text monitor element

Table 4. Information returned for a locking event monitor: Table name: LOCK_ACTIVITY_VALUES
Column Name	Data Type	Description
XMLID	VARCHAR(1000) NOT NULL
PARTICIPANT_NO	INTEGER	participant_no - Participant within Deadlock monitor element
ACTIVITY_ID	INTEGER	activity_id - Activity ID monitor element
UOW_ID	INTEGER	uow_id - Unit of work ID monitor element
STMT_VALUE_INDEX	INTEGER	stmt_value_index - Value index monitor element
STMT_VALUE_ISREOPT	INTEGER	stmt_value_isreopt - Variable used for statement reoptimization monitor element
STMT_VALUE_ISNULL	INTEGER	stmt_value_isnull - Value has null value monitor element
STMT_VALUE_TYPE	CHAR(16)	stmt_value_type - Value type monitor element
STMT_VALUE_DATA	CLOB(32K)	stmt_value_data - Value data monitor element