Types of events for which event monitors capture data

You can use event monitors to capture information related to many different kinds of events that take place on your system.

The following tables lists the types of events that occur in the system that you can monitor with an event monitor. It also describes the type of data collected for different events, as well as when the monitoring data is collected. The names of the event monitors shown in column two correspond to the keywords used to create that type of event monitor using the CREATE EVENT MONITOR statement.

Table 1. Event Types
Type of event to monitor	Event monitor name	Event monitor properties	Details
Locks and deadlocks	LOCKING	Uses of this event monitor	To determine when locks or deadlocks occur, and the applications that are involved. The advantages of using the LOCKING event monitor instead of the deprecated DEADLOCKS event monitor include consolidated reporting of both lock and deadlock events, as well as the inclusion of information about lock waits and lock time-outs.
		Data collected	Comprehensive information regarding applications involved, including the identification of participating statements (and statement text) and a list of locks being held.
		When the event data is generated¹	Upon detection of any of the following event types, depending on how you configure the event monitor: lock timeout deadlock lock wait beyond a specified duration
Execution of a SQL statements or other operation that spawns a database activity.	ACTIVITIES	Uses of this event monitor	To track the execution of individual statements and other activities to understand what activities are running in the system. Also to capture activities for diagnostic reasons, and to study the resource consumption of SQL.
		Data collected	Activity level data, generally for activities involving workload management objects. If WITH DETAILS was specified as part of COLLECT ACTIVITY DATA clause on the CREATE or ALTER statements for a workload management object, then information collected includes statement and compilation environment information for those activities that have it. If WITH SECTION is also specified, then statement, compilation environment, section environment data, and section actuals are also captured. If AND VALUES was also specified on the CREATE OR ALTER statement for the workload management object, the information collected will also include input data values for those activities that have it.
		When event data is generated¹	Upon completion of an activity that executed in a service class, workload or work class that had its COLLECT ACTIVITY DATA option turned on. When an activity violates a threshold that has the COLLECT ACTIVITY DATA option enabled. At the instant the WLM_CAPTURE_ACTIVITY_IN_PROGRESS stored procedure is executed. When an activity is executed by a connection for which activity collection has been enabled using the WLM_SET_CONN_ENV stored procedure.
Execution of an SQL statement	STATEMENTS	Uses of this event monitor	To see what requests are being made to the database as a result of the execution of SQL statements.
		Data collected	Statement start or stop time, CPU used, text of dynamic SQL, SQLCA (return code of SQL statement), and other metrics such as fetch count. For partitioned databases: CPU used, execution time, table and table queue information. Notes: When monitoring the execution of SQL procedures using statement event monitors, data manipulation language (DML) statements, such as INSERT, SELECT, DELETE, and UPDATE, generate events. Procedural statements, such as variable assignments and control structures (for example, WHILE or IF), do not generate events in a deterministic fashion. Statement start or stop time is unavailable when the Timestamp switch is off.
		When event data is generated	End of SQL statement²; for partitioned databases, End of subsection²
Completion of a unit of work (transaction)	UNIT OF WORK	Uses of this event monitor	To gather resource usage information and performance metrics for units of work that run on the system. This information can be used for purposes ranging from generating reports for billing or charge-back purposes of system resources used by an application, to troubleshooting performance problems caused by slow-running routines. Recommended over the TRANSACTIONS event monitor.
		Data collected	Information about units of work (transactions), such as start and stop time, the workload and service class under which they ran. Option to include information about packages or executable IDs for statements run as part of the unit of work, as well as request metrics.
		When event data is generated¹	Upon completion of a unit of work
Eviction of sections from the package cache	PACKAGE CACHE	Uses of this event monitor	To capture a history of statements (and related metrics) that are no longer in the package cache. This information can be used if you need to examine performance metrics for statements that are no longer available in memory.
		Data collected	Includes statement text and metrics aggregated over all executions of the section.
		When event data is generated¹	As entries are evicted from the package cache.
Connections to the database by applications	CONNECTIONS	Uses of this event monitor	To capture metrics and other monitor elements for each connection to the database by an application.
		Data collected	All application-level counters. For example, the time that the application connected to or disconnected from the database, or number of lock escalations that the application was involved with.
		When event data is generated	End of connection²
Deactivation of database	DATABASE	Uses of this event monitor	To capture metrics and other monitor elements that reflect information about the database as whole, since activation.
		Data collected	All database level counters. For example, the number of connections made to a database, time spent waiting on locks, or rows of data inserted since its activation.
		When event data is generated	Database deactivation²
	BUFFERPOOLS TABLESPACES	Uses of this event monitor	To capture metrics related to buffer pools and table spaces.
		Data collected	Counters for buffer pools, prefetchers, page cleaners and direct I/O for each buffer pool.
		When event data is generated	Database deactivation²
	TABLES	Uses of this event monitor	To capture metrics related to tables that have changed since database activation.
		Data collected	Table level counters, such as rows read or written, or disk pages used by data,LOB or index objects.
		When event data is generated	Database deactivation²
