The output of an event monitor is a binary stream of logical data groupings that are exactly the same for both pipe and file event monitors. You can format this trace using the db2evmon productivity tool.
The following table illustrates the different groupings which may appear in the event monitor output. See Information Available from Event Monitors for a list of events that trigger the writing of event records and Reading an Event Monitor Trace for more information on output. Records in a trace can be divided into four types:
| Event type | Logical data group | Information returned |
|---|---|---|
| Monitor | ||
| Monitor Level |
event_log_stream_header (SQLM_EVENT_LOG_STREAM_HEADER) | Identifies the version level and byte order of the event monitor. Applications can use this header to determine whether they can handle the evmon output stream. |
| Prolog | ||
| Log Header |
log_header_event (SQLM_ELM_EVENT_LOG_HEADER) | Characteristics of the trace, for example server type and memory layout. |
| Database Header |
db_header_event (SQLM_ELM_EVENT_DB_HEADER) | Database name, path and activation time. |
| Event Monitor Start |
start_event (SQLM_ELM_EVENT_START) | Time when the monitor was started or restarted. |
| Connection Header |
connheader_event (SQLM_ELM_EVENT_CONNHEADER) | One for each current connection, includes connection time and application name. |
| Actual Contents | ||
| Statement Event |
stmt_event (SQLM_ELM_EVENT_STMT) | Statement level data, including text for dynamic statements. |
| Subsection Event |
subsection_event (SQLM_ELM_EVENT_SUBSECTION) | Subsection level data. |
| Transaction Event |
xaction_event (SQLM_ELM_EVENT_XACT) | Transaction level data. |
| Connection Event |
conn_event (SQLM_ELM_EVENT_CONN) | Connection level data. |
| Deadlock Event |
deadlock_event (SQLM_ELM_EVENT_DEADLOCK) | Deadlock level data. |
| Deadlocked Connection Event |
dlconn_event (SQLM_ELM_EVENT_DLCONN) | One for each connection involved in the deadlock, includes applications involved and locks in contention. |
| Overflow |
overflow_event (SQLM_ELM_EVENT_OVERFLOW) | Number of records lost - generated when writer cannot keep up with a (non-blocked) event monitor. |
| Epilog | ||
| Database Event |
db_event (SQLM_ELM_EVENT_DB) | Database level data. |
| Buffer Pool Event |
bufferpool_event (SQLM_ELM_EVENT_BUFFERPOOL) | Buffer pool level data. |
| Table Space Event |
tablespace_event (SQLM_ELM_EVENT_TABLESPACE) | Table space level data. |
| Table Event |
table_event (SQLM_ELM_EVENT_TABLE) | Table level data. |
Event records may be generated for any connection and may therefore appear in mixed order in the stream. This means that you may get a transaction event for Connection 1, immediately followed by a connection event for Connection 2. However, records belonging to a single connection or a single event, will appear in their logical order. For example, a statement record (end of statement) always precedes a transaction record (end of UOW), if any. Similarly, a deadlock event record always precedes the deadlocked connection event records for each connection involved in the deadlock. The application id or application handle (agent_id) can be used to match records with a connection.
For example, using the following event monitor,
|
the following workload,
 |
