Creating a file event monitor

When creating an event monitor you must determine where the information it collects is to be stored. File event monitors store event records in files. File event monitors and their options are defined by the CREATE EVENT MONITOR statement.

Before you begin

You will need SQLADM or DBADM authority to create a file event monitor.

About this task

A file event monitor streams event records to a series of 8-character numbered files with the extension "evt" (for example, 00000000.evt, 00000001.evt, and 00000002.evt). The data should be considered to be one logical file even though the data is broken up into smaller pieces (that is, the start of the data stream is the first byte in the file 00000000.evt; the end of the data stream is the last byte in the file nnnnnnnn.evt). An event monitor will never span a single event record across two files.

Procedure

Indicate that event monitor data is to be collected in a file (or set of files), and provide a directory location where event files are to be stored.
```
CREATE EVENT MONITOR dlmon FOR eventtype
                     WRITE TO FILE '/tmp/dlevents'
```
dlmon is the name of the event monitor.

/tmp/dlevents is the name of the directory path (on UNIX systems) where the event monitor is to write the event files.
Specify the types of events to be monitored. You can monitor one or more event types with a single event monitor.
```
CREATE EVENT MONITOR dlmon FOR CONNECTIONS, DEADLOCKS WITH DETAILS
                     WRITE TO FILE '/tmp/dlevents'
```
This event monitor will monitor for the CONNECTIONS and DEADLOCKS WITH DETAILS event types.
Specify the size of the file event monitor buffers (in 4K pages) by adjusting the BUFFERSIZE value:
```
CREATE EVENT MONITOR dlmon FOR CONNECTIONS, DEADLOCKS WITH DETAILS
                     WRITE TO FILE '/tmp/dlevents' BUFFERSIZE 8 
```
8 is the capacity in 4K pages of the two event file buffers.
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 size of the monitor heap, because the buffers are allocated from that heap. For performance reasons, highly active event monitors should have larger buffers than relatively inactive event monitors.
Indicate if you need the event monitor to be blocked or non-blocked. For blocked event monitors, each agent that generates an event will wait for the event buffers to be written to file if they are full. This can degrade database performance, as the suspended agent and any dependent agents cannot run until the buffers are clear. Use the BLOCKED clause to ensure no losses of event data:
```
CREATE EVENT MONITOR dlmon FOR CONNECTIONS, DEADLOCKS WITH DETAILS
                     WRITE TO FILE '/tmp/dlevents' BUFFERSIZE 8 
                     BLOCKED 
```
Event monitors are blocked by default. If database performance is of greater importance than collecting every single event record, use non-blocked event monitors. In this case, each agent that generates an event will not wait for the event buffers to be written to file if they are full. As a result, non-blocked event monitors are subject to data loss on highly active systems. Use the NONBLOCKED clause to minimize the additional processing time caused by event monitoring:
```
CREATE EVENT MONITOR dlmon FOR CONNECTIONS, DEADLOCKS WITH DETAILS
                     WRITE TO FILE '/tmp/dlevents' BUFFERSIZE 8 
                     NONBLOCKED 
```

Specify the maximum number of event files that can be collected for an event monitor. If this limit is reached, the event monitor will deactivate itself.

CREATE EVENT MONITOR dlmon FOR CONNECTIONS, DEADLOCKS WITH DETAILS
                     WRITE TO FILE '/tmp/dlevents' BUFFERSIZE 8 
                     NONBLOCKED MAXFILES 5

5 is the maximum number of event files that will be created.

You can also specify that there is no limit to the number of event files that the event monitor can create:

CREATE EVENT MONITOR dlmon FOR CONNECTIONS, DEADLOCKS WITH DETAILS
                     WRITE TO FILE '/tmp/dlevents' BUFFERSIZE 8 
                     NONBLOCKED MAXFILES NONE

Specify the maximum size (in 4K pages) for each event file created by an event monitor. If this limit is reached, a new file is created.

CREATE EVENT MONITOR dlmon FOR CONNECTIONS, DEADLOCKS WITH DETAILS
                     WRITE TO FILE '/tmp/dlevents' BUFFERSIZE 8 
                     NONBLOCKED MAXFILES 5 MAXFILESIZE 32

32 is the maximum number of 4K pages that an event file can contain.

This value must be greater than the value specified by the BUFFERSIZE parameter. You can also specify that there is to be no limit on an event file's size:

CREATE EVENT MONITOR dlmon FOR CONNECTIONS, DEADLOCKS WITH DETAILS
                     WRITE TO FILE '/tmp/dlevents' BUFFERSIZE 8 
                     NONBLOCKED MAXFILES NONE MAXFILESIZE NONE

Specify if the event monitor is to be activated automatically each time the database starts. By default, event monitors (with the exception of the WLM event monitors) are not activated automatically when the database starts.
- To create an event monitor that starts automatically when the database starts, issue the following statement:
```
CREATE EVENT MONITOR dlmon FOR CONNECTIONS, DEADLOCKS WITH DETAILS
                     WRITE TO FILE '/tmp/dlevents' BUFFERSIZE 8 
                     NONBLOCKED AUTOSTART
```
- To create an event monitor that does not start automatically when the database starts, issue the following statement:
```
CREATE EVENT MONITOR dlmon FOR CONNECTIONS, DEADLOCKS WITH DETAILS
                     WRITE TO FILE '/tmp/dlevents' BUFFERSIZE 8 
                     NONBLOCKED MANUALSTART
```
To activate or deactivate an event monitor, use the SET EVENT MONITOR STATE statement.

Results

Once a file event monitor is created and activated, it will record monitoring data as its specified events occur.