DB2 Version 9.7 for Linux, UNIX, and Windows

Event monitors that write to an unformatted event table

DB2® 9.7 introduces some event monitors with a new type of target, the unformatted event table. This type of event monitor provides better performance, new CREATE EVENT MONITOR statement options, and the new interfaces to access data for analysis.

Characteristics of unformatted event table event monitors affect how you can perform the following tasks:

Creating the event monitor and configuring data collection
Managing event monitor operations
Accessing event data captured by the event monitor

Typically, you can achieve all your monitoring needs by creating a single event monitor per database for a given type of event (for example, lock events). You can alter settings to increase or decrease the amount of data that you can collect with the monitor to address changing monitoring needs. This contrasts with some older event monitors where a more common practice is to create a multiple event monitors, each geared to collect a particular monitoring need.

Creating the unformatted event table associated with an event monitor

One aspect of creating an event monitor is specifying where to write the data that the monitor collects. This type of event monitor always writes data in binary format to an unformatted event table. The unformatted event table is a target type introduced in DB2 9.7. An unformatted event table is created implicitly each time you create an event monitor. The CREATE EVENT statement for this type of event monitor includes the clause WRITE TO UNFORMATTED EVENT TABLE.

The CREATE EVENT MONITOR statement includes the following options for configuring the unformatted event table:

table name - By default, the unformatted event table is named based on the event monitor name.
tablespace name - By default, the unformatted table is created in the table space IBMDEFAULTGROUP over which the user has USE privilege if it exists. However, the recommended practice is to define a tablespace optimized for your event monitors, as described below.
PCTDEACTIVATE - The default value is 100, which means that the event monitor deactivates when the table space becomes full.

The following considerations about the table space for the unformatted event table must be taken:

Create a table space for your event monitor unformatted event tables that is configured for performance. Use the following clauses with the CREATE TABLESPACE statement:
- Specify a page size (PAGESIZE) as large as possible, up to and including 32KB.
- Specify the NO FILE CACHING SYSTEM option.
In a partitioned database environment, consider on which partitions the table space exists. If a table space for a target unformatted event table does not exist on some database partition, data for that target unformatted event table is ignored. This behavior allows users to choose a subset of database partitions for monitoring to be chosen, by creating a table space that exists only on certain database partitions.

Other useful information about unformatted event tables includes the following:

The SYSCAT.EVENTTABLES catalog view lists event monitors, their associated unformatted table, and other details.
The columns of the unformatted event table are described in a topic listed in the related links.

Configuring data collection for an event monitor

Setting up an event monitor involves specifying what data to collect. Aspects include which subset of the system workload to monitor, what type of events to collect, how much detail to collect for each event, and enabling/disabling data capture (turning the data capture on and off). Considerations for configuring data collection are the following:

With this type of event monitor, you configure data collection primarily by setting properties of individual workload definitions using the CREATE/ALTER WORKLOAD statement. That is, you can specify different data collection settings for different workloads. The CREATE/ALTER WORKLOAD statement includes clauses specific to particular types of event monitor.
By default, this type of event monitor is automatically activated. You can specify that the event monitor be activated manually by specifying the MANUALSTART keyword in the CREATE EVENT MONITOR statement. You can then control the event monitor with the SET EVENT MONITOR STATE statement.
As mentioned in another context, in a partitioned database environment you can choose a subset of database partitions to monitor with your event monitor. When you create the event monitor, specify a table space for the unformatted event table that resides only on those partitions you want to monitor. If the unformatted event table does not exist on a given database partition, the event monitor will not collect data for that partition.
Data collection for this type of event monitor is not affected by system monitor switch settings set using the UPDATE MONITOR SWITCHES statement nor is event capture is turned on and off using the SET EVENT MONITOR STATE statement.

Managing event monitor operations

The following points provide guidance for managing ongoing operation of an event monitor:

At any time, you can change your specification of what data to collect by using the ALTER WORKLOAD statement.
If you specified the MANUALSTART option in the CREATE EVENT MONITOR statement, you can start and stop data collection using the SET EVENT MONITOR STATE statement.
Unformatted event tables must be manually pruned.
If an unformatted event table reaches the maximum space allotted, the event monitor will deactivate.
If an event monitor is no longer needed, use the DROP statement to drop an event monitor. Issuing the DROP statement does not drop the unformatted event table that is associated with the event monitor. The associated unformatted event table must be manually dropped after the event monitor is dropped. If you don't drop the unformatted event table, you will encounter difficulties if you subsequently try to create another event monitor whose unformatted event table has the same name as an existing one.

Accessing event data captured by an event monitor

This type of event monitor writes data in a binary format to an unformatted event table. You can access this data using the db2evmonfmt command or routines provided for this purpose.

With the db2evmonfmt command you can:

select events of interest based on the following attributes: event ID, event type, time period, application, workload, or service class.
choose whether to receive the output in the form of a text report or a formatted XML document.
completely control the output format by creating your own XSLT style sheets instead of using the ones provided with db2evmonfmt.

You can also extract data from an unformatted event table using the following routines:

EVMON_FORMAT_UE_TO_XML - extracts data from an unformatted event table into an XML document.
EVMON_FORMAT_UE_TO_TABLES - extracts data from an unformatted event table into a set of relational tables.

With these routines, you can use a SELECT statement to specify the exact rows from the unformatted event table that you want to extract.