DB2 Version 9.7 for Linux, UNIX, and Windows

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.

Starting with DB2® Version 9.7 Fix Pack 1, you can have information about the packages used within units of work included in the data the event monitor collects. This information is written to the unformatted event table along with the rest of the information associated with the event when the unit of work ends.

There are two ways to control the capture of this information:

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 the active unit of work event monitors.
The PKGLIST option for the mon_uow_data configuration parameter can be set so that information about all units of work executed on the data server, including package list information is sent to active unit of work event monitors.

The following data is collected for the package listing:

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.

Package list information is stored in the UE table in a BLOB column. A list with 32 entries can be stored as an inline BLOB when the page size for the table space is 4k (the default). The number of entries that can be written to the package list 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, ensure that the UE table used to store the event monitor output is created in a table space with a larger page size. Assume that every increase of 32 in the size of the package list requires an increase of 4k in the page size of the table space. So, for example, if you want to have up to 64 entries in the package list, ensure that the page size for the table space is at least 8k. If you increase mon_pkglist_sz without increasing the page size of the table space, the package list is still created, however the BLOB is not stored inline in the table, which might affect performance.

Note: You can use the ADMIN_IS_INLINED administrative function to determine whether the BLOB that contains the package list information is stored inline.

Package listing output

As stated earlier, when collecting package information, the unit of work event monitor writes two records to the UE table. Each of the interfaces for displaying the data in a UE table provides a mechanism for viewing the information contained in the two records together. 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 two relational tables that you can join; EVMON_FORMAT_UE_TO_XML produces a single XML document that contains the information from both records. For more information, see Accessing event data captured by a unit of work event monitor.

Note: In a partitioned database environment, the package list is only reported in the unit of work event generated by the coordinator agent and reflects the time spent in each package by that agent specifically; it does not reflect time spent in those packages by any other agent at any other partition.

Figure 1 shows the information produced by the unit of work event monitor, as formatted by the db2evmonfmt tool.

Figure 1. Sample output from the unit of work event monitor, with package listing information

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

Note: Some of the metrics in the "UOW Metrics" section 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:

Application
    EXEC PACKAGEA
    EXEC PACKAGEB
    EXEC PACKAGEA

Assume also that each of these invocations require 100 ms, 25 ms, and 460 ms, respectively. The following output shows what the package listing would look like:

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