Connections

: A connection event is recorded whenever an application 

disconnects from the database.

Transactions

: A transaction event is recorded whenever a transaction 

finishes. The event will be written out whenever a commit or rollback occurs. 

The monitor captures all of the individual statement data, and also information 

about the transaction such as its start and stop time.

Statements

: A statement event is recorded when an SQL statement ends. 

The monitor records statement's start and stop time, CPU used, text of 

dynamic SQL, the return code of the SQL statement, and other metrics such 

as fetch count.

Event monitors are created with the 

CREATE EVENT MONITOR

 SQL statement. 

Information about event monitors is stored in the system catalog table, and it can 

be reused later.

Example 9 23 creates a sample event monitor named 

DEADLOCK_EVMON

. The 

query in the example accesses the 

SYSCAT.EVENTMONITORS

 view and displays 

names of event monitors that have been created in the database. 

Example 9 23   Creating sample event monitor

db2 

create event monitor deadlock_evmon for deadlocks with details 

write to table manualstart 

db2 select evmonname from syscat.eventmonitors

The output of the 

DEADLOCK_EVMON 

monitor will be recorded in newly created 

tables. To check in advance what tables are to be created, or to generate syntax 

that overrides the default table names, use the 

db2evtbl

 tool as shown in the 

Example 9 24.

Example 9 24   Generating table syntax for specified event monitor

db2evtbl  evm deadlock_evmon deadlocks with details

CREATE EVENT MONITOR deadlock_evmon

           FOR DEADLOCKS WITH DETAILS

           WRITE TO TABLE

                 CONNHEADER (TABLE CONNHEADER_deadlock_evmon,

                               INCLUDES (AGENT_ID,

                                         APPL_ID,

                                         APPL_NAME,

                                         AUTH_ID,

[...]

 Chapter 9. Testing and tuning 

293