Event monitor life cycle

The steps listed below represent a typical life cycle of an event monitor.

1. Create an event monitor.
2. Activate the newly created event monitor to enable it to collect information.
3. Read event monitor output.
4. If you don’t want to collect event information for some time, you can deactivate the event monitor.
5. To eliminate an event monitor object, use the DROP EVENT MONITOR

Note: By default, all databases have an event monitor named DB2DETAILDEADLOCK defined, which keeps track of DEADLOCKS WITH DETAILS. The DB2DETAILDEADLOCK
event monitor starts automatically when the database starts.

CREATE EVENT MONITOR
CREATE EVENT MONITOR event_monitor_name FOR
{DATABASE |
TABLES |
DEADLOCKS [WITH DETAILS] |
TABLE SPACES |
BUFFERPOOLS |
CONNECTIONS [WHERE event_condition] |
STATEMENTS [WHERE event_condition] |
TRANSACTIONS [WHERE event_condition] }
WRITE TO FILE file_path [{APPEND | REPLACE}]
[{MANUAL START | AUTOSTART}]

CREATE EVENT MONITOR parameters

• DEADLOCKS WITH DETAILS specifies that the event monitor is to generate more detailed information for each application that is involved in a deadlock. This additional detail includes:
  - Information about the statement that the application was executing when the 
    deadlock occurred, such as the statement text
  - The locks held by the application when the deadlock occurred.
  

• WHERE event_condition defines a filter that determines which connections cause a CONNECTION, STATEMENT or TRANSACTION event to occur. The following comparison options are available: APPL_ID (application ID), AUTH_ID (authorization ID), and APPL_NAME (name of application). Multiple conditions can be combined with AND / OR. For example: WHERE APPL_NAME = 'PAYROLL' AND AUTH_ID = 'JSMITH'.
  

• WRITE TO FILE file_path Indicates that the target for the event monitor data is a file. The event monitor writes out the stream of data as a series of 8 character numbered files, with the extension "evt". Path-name specifies the name of the directory in which the event monitor should write the event files data.
  

• APPEND specifies that if event data files already exist when the event monitor is turned on, then the event monitor will append the new event data to the existing stream of data files. APPEND is the default option.
  

• REPLACE specifies that if event data files already exist when the event monitor is turned on, then the event monitor will erase all of the event files and start writing data to file 00000000.evt.
  

• MANUALSTART specifies that the event monitor not be started automatically each time the database is started. Event monitors with the MANUALSTART option must be activated manually using the SET EVENT MONITOR STATE statement. This is the default option.
  

• AUTOSTART specifies that the event monitor be started automatically each time the database is started.
  

Creating Event Monitor Examples

• Creates an event monitor called SMITHPAY. This event monitor, will collect event data for the database as well as for the SQL statements performed by the PAYROLL application owned by the JSMITH authorization ID. The data will be appended to the absolute path /home/jsmith/event/smithpay/.
  

CREATE EVENT MONITOR SMITHPAY
FOR DATABASE, STATEMENTS WHERE APPL_NAME = 'PAYROLL' AND AUTH_ID = 'JSMITH'
WRITE TO FILE '/home/jsmith/event/smithpay' APPEND

• Creates an event monitor called DEADLOCKS_EVTS. This event monitor will collect deadlock events and will write them to the relative path DLOCKS. The file will be replaced. The event monitor will be started each time the database is started.
  

CREATE EVENT MONITOR DEADLOCK_EVTS
FOR DEADLOCKS
WRITE TO FILE '/home/jsmith/event/dlocks' REPLACE AUTOSTART

• Creates an event monitor called DB_APPLS. This event monitor collects connection events, and writes the data to the path /home/jsmith/appl.
  

CREATE EVENT MONITOR DB_APPLS
FOR CONNECTIONS
WRITE TO FILE '/home/jsmith/appl'

Activating and Deactivating Event Monitors

• When event monitors are created with AUTOSTART they will start collecting data when the database they are associated with is activated. If the MANUAL START option was used instead or if no option was specified, an event monitor will not begin collecting data until it is activated.
  

• Event monitors are activated/deactivated with the SET EVENT MONITOR statement:
  

SET EVENT MONITOR event_monitor_name STATE [0 | 1]

0 indicates that the specified event monitor should be deactivated.

1 indicates that the specified event monitor should be activated.

• Example: Starting the event SMITHPAY

SET EVENT MONITOR SMITHPAY STATE 1

Flushing an event monitor

• Because some events do not happen as frequently as others, it may be desirable to force an event monitor to collect monitor data and write it to its target location before a monitor triggering event takes place.
  

• In such situations, an event monitor can be made to collect information early by executing the FLUSH EVENT MONITOR SQL statement.
  

FLUSH EVENT MONITOR [event_monitor_name]

Example:

FLUSH EVENT MONITOR DB_APPLS

Checking Event Monitors States

• To see if an event monitor is active or inactive, issue the SQL function EVENT_MON_STATE in a query against the table, SYSCAT.EVENTMONITORS:
  

SELECT evmonname, EVENT_MON_STATE(evmonname) FROM syscat.eventmonitors

• A list of all existing event monitors will be listed, along with their status. A returned value of 0 indicates that the specified event monitor is inactive, and 1 indicates that it is active.
  

Example:

SELECT evmonname, EVENT_MON_STATE(evmonname) FROM syscat.eventmonitors EVMONNAME 2

------------------------------- ----

DB2DETAILDEADLOCK 1

Viewing Event Monitor Data

• To format the event monitor files use the db2evmon tool.

db2evmon -db database-alias -evm event-monitor-name

or

db2evmon -path event-monitor-target

1. db database-alias specifies the database whose data is to be displayed. This parameter is case sensitive.
2. evm event-monitor-name the one-part name of the event monitor. An ordinary or delimited SQL identifier. This parameter is case sensitive.
3. path event-monitor-target specifies the directory containing the eventmonitor trace files.

Example:

db2evmon -db sample -evm DB_APPLS > db_appls.out

Dropping an event monitor

When the event monitor is no longer needed it can be dropped:

drop event monitor event-monitor-name

Example:

drop event monitor DB_APPLS