How to create event monitoring in IBM DB2


CREATE EVENT MONITOR dlmon FOR STATEMENTS, DEADLOCKS WITH DETAILS, CONNECTIONS
WRITE TO TABLE CONNHEADER (TABLE CONNHEADER_dlmon,
INCLUDES (AGENT_ID,APPL_ID, APPL_NAME, TERRITORY_CODE )),
DEADLOCK (TABLE DEADLOCK_dlmon),
DLCONN (TABLE mydept.dlconnections,
EXCLUDES ( LOCK_OBJECT_NAME, LOCK_OBJECT_TYPE,
TABLESPACE_NAME )),
STMT (TABLE STMT_dlmon,
INCLUDES (AGENT_ID, APPL_ID,CREATOR, INT_ROWS_DELETED, INT_ROWS_INSERTED,INT_ROWS_UPDATED, ROWS_READ, ROWS_WRITTEN,SQLCODE,
SQLSTATE, SQLWARN, START_TIME, STMT_OPERATION, STMT_TEXT )),
CONN , CONTROL (TABLE CONTROL_dlmon,
INCLUDES (EVENT_MONITOR_NAME,
MESSAGE,
MESSAGE_TIME ))
BUFFERSIZE 8 NONBLOCKED MANUALSTART;

Activate event monitor
SET EVENT MONITOR dlmon STATE=1;

Retrieve data from the event monitor tables

SELECT agent_id, appl_id, territory_code FROM CONNHEADER_dlmon;
SELECT agent_id, appl_id, int_rows_inserted,
system_cpu_time FROM CONN_dlmon;
SELECT * FROM CONTROL_dlmon;
SELECT * FROM STMT_dlmon;


Deactivate event monitor

SET EVENT MONITOR dlmon STATE = 0;

Drop event monitor
DROP EVENT MONITOR dlmon;

Dropping the monitor doesn't remove tables.

DROP TABLE CONNHEADER_dlmon;
DROP TABLE DEADLOCK_dlmon;
DROP TABLE mydept.dlconnections;
DROP TABLE STMT_dlmon;
DROP TABLE CONN_dlmon;
DROP TABLE CONTROL_dlmon;