Enabling event monitor data collection
Depending on the type of event monitor you are using, you might need to configure collection after you create the event monitor. By default, some event monitors collect certain data immediately when activated. Other event monitors require that you explicitly configure data collection independently of creating the event monitor. These types of event monitors are sometimes referred to as passive event monitors.
All event monitors must be activated before any data is written its target output table or tables (regular or UE), file or pipe. Some event monitors are configured by default as AUTOSTART event monitors. This means they are activated automatically when the database is activated. Others are configured by default to required that you activate them manually. Either way, you can override the default startup options. However, to start an automatic event monitor after you create it, but before the next database activation, you must use the SET EVENT MONITOR STATE statement to activate it manually.
Before you begin
Some event monitors support the use of a WHERE clause on the CREATE or ALTER EVENT MONITOR statement to capture event information selectively. The following event monitors, however, provide the ability to control what event data is collected independently of the event monitor definition:
About this task
- Change history
- Unit of work
- All activities in the database
- To have monitor data collected across all activities in the database,
you modify the appropriate configuration parameter for the type of
data you are interested in. For example, to have unit of work data
collected for all units of work that run in the database, set mon_uow_data to
BASE. In some cases, the default settings for configuration parameters
are such that some type of data is always collected if there is an
appropriate event monitor active to receive the date. For example,
the default setting for mon_req_metrics is BASE;
unless you override this setting, any active statistics or unit of
work event monitor will record the values for the BASE set of request
monitor elements.Remember: Event monitors that support the use of the WHERE predicate collect only the data that satisfies the conditions specified in that predicate, regardless of the settings for any relevant configuration parameters.
- Selected activities
- Some event monitors - in particular, the workload management event
monitors (threshold violations, statistics and activities) - provide
the ability to control data collection for specific workload management
objects. For example, you might choose to collect activity information
for activities running in a specific service superclass. Configuring
collection at this level generally involves adding a COLLECT clause
to the CREATE or ALTER WORKLOAD (or SERVICE CLASS or WORK ACTION)
statements to specify what type of information to collect for activities
running under the auspices of that WLM object. For example, to enable
the collection of extended statistics information for the service
urgent, you might use the following statement:
ALTER SERVICE CLASS urgent COLLECT AGGREGATE ACTIVITY DATA EXTENDED
Note: If a COLLECT clause is specified in a WLM CREATE or ALTER statement, the settings specified in the clause take precedence for that WLM object over any database-wide setting configured using a configuration parameter. For example, if mon_req_metrics is set to EXTENDED, and if workload
payrollwas configured to collect BASErequest metrics (for example, CREATE WORKLOAD payroll COLLECT REQUEST METRICS BASE), then extended request metrics are collected for all activities in the database except for the
To enable collection of data for one of the types of event monitors shown at the beginning of this section, perform the following steps:
- Determine what, if any data is already collected by default. The data you are interested in might be collected without you having to change any settings.
- Decide on the scope of activities for which you want to collect data. Do you want to collect data for the entire database, or only for specific workloads, service class or work actions?
- Decide what types of monitor elements you want to collect. Some event monitors support the collection of different types of monitor data, such as request monitor elements, activity data, and so on.
- For the different sets of monitor data collected, decide the scope of data to be collected within each set. You generally have the choice of collecting no data (NONE), basic data (BASE), or extended data (EXTENDED). See to determine what data is collected for each setting.
- Based on the decisions made in the preceding steps, configure
data collection using either a configuration parameter or a COLLECT
- To configure collection across
the entire database, set the appropriate configuration parameter.
For example, to enable the collection of lock wait information with
history by the locking event monitor on the database SALES, run the
UPDATE DATABASE CONFIGURATION for SALES USING mon_lockwait HISTORY
- To configure collection for a specific workload, create
or modify the workload, including the appropriate COLLECT clause.
For example, to configure the collection of lock wait data with statement history for locks waiting longer than 5 seconds in the MANAGERS workload, run a statement like the one that follows:
ALTER WORKLOAD MANAGERS COLLECT LOCK WAIT DATA FOR LOCKS WAITING MORE THAN 5 SECONDS WITH HISTORY
- To configure collection across the entire database, set the appropriate configuration parameter. For example, to enable the collection of lock wait information with history by the locking event monitor on the database SALES, run the following command.
Now that the event monitor is created and active, and data collection is enabled, run your applications or workload.