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.

Before you begin

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.

About this task

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:

Activities
Change history
Locking
Statistics
Unit of work

Some of the event monitors listed collect certain types of data by default after the event monitor is activated; others require that you explicitly enable data collection. Either way, you can enable data collection in one of two ways, depending on the scope of activities for which you want data collected:

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 class 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 payroll was 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 payroll workload.

Procedure

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 clause.
1. 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.
```
UPDATE DATABASE CONFIGURATION for SALES USING mon_lockwait HISTORY 
```
2. 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
```

What to do next

Now that the event monitor is created and active, and data collection is enabled, run your applications or workload.