DB2 Version 9.7 for Linux, UNIX, and Windows

Event monitors

Event monitors return information for the event types specified in the CREATE EVENT MONITOR statement. For each event type, monitoring information is collected at a certain point in time.

The following table lists available event types, when the monitoring data is collected, and the information available for each event type. The available event types in the first column correspond to the keywords used in the CREATE EVENT MONITOR statement, where the event type is defined.

In addition to the defined events where data 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.

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.

Table 1. Event Types
Event type	When data is collected	Available information
DEADLOCKS¹	Detection of a deadlock	Applications involved, and locks in contention.
DEADLOCKS WITH DETAILS¹	Detection of a deadlock	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.
DEADLOCKS WITH DETAILS HISTORY¹	Detection of a deadlock	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.
DEADLOCKS WITH DETAILS HISTORY VALUES¹	Detection of a deadlock	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.
STATEMENTS	End of SQL statement	Statement start or stop time, CPU used, text of dynamic SQL, SQLCA (return code of SQL statement), and other metrics such as fetch count. Note: Statement start or stop time is unavailable when the Timestamp switch is off.
STATEMENTS	End of subsection	For partitioned databases: CPU consumed, execution time, table and table queue information.
TRANSACTIONS²	End of unit of work	UOW work start or stop time, previous UOW time, CPU consumed, locking and logging metrics. Transaction records are not generated if running with XA.
CONNECTIONS	End of connection	All application level counters.
DATABASE	Database deactivation	All database level counters.
BUFFERPOOLS	Database deactivation	Counters for buffer pool, prefetchers, page cleaners and direct I/O for each buffer pool.
TABLESPACES	Database deactivation	Counters for buffer pool, prefetchers, page cleaners and direct I/O for each table space.
TABLES	Database deactivation	Rows read or written for each table.
Activities	Completion of an activity that executed in a service class, workload or work class that had its COLLECT ACTIVITY DATA option turned on. Data is also collected for the targeted activity at the instant the WLM_CAPTURE_ACTIVITY_IN_PROGRESS stored procedure is executed. Data is also collected if the activity violates a threshold that has the COLLECT ACTIVITY DATA option enabled.	Activity level data. If WITH DETAILS was specified as part of COLLECT ACTIVITY DATA, this will include statement and compilation environment information for those activities that have it. If AND VALUES was also specified, this will also include input data values for those activities that have it.
Statistics	Every `period` minutes, where `period` is the length of time over which statistics are gathered. This period is defined in the WLM_COLLECT_INT database configuration parameter. Data is also collected when the WLM_COLLECT_STATS stored procedure is called.	Statistics computed from the activities that executed within each service class, workload, or work class that exists on the system.
Threshold violations	Upon detection of a threshold violation.	Threshold violation information.
Locking	Upon detection of any of the following event types, depending on configuration settings : lock timeout, deadlock, lock wait beyond a specified duration.	Lock event records.
Unit of work	Upon completion of a unit of work	Unit of work event records. Option to include request metrics in the record.

¹: This option 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 option 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 overhead this event monitor incurs 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.