Unit of work event monitoring

To create a unit of work event monitor using defaults and best practices, use the CREATE EVENT MONITOR statement. The following sample statement uses defaults where possible and specifies that output will be stored in a UE table in the MY_EVMON_TABLESPACE table space:

CREATE EVENT MONITOR MY_UOW_EVMON
  FOR UNIT OF WORK
   WRITE TO UNFORMATTED EVENT TABLE (IN MY_EVMON_TABLESPACE)

You can specify four different collection levels for unit of work data:

1. None
2. Basic unit of work data
   1. Information about the packages that ran within the unit of work
   2. A list of executable IDs for statements that ran within the unit of work.

You can use database configuration parameters to control the collection of unit of work data for all unit of work event monitors that are active in the database. Alternatively, to control the collection of information for specific workloads, service classes, or work actions, you can use the CREATE and ALTER statements for the appropriate workload objects.

To configure data collection at the database level, set the mon_uow_data database configuration parameter and, optionally, the mon_uow_pkglist and mon_uow_execlist database configuration parameters by using the UPDATE DATABASE CONFIGURATION command. Possible combinations of values for these parameters are shown inTable 1:

• If you do not set any of the configuration parameters, no unit of work data is collected unless you enable collection for specific workload objects. You can enable collection for specific workload objects by using either the CREATE or ALTER statement for the appropriate workload object type, for example, the CREATE SERVICE CLASS or ALTER WORKLOAD statement.
• To collect basic unit of work data but no package list or executable ID information, you can set the mon_uow_data configuration parameter to BASE and omit the mon_uow_pkglist and mon_uow_execlist configuration parameters. If you do not explicitly set them, the default value of OFF is used.
• To collect one or both of package list and executable ID information, you must also set the mon_uow_data configuration parameter to BASE. If you set the mon_uow_data configuration parameter to NONE, no information is collected, regardless of the settings of the mon_uow_pkglist and mon_uow_execlist configuration parameters.

To control the collection of data for specific workload objects, use the COLLECT UNIT OF WORK DATA clause of the CREATE or ALTER statement for the specific type of workload object that you are interested in. For example, to collect basic unit of work event data and package list information for the workload REPORTS, you might issue a statement such as this:

ALTER WORKLOAD REPORTS COLLECT UNIT OF WORK DATA BASE INCLUDE PACKAGE LIST

To collect both package list information and a list of the executable IDs for statements that are run in the unit of work, you might use this statement:

ALTER WORKLOAD REPORTS COLLECT UNIT OF WORK DATA BASE INCLUDE PACKAGE LIST,
 EXECUTABLE LIST

The settings that are shown in Table 1 apply to all workloads running in the system unless you override these settings for specific workloads by using the CREATE WORKLOAD or ALTER WORKLOAD statement. If you want to collect base-level information for all workloads but also want to collect package list information for selected workloads, set the mon_uow_data database configuration parameter to BASE. Then, use the CREATE WORKLOAD or ALTER WORKLOAD statement to set the level to BASE PACKAGE LIST for the workloads that you are interested in.

By default, applicable table functions and event monitors, including the unit of work event monitor, collect and report request metrics. You can change the default setting as follows:

• By using the mon_req_metrics database configuration parameter
• By using the COLLECT REQUEST METRICS clause of the CREATE SERVICE CLASS or ALTER SERVICE CLASS statement for a service superclass.

Changing the default setting affects any table function or event monitor that can report request metrics.

To access data in a UE table, use one of 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.

When you use one of these table functions, you can specify which data to extract by including a SELECT statement as one of the parameters to the function. You have full control over selection, ordering, and other aspects provided by the SELECT statement.

You can also use the db2evmonfmt command to help 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 occurred in the past 24 hours in the database SAMPLE. These event records are obtained from the unformatted event table called SAMPLE_UOW_EVENTS. The command creates formatted text output by using the MyUOW.xsl style sheet.

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