Collecting unit of work event data and generating reports

You can use the unit of work event monitor to collect data about transactions that you can use for chargeback purposes. The collected transaction event data is in an unreadable form in an unformatted event table. You can use this data to create a readable text report.

Before you begin

To collect unit of work event monitor data, you must have SYSADM or SYSCTRL authority.

About this task

This task provides instructions for collecting unit of work event data for a particular workload.

Package listing and execution list information is also collected if you set both the mon_uow_pkglist and mon_uow_execlist configuration parameters to ON. Alternatively, you can collect package listing and execution list information for a workload, regardless of the settings of the mon_uow_pkglist and mon_uow_execlist configuration parameters, by altering the ALTER WORKLOAD statement as follows:

For package listing information, replace the BASE option with the BASE INCLUDE PACKAGE LIST option.
For execution list information, replace the BASE option with the BASE INCLUDE EXECUTABLE LIST option.
For package listing and execution list information, replace the BASE option with the BASE INCLUDE PACKAGE LIST, EXECUTABLE LIST option.

The unit of work event monitor collects information that identifies application transactions and the corresponding CPU usage. Examples of information that the unit of work event monitor collects for a transaction event are as follows:

Total CPU usage time (TOTAL_CPU_TIME monitor element)
Application handle (APPLICATION_HANDLE monitor element)

Restrictions

Input data values are not viewable if you do not have SYSADM or SYSCTRL authority.

Procedure

To collect detailed information regarding unit of work events:

Create a unit of work event monitor called UOWEVMON by issuing the CREATE EVENT MONITOR FOR UNIT OF WORK statement, as shown in the following example:
```
CREATE EVENT MONITOR UOWEVMON FOR UNIT OF WORK
   WRITE TO UNFORMATTED EVENT TABLE
```
Activate the UOWEVMON unit of work event monitor by issuing the following statement:
```
SET EVENT MONITOR UOWEVMON STATE 1
```
Enable unit of work event data collection at the workload level by issuing the ALTER WORKLOAD statement with statement history.
For example, to collect unit of work data for the FINANCE and PAYROLL applications, issue the following statements:
```
ALTER WORKLOAD finance COLLECT UNIT OF WORK DATA BASE
ALTER WORKLOAD payroll COLLECT UNIT OF WORK DATA BASE
```
To collect unit of work transaction events, rerun the workload.
Connect to the database.
Produce a flat-text report that is based on the event data that is collected in the unformatted event table by using the XML parser tool, db2evmonfmt, as shown in the following example:
```
java db2evmonfmt -d db_name -ue table_name -ftext -u user_id -p password
```
Analyze the report to determine how much CPU time applications are using so that appropriate charges can be billed.
If you want to turn off unit of work data collection for both the FINANCE and PAYROLL applications, issue the following statements:
```
ALTER WORKLOAD finance COLLECT UNIT OF WORK DATA NONE
ALTER WORKLOAD payroll COLLECT UNIT OF WORK DATA NONE
```

Example

The following example of a report was obtained by using the db2evmonfmt tool to convert the data that the unit of work event monitor collected in the unformatted event table:

-------------------------------------------------------
Event ID               : 1
Event Type             : UOW
Event Timestamp        : 2008-10-31-13.29.04.130849
Member of detection    : 0
-------------------------------------------------------

Database Level Details
----------------------
Member Activation Time     : 2008-10-31T13:28:48.538973
Coordinator Member         : 0

Connection Level Details
------------------------
Application ID             : *LOCAL.gstager.081031172848
Application Handle         : 20
Application Name           : db2bp
Session Authorization ID   : GSTAGER
System Authorization ID    : GSTAGER
Connection Timestamp       : 2008-10-31T13:28:48.538973
Client Process ID          : 28167
Client Platform            : 30
Client Product ID          : SQL09070
Client Hostname            : gilera
Client Port Number         : 30143

UOW Level Details
------------------------
Start Time                 : 2008-10-31T13:28:51.560138
Stop Time                  : 2008-10-31T13:29:04.130849
Completion Status          : COMMIT
UOW ID                     : 5
Workoad Occurrence ID      : 1
Workload Name              : SYSDEFAULTUSERWORKLOAD
Workoad ID                 : 1
Client userid              : 
Client Workstation Name    : 
Client Application Name    : 
Client Accounting String   : 
Local Transaction ID       : 00000000000000EB
Global Transaction ID      : 0000000000000000000000000000000000000000
Log Space Used             : 0

UOW Metrics
------------------------
TOTAL_CPU_TIME                 : 7459
TOTAL_WAIT_TIME                : 0
ACT_ABORTED_TOTAL              : 0
...