Unit of work event monitor package listing information
The unit of work event monitor can collect a listing of packages used within a unit of work. This information can be used to determine which stored procedures within an application might be taking more time than expected to run.
You can have information about the packages used within units of work included in the data the event monitor collects. Depending on the output option you choose for the unit of work event monitor, this information is written to the unformatted event table or to the UOW_PACKAGE_LIST_evmon-name table (where evmon-name is the name assigned to the event monitor) when the unit of work ends along with the rest of the information associated with the event.
- The PACKAGE LIST option for the COLLECT UNIT OF WORK DATA clause of the CREATE or ALTER WORKLOAD statements controls the collection of this information for specific workloads. If this option is specified, information for the units of work that are executed under the workload identified in the CREATE or ALTER WORKLOAD statements, including package list information, is sent to any active unit of work event monitors.
- The mon_uow_pkglist configuration parameter can be set to
ON so that package list information for all units of work executed
on the data server is sent to any active unit of work event monitors. Note: mon_uow_data must also be set to BASE for package list information to be collected.
- Package ID (package_id - Package identifier monitor element)
- A unique ID that identifies a package.
- Nesting level (nesting_level - Nesting level monitor element)
- The level of nesting or recursion in effect when the statement was being run. Each level of nesting corresponds to nested or recursive invocation of a stored procedure or user-defined function (UDF).
- Routine ID (routine_id - Routine ID monitor element)
- A unique routine identifier. It returns zero if the activity is not part of a routine.
- Invocation ID (invocation_id - Invocation ID monitor element)
- An identifier that distinguishes one invocation of a routine from others at the same nesting level within a unit of work. It is unique within a unit of work for a specific nesting level.
- Package elapsed time (package_elapsed_time - Package elapsed time monitor element)
- The elapsed time spent executing sections within the package.
As the list of information collected for the package listing suggests, information is captured not only for each package but also for each invocation of a routine within a package.
Elapsed time is also tracked. The time calculated for a given invocation starts from the first execution of a section within a package until the database manager switches to another package. See Examples to see more about how elapsed time is tracked.
How package lists are written to unformatted event tables
When you enable the collection of package list information, the unit of work event monitor writes two records to the unformatted event (UE) table for each unit of work. The first record contains the basic unit of work event monitor data. The next record contains the package listing information.
How package lists are written to regular tables
When you use regular tables for event monitor output, package list information is captured as part of the uow_package_list logical data group. As each unit of work completes, one or more rows are added to the UOW_PACKAGE_LIST_evmon-name table, with one column for each monitor element in the logical data group. The number of rows added to the table depends on how many packages ran as part of the unit of work. However, the upper limit to the number of rows that can be added to this table is controlled by the mon_pkglist_sz configuration parameter. The default for this parameter is 32, which means that up to 32 entries can be included in the package listing. If you want to increase the number of entries that can be included in the package list, increase mon_pkglist_sz .
Package listing output
As stated earlier, when the event monitor writes to a UE table, the unit of work event monitor writes two records to the UE table when collecting package information. Each of the interfaces for displaying the data in a UE table provides a mechanism for viewing the information contained in the two UE table records. For example, the db2evmonfmt tool combines the information in each record into a single report. If you use the EVMON_FORMAT_UE_TO_TABLES procedure, it produces relational tables that you can join; the table UOW_PACKAGE_LIST contains the package list information. EVMON_FORMAT_UE_TO_XML produces a single XML document that contains the information from both records. For more information, see Accessing event data that is captured by a unit of work event monitor.
-------------------------------------------------------
Event ID : 12
Event Type : UOW
Event Timestamp : 2009-12-08-14.44.39.162707
Member : 0
Release : 9070200
-------------------------------------------------------
Database Level Details
----------------------
Database Member Activation Time : 2009-12-08-14.41.55.089416
Coordinator Member : 0
Connection Level Details
------------------------
Application ID : *LOCAL.gstager.091208194155
Application Handle : 21
Application Name : db2bp
Session Authorization ID :
System Authorization ID :
Connection Timestamp : 2009-12-08-14.41.55.089416
Client Process ID : 13043
Client Platform : LINUXX8664
Client Product ID : SQL09072
Client Protocol : LOCAL
Client Hostname : HOSTX
Client Port Number : 0
UOW Level Details
------------------------
Start Time : 2009-12-08-14.44.39.160651
Stop Time : 2009-12-08-14.44.39.162707
Completion Status : COMMIT
UOW ID : 12
Workoad Occurrence ID : 1
Workload Name : SYSDEFAULTUSERWORKLOAD
Workoad ID : 1
Service Superclass Name : SYSDEFAULTUSERCLASS
Service Subclass Name : SYSDEFAULTSUBCLASS
Service Class ID : 13
Client Userid :
Client Workstation Name :
Client Application Name :
Client Accounting String :
Local Transaction ID : 000000000000013B
Global Transaction ID : 0000000000000000000000000000000000000000
Log Space Used : 124
UOW Metrics
------------------------
TOTAL_CPU_TIME : 1591
TOTAL_WAIT_TIME : 8363
ACT_ABORTED_TOTAL : 0
ACT_COMPLETED_TOTAL : 1
ACT_REJECTED_TOTAL: : 0
AGENT_WAIT_TIME : 87
AGENT_WAITS_TOTAL : 1
APP_RQSTS_COMPLETED_TOTAL : 1
.
.
.
Package List
------------------------
Package List Size : 2
Package List Exceeded : no
PACKAGE_ID NESTING_LEVEL ROUTINE_ID INVOCATION_ID PACKAGE_ELAPSED_TIME
-------------------- ------------- ----------- -------------------- --------------------
240 0 0 0 0
330 1 66539 1 1UOW Metricssection have been excluded.
The number of packages that appear in the package list
for a given unit of work is reflected in the package_list_count monitor element (Package List Size
in the preceding report),
which is included with the base unit of work event monitor data. If
the number of packages used with the unit of work exceeds the value
specified in the mon_pkglist_sz configuration
parameter, the additional packages are not included in the package
listing. However, the package_list_exceeded monitor
element indicates whether there were more packages than would fit
into the package list. This monitor element is returned along with
the base information for the unit of work event monitor (Package
List Exceeded
in Figure 1). If the
value for this monitor element is YES, you can increase
the value for mon_pkglist_sz to have a larger
number of packages included in the package list.
Examples
Each of the examples that follow show the information returned for the package listing as it would be displayed by the db2evmonfmt tool.-
Example 1: An application that executes one or more sections
in a single package
- In this example, one package with a package ID of 300 was run for this unit of work.
PACKAGE_ID NESTING_LEVEL ROUTINE_ID INVOCATION_ID ELAPSED_TIME ---------- ------------- ---------- ------------- ------------ 300 0 0 0 100
In this case, there is one entry on the package list, which reflects the execution of one or more sections in the package. All sections executed from the same package are considered to be part of the same package invocation.
Example 2: An application calls a stored procedure in a package
- In this example, the package with a package ID of 300 calls a stored procedure with an ID of 806. Three sections are executed within the stored procedure.
PACKAGE_ID NESTING_LEVEL ROUTINE_ID INOVATION_ID ELAPSED_TIME ---------- ------------- ---------- ------------ ------------ 300 0 0 0 21 300 1 806 1 100
This output shows two entries in the list. One entry is for the call to the stored procedure, and one for the execution of the three sections within the stored procedure. The NESTING_LEVEL for the second entry in the list reflects the fact that the stored procedure was called from another package.
Example 3: An application executes sections in two different
packages
- In this example, an application executes sections from one package, then another package, and then back to the first package. No stored procedures are called. The pseudocode that follows is a representation of this unit of work:
Assume also that the invocation of PACKAGEA requires 100 ms, the invocation of PACKAGEB requires 25 ms, and that the invocation of PACKAGEC requires 460 ms. The following output shows what the package listing would look like:Application EXEC PACKAGEA EXEC PACKAGEB EXEC PACKAGEAPACKAGE_ID NESTING_LEVEL ROUTINE_ID INVOCATION_ID ELAPSED_TIME ---------- ------------- ---------- ------------- ------------ 300 0 0 0 560 301 0 0 0 25
In this case, there are two entries in the list. Package A, with PACKAGE_ID 300 had sections that ran for 560 ms in total. Package B ran for 25 ms. Package A is represented by a single line because each invocation has the same INVOCATION_ID and NESTING_LEVEL. INVOCATION_ID and NESTING_LEVEL remain at 0, because no stored procedures were called in either package.
Example 4: An application executes sections and stored procedures
in multiple packages
In this example, there are 3 packages with IDs 100, 101, and 102. The application is in package 100. There are two stored procedures with IDs 201 and 202. The first stored procedure (SP1) is in package 101, and the second (SP2) is in package 102. The pseudocode that follows is a representation of this unit of work:
Application CALL SP1 a INSERT INTO T1 VALUES(7) b CALL SP2 c INSERT INTO T2 VALUES(8) CALL SP2 d INSERT INTO T2 VALUES(8)The package listing for this unit of work would be as follows:PACKAGE_ID NESTING_LEVEL ROUTINE_ID INVOCATION_ID ELAPSED_TIME ---------- ------------- ---------- ------------- ------------ 100 0 0 0 21 101 1 1 201 1 40 102 2 2 202 1 3 35 102 2 202 2 3 35
In the preceding output, there are four entries:- The first corresponds to the execution of the call to SP1 within the first package, line a in the pseudocode that represents the unit of work.
- The second corresponds to the execution of the sections within the stored procedure with ID 201 in package 101. These sections include lines b , c , and d . The nesting level increases to 1, as shown by 1 .
- The third entry represents the execution of the first of the INSERT INTO T2 statements in SP2, as called from SP1. The nesting level increases again ( 2 ).
- The fourth entry in the list represents the execution of the second of the INSERT INTO T2 statements in SP2. The nesting level remains the same, because like the previous call to SP2, this stored procedure is called from SP1. However, because these two statements occur within separate invocations of the stored procedure, they each have separate invocation IDs ( 3 ). Thus, there are two separate entries in the package listing.