Calculating the CPU time used by different applications or workloads with the unit of work event monitor

This topic shows one way that you can use the unit of work event monitor in day-to-day database operations. In some business environments, departments are billed for the processing time their applications use. You can use the unit of work event to record the CPU time used by different application, workloads, or service classes. This information can, in turn, be used in accounting applications that perform billing for system resources.

Before you begin

The CREATE EVENT MONITOR statement requires a table space with a page size of at least 8 K to store the unformatted event (UE) table produced by the event monitor. Unless a table space is explicitly named in the CREATE EVENT MONITOR statement, the default table space for the database is used.

About this task

This task describes a basic scenario for charge-back accounting. In the example that follows, all work performed on the system is tracked. From the data gathered, reports are created that show the CPU time used by different applications.

Depending on how your organization is set up, tracking system time based on workload might be appropriate. Alternatively, you can also look at the CPU time used in different service super classes, by specific workloads, or even by different users. If the data is written to relational tables, as the example in this task shows, you can use SQL to query and present the data in almost limitless ways.

Note: Activities within a unit of work can run in different service subclasses. For this reason, it is not appropriate to aggregate unit of work information by service subclass. If you want to aggregate CPU time by service class, use the activity event monitor instead.

Procedure

Create a unit of work event monitor to capture information about units of work as they finish.
For example, to create an event monitor called TRACKWORK, you might could use the following SQL:
```
CREATE EVENT MONITOR TRACKWORK FOR UNIT OF WORK WRITE TO UNFORMATTED EVENT TABLE
```
This statement creates a unit of work event monitor that writes to an unformatted event (UE) table. The UE table has the same name as the event monitor itself, TRACKWORK, and it is stored in the default table space.
Tell the database manager that you want to collect event information for all units of work completed on the database by running the following command:
```
UPDATE DATABASE CONFIGURATION FOR dbname USING MON_UOW_DATA BASE
```
This command causes information about all units of work executed on the data server to be sent to the active unit of work event monitors when the units of work complete. See Configuring data collection for more information about controlling the scope of the unit of work data that is collected.
Next, activate the event monitor:
```
SET EVENT MONITOR TRACKWORK STATE 1
```
Note: By default, this event monitor starts automatically upon database activation, because the AUTOSTART option is applied by default. However, because this event monitor is being created in an already-active database, you must use the SET EVENT MONITOR command to start it manually.

From this point on, the unit of work event monitor captures information for each unit of work as it runs to completion. As each unit of work completes, the event monitor adds a record for the event to the UE table TRACKWORK.
When you are ready to collect data for reporting purposes, you must extract the records from the TRACKWORK UE table.
You can view this information in XML or relational format, using either the EVMON_FORMAT_UE_TO_XML or the EVMON_FORMAT_UE_TO_TABLES procedure to convert the data in the UE table. Alternatively, you can use the db2evmonfmt tool to create a text report of the information returned by the event monitor. This example shows the use of EVMON_FORMAT_UE_TO_TABLES to create relational tables that you can query in whatever way suits your needs.
```
CALL EVMON_FORMAT_UE_TO_TABLES 
   ('UOW', NULL, NULL, NULL, NULL, NULL, NULL, -1, 'SELECT * FROM TRACKWORK')
```
The EVMON_FORMAT_UE_TO_TABLES procedure examines the UE table TRACKWORK produced by the event monitor; it selects each of the records from the UE table, and from them, creates rows containing the data collected by the unit of work event monitor in two relational tables:
- UOW_EVENT
- UOW_METRICS
The first table contains the most frequently used monitor elements and metrics associated with each event captured. The second contains detailed metrics for each event.
Notes:
- If you specify PKGLIST rather than BASE for the MON_UOW_DATA configuration parameter in step 2, the EVMON_FORMAT_UE_TO_TABLES procedure creates a third table called UOW_PACKAGE_LIST. This table contains package list information related to the units of work. However, in this example, because only basic monitor elements are collected (see step 2), this table will not contain any data. (See Unit of work event monitor package listing information for more information about how the package list information can be used.)
- The values in the columns of UOW_METRICS can also be found in the XML document contained in the METRICS column of the UOW_EVENT table. They are provided in the UOW_METRICS table for more convenient, column-oriented access.

