DB2 Version 9.7 for Linux, UNIX, and Windows

Monitoring unit of work events

The unit of work event monitor records an event whenever a unit of work is completed, that is, whenever there is a commit or a rollback. This historical information about individual units of work is useful for chargeback purposes (charging by CPU usage) and for monitoring compliance with response time service level objectives.

The unit of work event monitor is one way to perform system perspective monitoring with request metrics. The most closely related alternatives or complements to the unit of work event monitor are either the statistics event monitor or the MON_GET_UNIT_OF_WORK and MON_GET_UNIT_OF_WORK_DETAILS table functions.

Starting with DB2 Version 9.7 Fix Pack 1, using the unit of work event monitor, you can collect a listing of packages used within a unit of work, as well as the nesting level at which it was used, to help facilitate stored procedure troubleshooting.

To create the unit of work event monitor and collect unit of work event monitor data, you must have DBADM or SQLADM authority.

Creating a unit of work event monitor and configuring data collection

Before you create a unit of work event monitor, identify the table space where you plan to store the unformatted event table for your event monitor. The recommended practice is to have a table space dedicated and configured to store the unformatted event table associated with any event monitor. Create the unit of work event monitor in a tablespace with at least 8K pagesize to ensure that the event data is contained within the inlined BLOB column of the unformatted event table. If the BLOB column is not inlined, then the performance of writing and reading the events to the unformatted event table might not be efficient.

The database manager attempts to inline the event_data BLOB column in the unformatted event table, but this is not always possible. To check that the rows in the unformatted event table have been inlined, use the ADMIN_IS_INLINED function. If the rows have not been inlined, use the ADMIN_EST_INLINE_LENGTH functions to determine how much space the rows need.

Your other options when you create an event monitor are to specify any existing table space or to not specify any and have one chosen by default.

To setup a unit of work event monitor using defaults and best practices, complete the following steps:

Create the event monitor by issuing the CREATE EVENT MONITOR statement. The following example uses defaults where possible and specifies to store the unformatted event table in an existing table space:
```
CREATE EVENT MONITOR MY_UOW_EVMON
  FOR UNIT OF WORK
   WRITE TO UNFORMATTED EVENT TABLE (IN MY_EVMON_TABLESPACE)
```
Configure what data to collect. The following statement illustrates a simple approach:
```
db2 update db cfg for dbname using mon_uow_data base
```

Configuring data collection

To configure data collection, you must also specify the subset of the system workload for which to capture events and how much detail to collect for each event. By default unit of work data is not collected. You can change the default settings by using one of the following settings:

The mon_uow_data database configuration parameter
The COLLECT UNIT OF WORK DATA clause of the CREATE and ALTER WORKLOAD statements.

The following levels for data collection are available to you:

NONE: No unit of work data collected.
BASE: Basic unit of work data collected.
PACKAGE LIST: The package list for transactions associated with this unit of work, as well as the basic unit of work data, is collected.

If either the mon_uow_data database configuration parameter or the COLLECT UNIT OF WORK DATA clause of the CREATE/ALTER WORKLOAD statement is set to BASE, then that is the effective setting for the workload.

If you want to enable data collection for only selected workloads, then set mon_uow_data database configuration parameter to NONE and set the level to BASE for the desired workloads.

Requests metrics is one of the types of information that you can collect with a unit of work event monitor. The unit of work event monitor is one of the interfaces affected by the setting for request metric collection. By default, request metrics are collected and reported in applicable table functions and event monitors, including the unit of work event monitor. You can change the default setting by using one of the following settings:

The mon_req_metrics database configuration parameter
The COLLECT REQUEST METRICS clause of the CREATE/ALTER SERVICE CLASS statement for a service superclass.

Changing these settings affects any table function or event monitor that can report request metrics.

Accessing event data captured by a unit of work 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 following table functions:

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.

Use these table functions to specify the data to extract using a SELECT statement. You have full control over selection, ordering, and other aspects provided by the SELECT statement.

If you are generating package listing information, EVMON_FORMAT_UE_TO_XML generates a single XML document that contains both the basic unit of work event monitor data as well as the package listing. EVMON_FORMAT_UE_TO_TABLES produces two tables, one for the base unit of work event monitor information, and another for the package listing information. You can join the two using the values contained in the MEMBER, APPLICATION_ID and UOW_ID columns.

You can also use the db2evmonfmt command to perform the following tasks:

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.
Control the output format by creating your own XSLT style sheets instead of using the ones provided by the db2evmonfmt command.

For example, the following command provides a unit of work report that:

Selects unit of work events that have occurred in the past 24 hours in the database SAMPLE. These event records are obtained from the unformatted event table called SAMPLE_UOW_EVENTS.
Provides formatted text output using the DB2EvmonUOW.xsl style sheet.

java db2evmonfmt -d SAMPLE -ue SAMPLE_UOW_EVENTS -ftext -ss DB2EvmonUOW.xsl -hours 24