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.
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.
Before you begin
- You must have a table space in which to store event monitor output
before you attempt to create the event monitor. A table space with
a page size of at least 8k to store the unformatted event (UE) table
produced by the event monitor is recommended. However, even with an
8k page size, the BLOB column used for storing the unformatted event
data might not be stored inline within the table. If you want improved
performance, consider using a table space with a large page size,
such as 32k.
- For partitioned database environments, the table space must extend
across all partitions.
- Unless a table space is explicitly named in the CREATE EVENT MONITOR
command, the default table space for the database is used.
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:
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:
- Disable the collection of unit of work for this event monitor
information using the SET EVENT MONITOR TRACKWORK STATE 0 command.
- Drop the event monitor itself using the DROP EVENT MONITOR statement.
- 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
- 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 COLLNECT 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.