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.
Scope
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).
Authorization
The privileges held by the authorization ID must include either SYSADM or DBADM authority (SQLSTATE 42502).
Command Syntax
>>-CREATE--EVENT--MONITOR--event-monitor-name--FOR-------------->
.-,----------------------------------------------------------.
V |
>---------+-+-DATABASE----+-------------------------------------+--+>
| +-TABLES------+ |
| +-DEADLOCKS---+ |
| +-TABLESPACES-+ |
| '-BUFFERPOOLS-' |
'--+-CONNECTIONS--+---+-----------------------------+-'
+-STATEMENTS---+ '-WHERE--| Event Condition |--'
'-TRANSACTIONS-'
.-MANUALSTART--.
>----WRITE--TO--+-PIPE--pipe-name--------------------+---+--------------+>
'-FILE--path-name--| File Options |--' '-AUTOSTART----'
.-LOCAL--.
>-----+-----------------------+---+--------+-------------------><
'-ON NODE--node-number--' '-GLOBAL-'
Event Condition
.-AND | OR------------------------------------------------------------.
V |
|------+-----+--+--+-APPL_ID---+---+-=----------+---comparison-string--+--+->
'-NOT-' | +-AUTH_ID---+ | (1) | |
| '-APPL_NAME-' +-<>---------+ |
| +->----------+ |
| | (1) | |
| +->=---------+ |
| +-<----------+ |
| | (1) | |
| +-<=---------+ |
| +-LIKE-------+ |
| '-NOT--LIKE--' |
'-(Event Condition)------------------------------------'
>---------------------------------------------------------------|
File Options
|---+--------------------------------+-------------------------->
| .-NONE------------. |
'-MAXFILES--+-number-of-files-+--'
>-----+--------------------------+---+--------------------+----->
| .-pages--. | '-BUFFERSIZE--pages--'
'-MAXFILESIZE--+-NONE---+--'
.-BLOCKED----. .-APPEND--.
>-----+------------+---+---------+------------------------------|
'-NONBLOCKED-' '-REPLACE-'
Notes:
Command Parameters
This clause is a special form of the WHERE clause that should not be confused with a standard search condition.
To determine if an application will generate events for a particular event monitor, the WHERE clause is evaluated:
The WHERE clause is not evaluated for each event.
If no WHERE clause is specified then all events of the specified event type will be monitored.
The application program name is the first 20 bytes of the application program file name, after the last path separator.
The naming rules for pipes are platform specific. On UNIX operating systems pipe names are treated like file names. As a result, relative pipe names are permitted, and are treated like relative path-names (see path-name below). However, on OS/2, Windows 95 and Windows NT, there is a special syntax for a pipe name. As a result, on OS/2, Windows 95 and Windows NT 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).
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 (a path that starts with the root directory on AIX, or a disk identifier on OS/2, Windows 95 and Windows NT) is specified, then the specified path will be the one used. 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.
When a relative path is specified, the DB2EVENT directory is used to convert it into an absolute path. Thereafter, no distinction is made between absolute and relative paths. The absolute path is stored in the SYSCAT.EVENTMONITORS catalog view.
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.
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 minimum and default size of each buffer (if this option is not specified) is 1 page (that is, 2 buffers, each 4 K in size). The maximum size of the buffers is limited by the size of the database heap (DBHEAP) since the buffers are allocated from the heap. If using a lot of event monitors at the same time, increase the size of the DBHEAP database 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 database heap (DBHEAP). 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.
Sample Programs
Usage Notes
Examples
Example 1: The following example 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/. A maximum of 25 files will be created. Each file will be a maximum of 1024 4K pages long. The file I/O will be non-blocked.
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
Example 2: The following example creates an event monitor called DEADLOCKS_EVTS. This event monitor will collect deadlock events and will write them to the relative path DLOCKS. One file will be written, and there is no maximum file size. Each time the event monitor is activated, it will append the event data to the file 00000000.evt if it exists. The event monitor will be started each time the database is started. The I/0 will be blocked by default.
CREATE EVENT MONITOR DEADLOCK_EVTS
FOR DEADLOCKS
WRITE TO FILE 'DLOCKS'
MAXFILES 1
MAXFILESIZE NONE
AUTOSTART
Example 3: This example creates an event monitor called DB_APPLS. This event monitor collects connection events, and writes the data to the named pipe /home/jsmith/applpipe.
CREATE EVENT MONITOR DB_APPLS
FOR CONNECTIONS
WRITE TO PIPE '/home/jsmith/applpipe'