Statistics and metrics on workload management objects	STATISTICS	Uses of this event monitor	To capture processing metrics related to workload management objects (for example service superclasses, or workloads) in the database. For example, you could use a statistics event monitor to check on CPU utilization over time for a given workload.
		Data collected	Statistics computed from the activities that executed within each service class, workload, or work class that exists on the system.
		When event data is generated	Statistics can be collected automatically at regular intervals. This interval is defined with the wlm_collect_int database configuration parameter. Data can also collected manually, using the WLM_COLLECT_STATS stored procedure. Note: With either collection mechanism, the values of statistics monitor elements are reset to 0 after collection has taken place.
Exceeding a workload manager threshold	THRESHOLD VIOLATIONS	Uses of this event monitor	To determine when specific thresholds that you set are exceeded during database operations. Thresholds can be set for a variety of things, ranging from CPU time to the number of database connections, to the execution of specific statements. Data collected can be used for a variety of purposes, including monitoring for potential problems (such as approaching limits on temporary table space).
		Data collected	Threshold violation information.
		When event data is generated	Upon detection of a threshold violation. Thresholds are defined using the CREATE THRESHOLD statement.
Changes to database or database manager configuration	CHANGE HISTORY	Uses of this event monitor	To captures change to database and database manager configuration and registry settings, execution of DDL statements, and execution of utilities
		Data collected	Database and database manager configuration parameter changes, registry variable changes, execution of DDL statements, execution of certain database utilities and commands, and change history event monitor startup. Note: Generally, information related to events that occur while the change history event monitor is inactive or the database is offline are not captured. However, changes to registry variables and configuration parameters are recorded.
		When event data is generated¹	Upon monitor startup, when a parameter or variable changes, or when a command, DDL, or utility completes.
Notes: If a database is deactivated while an activity event monitor is active, backlogged activity records in the queue are discarded. To ensure that you obtain all activities event monitor records and that none are discarded, deactivate the activities event monitor before deactivating the database. When an activities event monitor is explicitly deactivated, all backlogged activity records in the queue are processed before the event monitor deactivates. In addition to the defined times where data collection automatically occurs, you can use the FLUSH EVENT MONITOR SQL statement to generate events. The events generated by this method are written with the current database monitor values for all the monitor types (except for DEADLOCKS and DEADLOCKS WITH DETAILS) associated with the flushed event monitor.

Table 2. Event Types For Deprecated Event Monitors
Type of event to monitor	Event monitor name	Event monitor properties	Details
Deadlocks	DEADLOCKS²	Uses of this event monitor	To determine when deadlocks occur, and the applications that are involved.
		Data collected	Applications involved, and locks in contention.
		When event data is generated	Detection of a deadlock
	DEADLOCKS WITH DETAILS²	Uses of this event monitor	To determine when deadlocks occur, and the applications that are involved.
		Data collected	Comprehensive information regarding applications involved, including the identification of participating statements (and statement text) and a list of locks being held. Using a DEADLOCKS WITH DETAILS event monitor instead of a DEADLOCKS event monitor will incur a performance cost when deadlocks occur, due to the extra information that is collected.
		When event data is generated	Detection of a deadlock
	DEADLOCKS WITH DETAILS HISTORY²	Uses of this event monitor	To determine when deadlocks occur, and the applications that are involved.
		Data collected	All information reported in a DEADLOCKS WITH DETAILS event monitor, along with the statement history for the current unit of work of each application owning a lock participating in a deadlock scenario for the database partition where that lock is held. Using a DEADLOCKS WITH DETAILS HISTORY event monitor will incur a minor performance cost when activated due to statement history tracking.
		When event data is generated	Detection of a deadlock
	DEADLOCKS WITH DETAILS HISTORY VALUES²	Uses of this event monitor
		Data collected	All information reported in a deadlock with details and history, along with the values provided for any parameter markers at the time of execution of a statement. Using a DEADLOCKS WITH DETAILS HISTORY VALUES event monitor will incur a more significant performance cost when activated due to extra copying of data values.
		When event data is generated	Detection of a deadlock
Completion of a unit of work (transaction)	TRANSACTIONS³	Uses of this event monitor
		Data collected	UOW work start or stop time, previous UOW time, CPU consumed, locking and logging metrics. Transaction records are not generated if running with XA.
		When event data is generated	Upon completion of a unit of work¹
Notes: In addition to the defined times where data collection automatically occurs, you can use the FLUSH EVENT MONITOR SQL statement to generate events. The events generated by this method are written with the current database monitor values for all the monitor types (except for DEADLOCKS and DEADLOCKS WITH DETAILS) associated with the flushed event monitor. This event monitor has been deprecated. Its use is no longer recommended and might be removed in a future release. Use the CREATE EVENT MONITOR FOR LOCKING statement to monitor lock-related events, such as lock timeouts, lock waits, and deadlocks. This event monitor has been deprecated. Its use is no longer recommended and might be removed in a future release. Use the CREATE EVENT MONITOR FOR UNIT OF WORK statement to monitor transaction events.

Note: A detailed deadlock event monitor is created for each newly created database. This event monitor, named DB2DETAILDEADLOCK, starts when the database is activated and will write to files in the database directory. You can avoid the additional processor time this event monitor requires by dropping it. The DB2DETAILDEADLOCK event monitor is deprecated. Its use is no longer recommended and might be removed in a future release. Use the CREATE EVENT MONITOR FOR LOCKING statement to monitor lock-related events, such as lock timeouts, lock waits, and deadlocks.