DB2 Version 9.7 for Linux, UNIX, and Windows

Collecting package cache event data and generating reports

You can use the package cache event monitor to collect data about statement entries that were flushed from the database package cache. After the package cache event data has been collected in an unformatted event table, follow the directions in this task to obtain a text report.

About this task

The package cache event monitor collects relevant history information about what was in the package cache to help with query performance and problem determination issues related to SQL statements. For example, some of the information the package cache event monitor collects from the database package cache is as follows:

Executable ID (EXECUTABLE_ID)
The estimated cost of the query (QUERY_COST_ESTIMATE)
The time that the entry was flushed from the package cache (Event Timestamp)

This task provides instructions for collecting package cache event data.

Restrictions

Input data values are not viewable if you do not have DBADM or SQLADM authority.

Before you begin

To collect package cache event monitor data, you must have DBADM or SQLADM authority.

Procedure

To collect detailed information regarding package cache events, perform the following steps:

Create a package cache event monitor called cachestmtevmon by using the CREATE EVENT MONITOR FOR PACKAGE CACHE statement, as shown in the following example:
```
CREATE EVENT MONITOR cachestmtevmon FOR PACKAGE CACHE
   WRITE TO UNFORMATTED EVENT TABLE
```
Activate the package cache event monitor called cachestmtevmon by running the following statement:
```
SET EVENT MONITOR cachestmtevmon STATE 1
```
Unlike the locking and the unit of work event monitors, the package cache event monitor automatically starts collecting data after the event monitor is activated.
Connect to the database.
Run the application, workload or SQL statements for which you want to collect event monitor information.
If you want to turn OFF package cache data collection, deactivate the event monitor by running the following command:
```
SET EVENT MONITOR cachestmtevmon STATE 0
```
Obtain the package cache event report using the XML parser tool, db2evmonfmt, to produce a flat-text report based on the event data collected in the unformatted event table, for example:
```
java db2evmonfmt -d db_name -ue table_name -ftext -u user_id -p password
```
Analyze the report.

Example

The following is an example of a report obtained by using the db2evmonfmt Java™-based report tool to convert data in the unformatted event table collected by the package cache event monitor:

-------------------------------------------------------
Event ID               : 1
Event Type             : PKGCACHEBASE
Event Timestamp        : 2009-11-06-12.32.06.442020
Member                 : 0
Release                : 9070100
-------------------------------------------------------

Package Cache Details
---------------------
Section Type                           : D
Insert Timestamp                       : 2009-11-06-12.28.04.246930
Executable ID                          : 0100000000000000010000000000000000000000020020091106122804246932
Package Schema                         : 
Package Name                           : 
Package Version ID                     : 
Section Number                         : 
Effective Isolation                    : CS
Number Of Executions                   : 1
Number Of Executions With Metrics      : 1
Prep Time                              : 9
Last Metrics Update                    : 2009-11-06-12.28.07.905942
Executions By Coordinator              : 1
Executions By Coordinator With Metrics : 1
Statement Type                         : DDL, (not Set Constraints)
Query Cost Estimate                    : 1
Statement Package Cache ID             : 1151051235329
Statement Text                         : create event monitor cachestmtevmon for package cache write to unformatted event table
Compilation Environment                : 47454E5F434D504C010000000E000000800100000000000000000000000000000100000004000000000100000
                                         0000000020000000400000008010000000000000300000008000000100100000000B0FA040000000100000018
                                         010000B804B0FA050000000100000020010000B8040000060000000100000028010000B804000007000000010
                                         0000030010000B8040000080000000100000038010000B8040000090000000500000040010000B80400000A00
                                         00000800000048010000B80400000B0000000B00000050010000B8040000100000000A00000060010000B8040
                                         000130000000400000070010000000000000F0000000400000078010000000000000100000000000000050000
                                         00000000000000000000000000300000000000000030000000000000004E000000000000004E0000000000000
                                         04E0000000000000031202020200000002020444444444444000000000000000000000C000000000020091106
                                         12280400000000000000000000000000000000000100000000000000
Section Environment                    : 

