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.

Depending on how you want to use the information collected by event monitors, and on the type of event monitor, you can choose to have the output that the event monitors collect produced in different ways. The output types available include:

Regular tables

As of DB2® V10.1, 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 were introduced in DB2 Version 9.7 for the new event monitors added in that release. 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 writers 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 writers to a named pipe, specify the WRITE TO PIPE clause in the CREATE EVENT MONITOR statement.

Depending on your needs, one type of event monitor output might be more appropriate than another. Table 1 provides an summary of when specific output types are particularly useful.

Table 1. Summary of different event monitor output types
Output type	Scenarios where this output type is useful
Regular tables	When you want to examine monitoring data at a later point in time In systems that are not approaching the maximum capacity for CPU, log file or disk storage Where immediate access to data using SQL is desirable
Unformatted event (UE) tables	When you want to examine monitoring data at a later point in time In systems where event monitor performance is a priority, or where there are constraints on CPU, log file or disk usage Where the added step of post-processing of data is not an issue
Files	In systems where you do not want or need to manage monitor data as part of the database. (Eliminates the additional processing time of logging, inserts, maintaining consistency) When you want to store the data outside of the database being monitored When you want to examine the data offline at later point in time
Pipes	Streaming event data to an application that processes it immediately. When there is no need to access event data at a later point in time.

Not all event monitors support all output types. For example, only the unit of work, package cache and locking event monitor can produce a UE table. Table 2 shows what output options are available for different types of event monitors:

Table 2. Output options for event monitors
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.