Query the tables produced in the previous step to see how CPU time was used by applications. The statement that follows returns a breakdown of total CPU time used by different users on the system since the unit of work event monitor was initialized. (This example assumes that client applications have identified themselves to the database using the sqleseti API, or through whatever application development environment you might be using, such as IBM® Rational® Application Developer for WebSphere® Software.

SELECT SUBSTR(E.CLIENT_USERID,1,10) AS CLIENT_ID,
       SUBSTR(E.CLIENT_APPLNAME,1,80) AS CLIENT_APP, 
       SUBSTR(E.CLIENT_WRKSTNNAME,1,10) AS WKSTN,
       SUM(M.TOTAL_CPU_TIME) AS CPU_TIME  
       FROM UOW_EVENT E, UOW_METRICS M 
WHERE  M.APPLICATION_ID = E.APPLICATION_ID
   AND M.UOW_ID = E.UOW_ID 
   AND M.MEMBER = E.MEMBER 
GROUP BY E.CLIENT_USERID, E.CLIENT_APPLNAME, E.CLIENT_WRKSTNNAME
ORDER BY CPU_TIME DESC;

The preceding query returns the following results:

CLIENT_ID  CLIENT_APP                                                                       WKSTN      CPU_TIME            
---------- -------------------------------------------------------------------------------- ---------- --------------------
                                                                                                                  987770013
           DB2BATCH                                                                                               249375000
           CLP C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\TMP\CCSCRIPT1003021324173                        91181678
           CLP C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\TMP\CCSCRIPT1004201047173                        66097348
           CLP C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\TMP\CCSCRIPT1003191536588                        28824420
           CLP C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\TMP\CCSCRIPT1003191536434                        27555568
           CLP C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\TMP\CCSCRIPT1003221122075                        16203116
           CLP C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\TMP\CCSCRIPT1003221118191                        15759227
           CLP C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\TMP\CCSCRIPT1003221531062                        15630121
           CLP C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\TMP\CCSCRIPT1003221117466                        15236718
           CLP C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\TMP\CCSCRIPT1003221116141                        14607249
           CLP C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\TMP\CCSCRIPT1003251550366                        14427883
           CLP C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\TMP\CCSCRIPT1003051054311                         1312500
           CLP C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\TMP\CCSCRIPT1003051053301                         1296875
           CLP C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\TMP\CCSCRIPT1003051139066                         1296875
           CLP C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\TMP\CCSCRIPT1003051152281                         1281250
           CLP C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\TMP\CCSCRIPT1003041230283                         1046875
                                                                                            asrisk2                 1031250
           CLP C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\TMP\CCSCRIPT1003291503479                          515625
           CLP C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\TMP\CCSCRIPT1003251506219                          484375
           CLP C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\TMP\CCSCRIPT1003221444488                          453125
           CLP C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\TMP\CCSCRIPT1003021323249                          406250
           CLP C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\TMP\CCSCRIPT1003251544498                          296875
           CLP C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\TMP\CCSCRIPT1003171431559                          171875
           CLP C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\TMP\CCSCRIPT1003041227488                          156250
           CLP C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\TMP\CCSCRIPT1003221117188                          109375
           CLP C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\TMP\CCSCRIPT1003021333329                           62500
           CLP C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\TMP\CCSCRIPT1003191502148                           62500
           CLP C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\TMP\CCSCRIPT1003191527385                           62500
           CLP C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\TMP\CCSCRIPT1003191528492                           62500
           CLP C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\TMP\CCSCRIPT1003191530518                           62500
           CLP C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\TMP\CCSCRIPT1003191533265                           62500
           CLP C:\Documents and Settings\All Users\Application Data\IBM\DB2\DB2COPY1\DB2DAS                           62500

At this point, the unit of work event monitor TRACKWORK is still collecting information. Depending on how you want to track CPU time used by different applications, users or workloads, you can choose to take one of the following courses of action:
- If you want to calculate CPU usage on a daily basis, you can leave this unit of work event monitor active. Each day, run the EVMON_FORMAT_UE_TO_TABLES procedure to retrieve just the time-spent metrics for the preceding day:
```
CALL EVMON_FORMAT_UE_TO_TABLES 
     ('UOW', NULL, NULL, NULL, NULL, NULL, NULL, -1, 
      'SELECT * FROM TRACKWORK 
      WHERE (DATE(EVENT_TIMESTAMP)=(CURRENT DATE - 1 DAY))'
     )
```
  With this approach, the three relational tables produced by the EVMON_FORMAT_UE_TO_TABLES procedure continue to grow, providing a history of CPU usage over time. The query in step 5 returns the cumulative totals for CPU time since the tables were first created with the EVMON_FORMAT_UE_TO_TABLES procedure. You can modify that query to show only the results from the previous day as follows:
```
SELECT SUBSTR(E.CLIENT_USERID,1,10) AS CLIENT_ID,
       SUBSTR(E.CLIENT_APPLNAME,1,80) AS CLIENT_APP, 
       SUBSTR(E.CLIENT_WRKSTNNAME,1,10) AS WKSTN,
       SUM(M.TOTAL_CPU_TIME) AS CPU_TIME  
       FROM UOW_EVENT E, UOW_METRICS M 
WHERE  M.APPLICATION_ID = E.APPLICATION_ID
   AND M.UOW_ID = E.UOW_ID 
   AND M.MEMBER = E.MEMBER 
   AND(DATE(E.EVENT_TIMESTAMP)=(CURRENT DATE - 1 DAY))
GROUP BY E.CLIENT_USERID, E.CLIENT_APPLNAME, E.CLIENT_WRKSTNNAME
ORDER BY CPU_TIME DESC;
```
  Tip: If you want to track CPU usage on a daily basis, but also want to manage how much data you collect on your system, remove data you no longer need from the UE table after you have updated the relational tables. For example, to delete the data collected on the previous day from the UE table TRACKWORK, use a DELETE statement similar to the one that follows:
```
DELETE FROM TRACKWORK WHERE (DATE(EVENT_TIMESTAMP)=(CURRENT DATE - 1 DAY))
```
  While an event monitor is active, it holds an intention exclusive (IX) table lock on any tables to which it writes information to prevent those tables from being dropped while it is using them. When a large number of rows is being deleted, the DELETE statement acquires a large number of row locks. In this situation, lock escalation might occur, as row locks might be converted to a table lock. This request for table lock can cause the DELETE statement to hang, since the event monitor already has a lock on the table.
  To avoid this situation, consider setting a lock timeout before issuing the DELETE statement:
```
SET CURRENT LOCK TIMEOUT 60
```
  If increasing the lock timeout period does not resolve the problem, try deleting smaller subsets of the data, such as the records for smaller time periods (for example, 6 or 12 hours). This approach requires fewer locks, which will reduce the chance of lock escalation happening.
  You can also prune the relational tables produced by EVMON_FORMAT_UE_TO_TABLES as needed to balance storage requirements with the need to view historical data.
- If you are finished calculating CPU time, you can stop the collection of event monitor information, and drop the event monitor and its related tables by performing the following steps:
  1. Disable the collection of unit of work for this event monitor information using the SET EVENT MONITOR TRACKWORK STATE 0 command.
  2. Drop the event monitor itself using the DROP EVENT MONITOR statement.
  3. Drop the tables related to the event monitor using a DROP TABLE statement. In this case, there are four tables in total to drop:
    - TRACKWORK, the UE table used to collect information from the event monitor
    - UOW_EVENT
    - UOW_METRICS
    - UOW_PACKAGE_LIST
  4. Optional: If there are no remaining active event monitors, you might want to update the database configuration such that no unit of work event information is collected using the following command:
```
UPDATE DATABASE CONFIGURATION FOR dbname USING MON_UOW_DATA NONE
```

Variation: Collecting metrics for specific workloads

The previous example illustrates how you can capture unit of work metrics for all work done on the system. Setting the scope of data collected using the UPDATE DATABASE CONFIGURATION command might cause more information to be collected than you need. You might, for example, want to track only work done by specific workloads. In this case, rather than enable collection of unit of work information across the whole database as shown in step 2, you can specify the COLLECT UNIT OF WORK DATA clause with the CREATE or ALTER WORKLOAD statements. This clause causes only data for the workload specified to be collected by the event monitor. For example, to collect unit of work data for the workload named PAYROLL, use the following statement:

ALTER WORKLOAD PAYROLL COLLECT UNIT OF WORK DATA BASE

You can collect data for multiple workloads by running an ALTER WORKLOAD statement for each.

The remaining steps are the same, except for step 5, where you would change the query to resemble the one that follows:

SELECT E.WORKLOAD_NAME, 
       SUM(M.TOTAL_CPU_TIME) AS CPU_TIME 
       FROM UOW_EVENT E, UOW_METRICS M 
WHERE  M.APPLICATION_ID = E.APPLICATION_ID 
   AND M.UOW_ID = E.UOW_ID 
   AND M.MEMBER = E.MEMBER 
GROUP  BY E.WORKLOAD_NAME
ORDER  BY CPU_TIME DESC

The preceding statement reports the CPU time for each workload for which metrics collection is enabled:

WORKLOAD                       CPU_TIME
------------------------------ --------------------
PAYROLL                                  2143292042
MARKETING                                 492784916

  2 record(s) selected.