Unit of work event monitoring
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 the statistics event monitor and the MON_GET_UNIT_OF_WORK and MON_GET_UNIT_OF_WORK_DETAILS table functions.
You can use the unit of work event monitor to collect a listing of packages used within a unit of work and the nesting level at which it was used. This information helps facilitate stored procedure troubleshooting. You can also generate a listing of the executable IDs and associated statement-level metrics for statements that ran within a unit of work.
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
You have the choice of having the output for a unit of work event monitor output written to an unformatted event (UE) table, or a regular table. See Output options for event monitors for more information on how to choose the most appropriate output format.
Whichever type of table you use, when you create a unit of work event monitor, identify the table space where you plan to store the table or tables containing the output for your event monitor. The recommended practice is to have a table space that is dedicated and configured to store the table. However, when you create an event monitor, you can specify an existing table space. If you do not specify a table space, one is chosen for you.
CREATE EVENT MONITOR MY_UOW_EVMON FOR UNIT OF WORK WRITE TO UNFORMATTED EVENT TABLE (IN MY_EVMON_TABLESPACE)
Configuring data collection
- Basic unit of work data
- Information about the packages that ran within the unit of work
- A list of executable IDs for statements that ran within the unit of work.
|Data to collect||mon_uow_data||mon_uow_pkglist||mon_uow_execlist|
|Collect no unit of work data||NONE (default)||OFF(default)||OFF (default)|
|Collect only basic unit of work data||BASE||OFF (default)||OFF (default)|
|Collect package list information but not information about executable IDs||BASE||ON||OFF (default)|
|Collect information about executable IDs but not a list of packages||BASE||OFF (default)||ON|
|Collect basic unit of work data, package list information, and information about executable IDs||BASE||ON||ON|
- 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 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
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.
ALTER WORKLOAD REPORTS COLLECT UNIT OF WORK DATA BASE INCLUDE PACKAGE LIST, EXECUTABLE LIST
- 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.
Accessing event data that is captured by a unit of work event monitor
A unit of work event monitor can write data to a regular table or it can write data in binary format to an unformatted event (UE) table. You can access the data in regular tables by using SQL.
- Extracts data from an unformatted event table into an XML document.
- Extracts data from an unformatted event table into a set of relational tables.
If you are generating package listing information, you can use the EVMON_FORMAT_UE_TO_XML table function to generate a single XML document that contains both the base unit of work event monitor data and the package listing. The EVMON_FORMAT_UE_TO_TABLES procedure produces two tables: one for the basic unit of work event monitor information and another for the package listing information. You can join the two tables by using the values in the MEMBER, APPLICATION_ID, and UOW_ID columns.
- 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
java db2evmonfmt -d SAMPLE -ue SAMPLE_UOW_EVENTS -ftext -ss MyUOW.xsl -hours 24