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.