the following trace might be generated. Listed in this sample are some of the fields in each event record to give a flavor of the type of information contained in a trace. See Event Monitors for an example of deadlock events. Note, the numbers in this sample are used to illustrate the order in which records have been written.
MONITOR
The Monitor information is generated for all event monitors. Only event monitors that return a version of SQLM_DBMON_VERSION6 use the self-describing data stream.
Pre-Version 6 output must be read using the Version 5 method. For information on these static sized structures refer to the sqlmon.h file.
PROLOG
The Prolog information is generated when set event monitor all state 1 is executed. If this event monitor had been AUTOSTART, it would have been generated when the database was activated.
1) event_log_stream_header
byte_order: SQLM_BIG_ENDIAN - a UNIX or AIX box
size: 400 - not used, for compatibility only
version: SQLM_DBMON_VERSION6 - trace was produced by UDB V6
2) log_header_event
version: SQLM_DBMON_VERSION6 - Trace was produced by UDB V6
num_nodes_in_db2_instance: 1 - for a standalone system,
byte_order: SQLM_BIG_ENDIAN - on a UNIX or AIX box,
event_monitor_name: ALL - by event monitor: 'ALL'
3) dbheader_event
db_name: SAMPLE - for database 'SAMPLE'
4) connheader_event
agent_id: 14 - Application 1 - handle
appl_id: *LOCAL.bourbon.970602180712 - Application 1 - id with timestamp
CONTENTS
Generated when Application 1 issues select name from syscat.eventmonitors. At the time that the event monitor is turned on, Application 2 has not yet connected.
5) stmt_event
agent_id: 14
appl_id: *LOCAL.bourbon.970602180712
operation: SQLM_PREPARE
package_name: SQLC2BA4
cursor: SQLCUR201
@stmt_text_offset: SELECT EVMONNAME FROM SYSCAT.EVENTMONITORS
6) stmt_event
agent_id: 14
appl_id: *LOCAL.bourbon.970602180712
operation: SQLM_OPEN
package_name: SQLC2BA4
cursor: SQLCUR201
@stmt_text_offset: SELECT EVMONNAME FROM SYSCAT.EVENTMONITORS
7) stmt_event
agent_id: 14
appl_id: *LOCAL.bourbon.970602180712
operation: SQLM_FETCH
package_name: SQLC2BA4
cursor: SQLCUR201
@stmt_text_offset: SELECT EVMONNAME FROM SYSCAT.EVENTMONITORS
fetch_count: 2
sqlca.sqlcode: 100 - (all rows in the SYSCAT.EVENTMONITORS table)
SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a
query is an empty table. SQLSTATE=02000
NOTE - A fetch event is generated only if the fetch fails or encounters end of table
8) stmt_event
agent_id: 14
appl_id: *LOCAL.bourbon.970602180712
operation: SQLM_DESCRIBE
package_name: SQLC2BA4
cursor: SQLCUR201
@stmt_text_offset: SELECT EVMONNAME FROM SYSCAT.EVENTMONITORS
9) stmt_event
agent_id: 14
appl_id: *LOCAL.bourbon.970602180712
operation: SQLM_CLOSE
package_name: SQLC2BA4
cursor: SQLCUR201
@stmt_text_offset: SELECT EVMONNAME FROM SYSCAT.EVENTMONITORS
fetch_count: 2
10) stmt_event
agent_id: 14
appl_id: *LOCAL.bourbon.970602180712
operation: SQLM_STATIC_COMMIT - generated by CLP after the SELECT
package_name: SQLC2BA4
11) xaction_event
agent_id: 14
appl_id: *LOCAL.bourbon.970602180712
status: SQLM_UOWCOMMIT
rows_read: 7
Application 2 is connecting to the database. Output is interleaved, as the DB2 agents are executing simultaneously:
12) connheader_event
agent_id: 15 - Application 2 - handle
appl_id: *LOCAL.bourbon.970602180714 - Application 2 - id with timestamp
13) stmt_event
agent_id: 15
appl_id: *LOCAL.bourbon.970602180714
operation: SQLM_STATIC_COMMIT - generated by CLP on CONNECT
14) xaction_event
agent_id: 15
appl_id: *LOCAL.bourbon.970602180714
status: SQLM_UOWCOMMIT
15) stmt_event
agent_id: 15
appl_id: *LOCAL.bourbon.970602180714
operation: SQLM_STATIC_COMMIT - generated on CONNECT RESET
16) xaction_event
agent_id: 15
appl_id: *LOCAL.bourbon.970602180714
status: SQLM_UOWCOMMIT
17) conn_event
agent_id: 15
appl_id: *LOCAL.bourbon.970602180714
commit_sql_stmts: 2
18) stmt_event
agent_id: 14
appl_id: *LOCAL.bourbon.970602180712
operation: SQLM_STATIC_COMMIT - generated on CONNECT RESET
package_name: SQLC2BA4
19) xaction_event
agent_id: 14
appl_id: *LOCAL.bourbon.970602180712
status: SQLM_UOWCOMMIT
rows_read: 2
locks_held_top: 7
20) conn_event
agent_id: 14
appl_id: *LOCAL.bourbon.970602180712
select_sql_stmts: 1
rows_selected: 2
EPILOG
The Epilog information is generated during database deactivation (last application finished disconnecting):
21) table_event
table_schema: SYSIBM
table_name: SYSTABLES
table_type: SQLM_CATALOG_TABLE
rows_read: 2
22) table_event
table_schema: SYSIBM
table_name: SYSDBAUTH
table_type: SQLM_CATALOG_TABLE
rows_read: 3
23) tablespace_event
tablespace_name: SYSCATSPACE
24) tablespace_event
tablespace_name: TEMPSPACE1
25) tablespace_event
tablespace_name: USERSPACE1
26) bufferpool_event
bp_name: IBMDEFAULTBP
27) db_event
connections_top: 2
| Note: | A WHERE clause on the CREATE EVENT MONITOR SQL statement can be used to restrict the applications that will generate events; see Appendix A. Database System Monitor Interfaces for details. |