The CREATE EVENT MONITOR statement defines a monitor that will record certain events that occur when using the database. The definition of each event monitor also specifies where the database should record the events.
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).
>>-CREATE EVENT MONITOR--event-monitor-name--FOR----------------> .-,------------------------------------------------------------. V | >----+-+-DATABASE---------------------------------------------+-+-+--> | +-TABLES-----------------------------------------------+ | | +-DEADLOCKS--+---------------------------------------+-+ | | | '-WITH DETAILS--+---------------------+-' | | | | '-HISTORY--+--------+-' | | | | '-VALUES-' | | | +-TABLESPACES------------------------------------------+ | | '-BUFFERPOOLS------------------------------------------' | '-+-CONNECTIONS--+--+----------------------------+---------' +-STATEMENTS---+ '-WHERE--| event-condition |-' '-TRANSACTIONS-' >--WRITE TO--+-TABLE--| evm-group-info |---------+--●-----------> +-PIPE--pipe-name-------------------+ '-FILE--path-name--| file-options |-' .-MANUALSTART-. >--+-------------+--●-------------------------------------------> '-AUTOSTART---' >--+----------------------------------------+--●----------------> '-ON DBPARTITIONNUM--db-partition-number-' .-LOCAL--. >--+--------+--●----------------------------------------------->< '-GLOBAL-' event-condition .-AND | OR-----------------------------------------------------. V | |----+-----+--+-+-APPL_ID---+--+-=---------+--comparison-string-+-+--| '-NOT-' | +-AUTH_ID---+ | (1) | | | '-APPL_NAME-' +-<>--------+ | | +->---------+ | | | (1) | | | +->=--------+ | | +-<---------+ | | | (1) | | | +-<=--------+ | | +-LIKE------+ | | '-NOT--LIKE-' | '-(--event-condition--)---------------------------' evm-group-info |--●--+---------------------------------------------------+-----> | .-,---------------------------------------------. | | V | | '---evm-group--+--------------------------------+-+-' '-(--| target-table-options |--)-' .-BLOCKED-. >--+---------+--------------------------------------------------| target-table-options .-------------------------------------------------. V (2) (3) | |----------------+-TABLE--table-name---------------+-+----------| +-IN--tablespace-name-------------+ | .-PCTDEACTIVATE--100-----. | +-+-PCTDEACTIVATE--integer-+------+ +-TRUNC---------------------------+ | .-,-------. | | V | | '-+-INCLUDES-+--(----element-+--)-' '-EXCLUDES-' file-options .-MAXFILES--NONE------------. |--●--+---------------------------+--●--------------------------> '-MAXFILES--number-of-files-' .-BUFFERSIZE--4-----. >--+------------------------+--●--+-------------------+--●------> '-MAXFILESIZE--+-pages-+-' '-BUFFERSIZE--pages-' '-NONE--' .-BLOCKED----. .-APPEND--. >--+------------+--●--+---------+--●----------------------------| '-NONBLOCKED-' '-REPLACE-'
Only one of: DEADLOCKS, DEADLOCKS WITH DETAILS, DEADLOCKS WITH DETAILS HISTORY, or DEADLOCKS WITH DETAILS HISTORY VALUES can be specified in a single CREATE EVENT MONITOR statement (SQLSTATE 42613).
This clause is a special form of the WHERE clause that should not be confused with a standard search condition.
The WHERE clause is not evaluated for each event.
If no WHERE clause is specified, all events of the specified event type will be monitored.
The event-condition must not exceed 32 678 bytes in length in the database code page (SQLSTATE 22001).
The application program name is the first 20 bytes of the application program file name, after the last path separator.
For more information about logical data groups, refer to ../../com.ibm.db2.luw.admin.mon.doc/doc/r0059240.html .
Type of Event Monitor | evm-group Value |
---|---|
Database |
|
Tables |
|
Deadlocks |
|
Deadlocks with details |
|
Deadlocks with details history |
|
Deadlocks with details history values |
|
Table spaces |
|
Bufferpools |
|
Connections |
|
Statements |
|
Transactions |
|
Activities |
|
Statistics |
|
Threshold Violations |
|
Locking5 |
|
Package Cache5 |
|
Unit of Work5 |
|
Change History |
|
1 Logical data groups dbheader (conn_time element only), start and overflow, are all written to the CONTROL group. The overflow group is written if the event monitor is non-blocked and events were discarded. 2 Corresponds to the DETAILED_DLCONN event. 3 Corresponds to the LOCK logical data groups that occur within each DETAILED_DLCONN event. 4 Created only for partitioned database environments. 5 Refers to the Formatted Event Table version of this event monitor type. |
substring(evm-group CONCAT '_'
CONCAT event-monitor-name,1,128)
Use the db2evtbl command to build a CREATE EVENT MONITOR statement that includes a complete list of elements for a group.
Operating system | Naming rules |
---|---|
AIX HP-UX Solaris |
Pipe names are treated like file names. As a result, relative pipe names are permitted, and are treated like relative path-names (see description for path-name). |
Linux | Pipe names are treated like file names. As a result, relative pipe names are permitted, and are treated like relative path-names (see description for path-name). |
Windows | There is a special syntax for a pipe name and, as a result, absolute pipe names are required. |
The existence of the pipe will not be checked at event monitor creation time. It is the responsibility of the monitoring application to have created and opened the pipe for reading at the time that the event monitor is activated. If the pipe is not available at this time, then the event monitor will turn itself off, and will log an error. (That is, if the event monitor was activated at database start time as a result of the AUTOSTART option, then the event monitor will log an error in the system error log.) If the event monitor is activated via the SET EVENT MONITOR STATE SQL statement, then that statement will fail (SQLSTATE 58030).
In a DB2® pureScale® environment, the pipe-name must be on a shared file system whether this is a LOCAL or GLOBAL event monitor. This requirement is to allow these event monitors to operate correctly in the event of a member failover. Failure to use a pipe-name on a shared file system will result in an error (SQLSTATE 428A3) if the event monitor activates during a member failover.
The maximum size of each file can be defined as well as the maximum number of files. An event monitor will never split a single event record across two files. However, an event monitor may write related records in two different files. It is the responsibility of the application that uses this data to keep track of such related information when processing the event files.
The directory does not have to exist at CREATE EVENT MONITOR time. However, a check is made for the existence of the target path when the event monitor is activated. At that time, if the target path does not exist, an error (SQLSTATE 428A3) is raised.
If an absolute path is specified, the specified path will be the one used.
In environments other than DB2 pureScale, if a relative path (a path that does not start with the root) is specified, then the path relative to the DB2EVENT directory in the database directory will be used. In a DB2 pureScale environment, if a relative path is specified, then the path relative to the database owning directory in the database directory will be used.
It is possible to specify two or more event monitors that have the same target path. However, once one of the event monitors has been activated for the first time, and as long as the target directory is not empty, it will be impossible to activate any of the other event monitors.
In a DB2 pureScale environment, the path-name must be on a shared file system whether this is a LOCAL or GLOBAL event monitor. This requirement is to allow these event monitors to operate correctly in the event of a member failover. Failure to use a path-name on a shared file system will result in an error (SQLSTATE 428A3) if the event monitor activates during a member failover.
If an application removes the event files from the directory after they have been written, then the total number of files that an event monitor can produce can exceed number-of-files. This option has been provided to allow a user to guarantee that the event data will not consume more than a specified amount of disk space.
The number of pages must be greater than at least the size of the event buffer in pages. If this requirement is not met, then an error (SQLSTATE 428A4) is raised.
The default size of each buffer is 4 pages (two 16K buffers are allocated). The minimum size is 1 page. The maximum size of the buffers is limited by the value of the MAXFILESIZE parameter, as well as the size of the monitor heap, because the buffers are allocated from that heap. If many event monitors are being used at the same time, increase the size of the mon_heap_sz database manager configuration parameter.
Event monitors that write their data to a pipe also have two internal (non-configurable) buffers that are each 1 page in size. These buffers are also allocated from the monitor heap (MON_HEAP). For each active event monitor that has a pipe target, increase the size of the database heap by 2 pages.
The APPEND option does not apply at CREATE EVENT MONITOR time, if there is existing event data in the directory where the newly created event monitor is to write its event data.
When DB2 pureScale is enabled, -1 can be specified, which allows the I/O component to run from any active member. Additionally, in the event that the I/O component is no longer able to run on a given member, the event monitor will be restarted with the I/O component running on another available active member.
This clause is not valid for table event monitors. In a partitioned database environment, write-to-table event monitors will run and write events on all database partitions where table spaces for target tables are defined.
In a DB2 pureScale environment, write-to-table event monitors will record events on all active members.
If this clause is not specified and DB2 pureScale is not enabled, the currently connected database partition number (for the application) is used.
If this clause is not specified and DB2 pureScale is enabled, the I/O component is able to run on any currently connected database partition number.
This clause is valid for file or pipe monitors. It is not valid for table event monitors.
This clause is valid for file or pipe monitors. It is not valid for table event monitors.
Event monitors WITH DETAILS HISTORY VALUES (and, to a lesser extent, WITH DETAILS HISTORY) use a significant amount of monitor heap space to track statements and their data values, as described in the mon_heap_sz configuration parameter.
In a DB2 pureScale environment, data will be written from every member.
In a partitioned database environment, if some target tables do not reside on a database partition, but other target tables do reside on that same database partition, only the data for the target tables that do reside on that database partition is recorded.
SQLM_TYPE_STRING CHAR[n], VARCHAR[n] or CLOB(n)
(If the data in the event monitor
record exceeds n bytes,
it is truncated.)
SQLM_TYPE_U8BIT and SQLM_TYPE_8BIT SMALLINT, INTEGER or BIGINT
SQLM_TYPE_16BIT and SQLM_TYPE_U16BIT SMALLINT, INTEGER or BIGINT
SQLM_TYPE_32BIT and SQLM_TYPE_U32BIT INTEGER or BIGINT
SQLM_TYPE_U64BIT and SQLM_TYPE_64BIT BIGINT
sqlm_timestamp TIMESTAMP
sqlm_time(elapsed time) BIGINT
sqlca:
sqlerrmc VARCHAR[72]
sqlstate CHAR[5]
sqlwarn CHAR[11]
other fields INTEGER or BIGINT
Column Name | Data Type | Nullable | Description |
---|---|---|---|
PARTITION_KEY | INTEGER | N | Distribution key (partitioned database only) |
PARTITION_NUMBER | INTEGER | N | Database partition number (partitioned database only) |
EVMONNAME | VARCHAR(128) | N | Name of the event monitor |
MESSAGE | VARCHAR(128) | N | Describes the nature of the MESSAGE_TIME column.
For more details see, message - Control Table Message monitor element |
MESSAGE_TIME | TIMESTAMP | N | Timestamp |
CREATE EVENT MONITOR SMITHPAY
FOR DATABASE, STATEMENTS
WHERE APPL_NAME = 'PAYROLL' AND AUTH_ID = 'JSMITH'
WRITE TO FILE '/home/jsmith/event/smithpay'
MAXFILES 25
MAXFILESIZE 1024
NONBLOCKED
APPEND
CREATE EVENT MONITOR DEADLOCK_EVTS
FOR DEADLOCKS
WRITE TO FILE 'DLOCKS'
MAXFILES 1
MAXFILESIZE NONE
AUTOSTART
CREATE EVENT MONITOR DB_APPLS
FOR CONNECTIONS
WRITE TO PIPE '/home/jsmith/applpipe'
CREATE EVENT MONITOR FOO
FOR STATEMENTS
WRITE TO TABLE
CREATE EVENT MONITOR BAR
FOR STATEMENTS, TRANSACTIONS
WRITE TO TABLE
STMT(TABLE MYDEPT.MYSTMTINFO IN MYTABLESPACE
INCLUDES(ROWS_READ, ROWS_WRITTEN, STMT_TEXT)),
STMT(TABLE MYDEPT.MYSTMTINFO IN MYTABLESPACE
EXCLUDES(START_TIME, STOP_TIME, PARTIAL_RECORD)),
XACT