Output options for event monitors
Event monitors can report the data they collect in a number of ways. All event monitors can write the data they collect to tables; some write to unformatted event (UE) tables, which can help improve performance. Others can also write directly to a file or named pipe.
- Regular tables
- All event monitors can write to regular tables that can be queried
directly using SQL. For a given event, each of the monitor elements
or metrics collected for the event is written to its own column in
the table. This makes it possible to use a SELECT statement query
the output to examine the values for a specific monitor element.
To create an event monitor that writes to tables, specify the WRITE TO TABLE clause in the CREATE EVENT MONITOR statement. Depending on the event monitor, one or more tables are created to contain the ouput, each table containing monitor elements that belong to a single logical group. See Target tables, control tables, and event monitor table management for details about the specific table produced for each logical group.
Tables can be stored in a table space of your choosing; however the target table of a CREATE EVENT MONITOR statement must be a non-partitioned table.
Note: There are two types of event monitors that write to tables. The first type includes event monitors created in version 9.7 and later releases. These include the unit of work, package cache, locking and change history event monitor. As of Db2® version 10.1, the first three of these event monitors can write their output to regular tables as an alternative to UE tables. The change history event monitor writes only to regular tables.The second type are the event monitors implemented before Db2 version 9.7. These include all other event monitors.
Generally, after an event monitor of either type has been created, they work in much the same way. That is, you can use SQL to directly access the data in the tables that they produce. However, the older event monitors in the second category have additional options that you can specify when creating the event monitor. In addition, only event monitors in the second category are capable of writing also to files and named pipes.
- Unformatted event (UE) tables
- UE tables are relational tables; however, they have only a limited number of columns. Most of
the data associated with each event is written to a column containing an inline binary (BLOB)
object. Writing event data in binary format reduces the time it takes to write each record to the
table. For this reason, UE tables are particularly useful where event monitor performance is
important, which might be the case on highly I/O or CPU-bound systems.
However, because the event data is written in binary format, you cannot use SQL to extract legible data. You must perform post-processing on the UE table to extract the data stored in binary format. Another benefit of using UE tables is that you can have UE table data pruned automatically during post-processing. The EVMON_FORMAT_UE_TO_TABLES procedure has an option to delete data from the UE table after it has been successfully extracted.
To create an event monitor that writes to an unformatted event table, specify the WRITE TO UNFORMATTED EVENT TABLE clause in the CREATE EVENT MONITOR statement. Only one UE table is created per event monitor.
- Files
- Some event monitors support sending their output directly to files maintained by the file system. This type of output is useful if you do not want the event monitor output to be subject to the additional processing time caused when being managed within the database, or if you want to look at the data while the database is offline. To create an event monitor that writes to files, specify the WRITE TO FILE clause in the CREATE EVENT MONITOR statement.
- Named pipes
- If you want to have an application process event data as it is generated, you can use a named
pipe event monitor. These types of event monitors send their output directly to a named pipe so that
the data can be used by another application immediately. This might be useful if you need to
manipulate event data in real time.
To create an event monitor that writes to a named pipe, specify the WRITE TO PIPE clause in the CREATE EVENT MONITOR statement.
Output type | Scenarios where this output type is useful |
---|---|
Regular tables |
|
Unformatted event (UE) tables |
|
Files |
|
Pipes |
|
Event monitor type | Regular table | Unformatted event table | File | Named pipe |
---|---|---|---|---|
Activity | Yes | Yes | Yes | |
Buffer pool | Yes | Yes | Yes | |
Change history | Yes | |||
Connections | Yes | Yes | Yes | |
Database | Yes | Yes | Yes | |
Deadlocks*(all variations) | Yes | Yes | Yes | |
Locking | Yes | Yes | ||
Package cache | Yes | Yes | ||
Statement | Yes | Yes | Yes | |
Statistics | Yes | Yes | Yes | |
Table space | Yes | Yes | Yes | |
Table | Yes | Yes | Yes | |
Threshold violations | Yes | Yes | Yes | |
Transaction* | Yes | Yes | Yes | |
Unit of work | Yes | Yes | ||
* Deprecated event monitor. |