Collecting unit of work event data and generating reports

You can use the unit of work event monitor to collect data about transactions that can be used for chargeback purposes. After the transaction event data has been collected in an unreadable form in an unformatted event table, this task describes how to obtain a readable text report.

To collect detailed information regarding unit of work events, perform the following steps:

1. Create a unit of work event monitor called uowevmon by using 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

2. Activate the unit of work event monitor called uowevmon by running the following statement:

   SET EVENT MONITOR uowevmon STATE 1

3. Enable the unit of work event data collection at the workload level using the ALTER WORKLOAD statement with statement history. 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

4. Rerun the workload in order to collect unit of work transaction events.
5. Connect to the database.
6. Obtain the unit of work event report using the following approach:
   1. Use the XML parser tool, db2evmonfmt, to produce a flat-text report based on the event data collected in the unformatted event table, for example:

      java db2evmonfmt -d db_name -ue table_name -ftext -u user_id -p password

7. Analyze the report to determine how much CPU time applications are using so that appropriate charges can be billed.
8. If you want to turn OFF unit of work data collection for both FINANCE and PAYROLL applications, run the following statements:

   ALTER WORKLOAD finance COLLECT UNIT OF WORK DATA NONE
   ALTER WORKLOAD payroll COLLECT UNIT OF WORK DATA NONE

-------------------------------------------------------
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
...