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. 注： mon_uow_data must also be set to BASE for package list information to be collected.
- Package ID (package_id -“程序包标识”监视元素)
- A unique ID that identifies a package.
- Nesting level (nesting_level -“嵌套级别”监视元素)
- 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 -“例程标识”监视元素)
- A unique routine identifier. It returns zero if the activity is not part of a routine.
- Invocation ID (invocation_id -“调用标识”监视元素)
- 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 -“程序包耗用时间”监视元素)
- 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 示例 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 逻辑数据组. 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 访问工作单元事件监视器捕获的事件数据.
-------------------------------------------------------
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出现在给定工作单元的程序包列表中的程序包数目（前一个报告中的“程序包列表大小”）会反映在 package_list_count 监视元素中，此监视元素与基本工作单元事件监视器数据包括在一起。如果与工作单元配合使用的程序包数目超过了在 mon_pkglist_sz 配置参数中所指定的值，那么不会将其他程序包包括在程序包列表中。但是，package_list_exceeded 监视元素指示程序包数目是否超过了程序包列表可以容纳的数目。此监视元素与工作单元事件监视器的基本信息（图 1 中的“Package List Exceeded”）一起返回。如果此监视元素的值为 YES，那么可以增大 mon_pkglist_sz 的值，以使程序包列表中包括更多程序包。
示例
下面的每个示例都显示了为程序包列表返回的信息，此信息与由 db2evmonfmt 工具显示的信息相同。- 示例 1：将执行单个程序包中的一个或多个节的应用程序
- 在此示例中，为此工作单元运行了一个程序包标识为 300 的程序包。
PACKAGE_ID NESTING_LEVEL ROUTINE_ID INVOCATION_ID ELAPSED_TIME ---------- ------------- ---------- ------------- ------------ 300 0 0 0 100
在此示例中，程序包列表中有一个条目，此条目反映执行程序包中的一个或多个节的情况。所执行的同一程序包中的所有节都被认为是同一程序包调用的一部分。
- 示例 2：应用程序将调用程序包中的存储过程
- 在此示例中，程序包标识为 300 的程序包调用标识为 806 的存储过程。在该存储过程中，将会执行三个节。
PACKAGE_ID NESTING_LEVEL ROUTINE_ID INOVATION_ID ELAPSED_TIME ---------- ------------- ---------- ------------ ------------ 300 0 0 0 21 300 1 806 1 100
此输出显示了列表中的两个条目。一个条目用于调用此存储过程，另一个条目用于执行此存储过程中的三个节。列表中的第二个条目的 NESTING_LEVEL 反映已从另一个程序包中调用了此存储过程这一事实。
- 示例 3：应用程序执行两个不同的程序包中的节
- 在此示例中，应用程序将执行一个程序包中的节，再执行另一个程序包中的节，然后返回到第一个程序包。未调用存储过程。下列伪码表示此工作单元：
还假定调用 PACKAGEA 需要 100 ms，调用 PACKAGEB 需要 25 ms，且调用 PACKAGEC 需要 460 ms。以下输出显示程序包列表将为如下所示：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
在此示例中，列表中有两个条目。PACKAGE_ID 为 300 的程序包 A 的各个节总共运行 560 ms。程序包 B 会运行了 25 ms。通过单行来表示程序包 A，因为每个调用都具有相同的 INVOCATION_ID 和 NESTING_LEVEL。INVOCATION_ID 和 NESTING_LEVEL 保持为 0，因为在任一程序包中都未调用存储过程。
- 示例 4：应用程序执行多个程序包中的节和存储过程
在此示例中，有三个程序包，程序包标识分别为 100、101 和 102。该应用程序位于程序包 100 中。有两个标识分别为 201 和 202 的存储过程。第一个存储过程 (SP1) 位于程序包 101 中，第二个存储过程 (SP2) 位于程序包 102 中。下列伪码表示此工作单元：
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)此工作单元的程序包列表将为如下所示：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
在前面的输出中有四个条目：- 第一个条目对应于调用第一个程序包中的 SP1，该伪码中的 a 行表示此工作单元。
- 第二个条目对应于执行程序包 101 中标识为 201 的存储过程中的节。这些节包括 b 、 c 和 d 行。嵌套级别增大为 1，如 1 所示。
- 第三个条目表示执行 SP2 中的第一个 INSERT INTO T2 语句（从 SP1 中调用存储过程）。嵌套级别将再次增大 ( 2 )。
- 列表中的第四个条目表示执行 SP2 中的第二个 INSERT INTO T2 语句。嵌套级别将保持不变，因为与上一次调用 SP2 一样，也是从 SP1 调用此存储过程。但是，因为这两个语句用于此存储过程的不同调用中，所以它们具有不同的调用标识 ( 3 )。因此，程序包列表中有两个不同的条目。