Working with event monitors

Generally, the process of creating and using event monitors to capture information about the system when certain events occur is similar for all event monitor types. First you create the event monitor, then you enable data collection, and finally, you access the data gathered.

To use an event monitor to capture event information:

1. Create the event monitor.
   To create an event monitor, use the appropriate version of the CREATE EVENT MONITOR statement. When you create an event monitor, you must choose how to record the data the event monitor collects. All event monitors can write their output to relational tables; however, depending on your specific purposes, there are different options that might be more appropriate.
2. Activate the event monitor.
   To activate the event monitor, use the SET EVENT MONITOR STATE statement.
   For example, for an event monitor called capturestats, use the following command:

   SET EVENT MONITOR capturestats STATE 1

   To turn off data collection by the event monitor, use the following statement:

   SET EVENT MONITOR capturestats STATE 0

   By default, some event monitors activate automatically upon database activation; others require that you activate them manually. However, an event monitor created with the AUTOSTART option will not automatically be activated until the next database activation. Use the SET EVENT MONITOR STATE statement to force a recently-created event monitor into the active state. To determine whether an event monitor starts automatically, refer to the reference information for the relevant CREATE EVENT MONITOR statement.
3. Enable the collection of data. (Only for LOCKING, ACTIVITIES, STATISTICS, UNIT OF WORK and PACKAGE CACHE event monitors)
   Enabling data collection involves configuring the database manager to gather specific types of data to be recorded by event monitors.

   Not all event monitors require data collection to be enabled; for those that do not, such as the TABLE event monitor, creating and activating them is sufficient to cause data to be collected. The threshold violations event monitor also starts data collection automatically; however, in this case, you must also define the thresholds for which you want data captured using the CREATE THRESHOLD statement.

   For those event monitors that require data collection to be enabled, there are different options available to you. Depending on the type of event monitor you are working with, you might set a database configuration parameter to enable data collection across the entire database. Alternatively, you might choose to enable the collection of specific kinds of data for specific types of workload objects.

   For example, to configure the collection of basic information for a unit of work event monitor when any unit of work in the system finishes, you can set the mon_uow_data parameter to BASE. Alternatively, to capture unit of work information only for a specific workload, you can specify the COLLECT UNIT OF WORK DATA BASE clause as part of the CREATE WORKLOAD or ALTER WORKLOAD statements.
4. Run your applications or queries.
   After the event monitor has been created, and activated, and you have enabled data collection, run the applications or queries for which you want to collect data.
5. Optional: Deactivate the event monitor.
   After you run the applications or queries for which you want data collected, you can deactivate the event monitor using the SET EVENT MONITOR STATE statement. (see step 2). Deactivating the event monitor is not necessary before proceeding to the next step, however leaving the event monitor active will result in disk space being used for data that you might not be interested in looking at.
6. Examine the data collected by the event monitor.
   Depending on the type of output the event monitor creates, there are different options for accessing the data collected. If the data is written directly to a relational table, you can use SQL to access the data contained in the table columns. On the other hand, if the event monitor writes to an unformatted event (UE) table, you must post-process the UE table using a command like db2evmonfmt or a procedure like EVMON_FORMAT_UE_TO_TABLES before you can view the event data.
7. Optional: Prune data that is no longer needed from the event monitor tables.
   For event monitors that you use on a regular basis, you might want to prune unneeded data from the tables. For example, if you use a unit of work event monitor to generate daily accounting reports about the system resources used by different applications, you might want to delete the current day's data from the event monitor tables once the reports have been generated.

   Tip: If you need to prune event monitor output regularly, consider using an unformatted event (UE) table to record event monitor output. Starting in DB2® V10.1, UE tables can be pruned automatically after data is transferred to regular tables.