Metrics
-------------------
WLM_QUEUE_TIME_TOTAL                : 0
WLM_QUEUE_ASSIGNMENTS_TOTAL         : 0
FCM_TQ_RECV_WAIT_TIME               : 0
FCM_MESSAGE_RECV_WAIT_TIME          : 0
FCM_TQ_SEND_WAIT_TIME               : 0
FCM_MESSAGE_SEND_WAIT_TIME          : 0
LOCK_WAIT_TIME                      : 0
LOCK_WAITS                          : 0
DIRECT_READ_TIME                    : 0
DIRECT_READ_REQS                    : 0
DIRECT_WRITE_TIME                   : 3
DIRECT_WRITE_REQS                   : 1
LOG_BUFFER_WAIT_TIME                : 0
NUM_LOG_BUFFER_FULL                 : 0
LOG_DISK_WAIT_TIME                  : 0
LOG_DISK_WAITS_TOTAL                : 0
POOL_WRITE_TIME                     : 0
POOL_READ_TIME                      : 33
AUDIT_FILE_WRITE_WAIT_TIME          : 0
AUDIT_FILE_WRITES_TOTAL             : 0
AUDIT_SUBSYSTEM_WAIT_TIME           : 0
AUDIT_SUBSYSTEM_WAITS_TOTAL         : 0
DIAGLOG_WRITE_WAIT_TIME             : 0
DIAGLOG_WRITES_TOTAL                : 0
FCM_SEND_WAIT_TIME                  : 0
FCM_RECV_WAIT_TIME                  : 0
TOTAL_ACT_WAIT_TIME                 : 36
TOTAL_SECTION_SORT_PROC_TIME        : 0
TOTAL_SECTION_SORTS                 : 0
TOTAL_SECTION_SORT_TIME             : 0
TOTAL_ACT_TIME                      : 37
TOTAL_ROUTINE_TIME                  : 0
STMT_EXEC_TIME                      : 3658
COORD_STMT_EXEC_TIME                : 3658
TOTAL_ROUTINE_NON_SECTION_PROC_TIME : 0
TOTAL_ROUTINE_NON_SECTION_TIME      : 0
TOTAL_SECTION_PROC_TIME             : 1
TOTAL_SECTION_TIME                  : 37
TOTAL_ROUTINE_USER_CODE_PROC_TIME   : 0
TOTAL_ROUTINE_USER_CODE_TIME        : 0
ROWS_READ                           : 19
ROWS_MODIFIED                       : 3
POOL_DATA_L_READS                   : 42
POOL_INDEX_L_READS                  : 83
POOL_TEMP_DATA_L_READS              : 0
POOL_TEMP_INDEX_L_READS             : 0
POOL_XDA_L_READS                    : 0
POOL_TEMP_XDA_L_READS               : 0
TOTAL_CPU_TIME                      : 2243
POOL_DATA_P_READS                   : 13
POOL_TEMP_DATA_P_READS              : 0
POOL_XDA_P_READS                    : 0
POOL_TEMP_XDA_P_READS               : 0
POOL_INDEX_P_READS                  : 33
POOL_TEMP_INDEX_P_READS             : 0
POOL_DATA_WRITES                    : 0
POOL_XDA_WRITES                     : 0
POOL_INDEX_WRITES                   : 0
DIRECT_READS                        : 0
DIRECT_WRITES                       : 2
ROWS_RETURNED                       : 0
DEADLOCKS                           : 0
LOCK_TIMEOUTS                       : 0
LOCK_ESCALS                         : 0
FCM_SENDS_TOTAL                     : 0
FCM_RECVS_TOTAL                     : 0
FCM_SEND_VOLUME                     : 0
FCM_RECV_VOLUME                     : 0
FCM_MESSAGE_SENDS_TOTAL             : 0
FCM_MESSAGE_RECVS_TOTAL             : 0
FCM_MESSAGE_SEND_VOLUME             : 0
FCM_MESSAGE_RECV_VOLUME             : 0
FCM_TQ_SENDS_TOTAL                  : 0
FCM_TQ_RECVS_TOTAL                  : 0
FCM_TQ_SEND_VOLUME                  : 0
FCM_TQ_RECV_VOLUME                  : 0
TQ_TOT_SEND_SPILLS                  : 0
POST_THRESHOLD_SORTS                : 0
POST_SHRTHRESHOLD_SORTS             : 0
SORT_OVERFLOWS                      : 0
AUDIT_EVENTS_TOTAL                  : 0
TOTAL_SORTS                         : 0
THRESH_VIOLATIONS                   : 0
NUM_LW_THRESH_EXCEEDED              : 0
TOTAL_ROUTINE_INVOCATIONS           : 0