DB2 Version 9.7 for Linux, UNIX, and Windows

Monitoring package cache events

The package cache event monitor captures data related to statement entries that have been flushed from the database package cache. This event monitor provides the history of the contents of the package cache which can help with SQL query performance and problem determination issues.

Overview

The package cache event monitor collects the same information as the MON_GET_PKG_CACHE_STMT table function, including the full set of available activity metrics and the executable section information of an entry.

Two control mechanisms, on the CREATE EVENT MONITOR statement, help limit the volume of data that can be captured. The two control mechanisms provide the following capabilities:

Filter entries with the WHERE clause based on one or more of the following:
- Whether the last update of the metrics for an entry occurs after a specific time before it is evicted (UPDATED_SINCE_BOUNDARY_TIME). An entry will only be collected if the time that the metrics were last updated is more recent than boundary time defined for the event monitor. The boundary time for an event monitor can be set using the MON_GET_PKG_CACHE_STMT table function. If no boundary time has been set for the event monitor, the UPDATED_SINCE_BOUNDARY_TIME clause will have no effect.
- The number of times the section of an entry was executed (NUM_EXECUTIONS)
- The total aggregated amount of time spent executing the statement (STMT_EXEC_TIME)
COLLECT DATA clause options:
- COLLECT BASE DATA
  Same information collected as the MON_GET_PKG_CACHE_STMT table function, as well as the full set of available activity metrics
- COLLECT DETAILED DATA
  Collects the same information gathered with the COLLECT BASE DATA clause and includes the executable section of the entry

When you need to investigate the individual execution of an SQL statement, you can use the MON_GET_PKG_CACHE_STMT table function (if the entries are still in the package cache) to compare the behavior of a cached entry relative to others. The execution metrics, compilation environment, and detailed descriptions for a cached entry are available for diagnostic purposes.

If an entry has already been flushed from the package cache, you can use the package cache event monitor to review the history of the cached entries which were flushed from the package cache. The history data contains the same information that the MON_GET_PKG_CACHE_STMT table function provides. In addition, the event monitor also provides the executable section of the statement. All of this applies to both dynamic and static SQL statements.

Creating a package cache event monitor

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

The package cache event monitor only has the unformatted event table as the output option.

Before you create a package cache event monitor, identify the table space where you plan to store the unformatted event table for your event monitor. The recommended practice is to have a table space dedicated and configured to store the unformatted event table associated with any event monitor. Create the package cache event monitor in a tablespace with at least 8K pagesize to ensure that the event data is contained within the inlined BLOB column of the unformatted event table. If the BLOB column is not inlined, then the performance of writing and reading the events to the unformatted event table might not be efficient.

The database manager attempts to inline the event_data BLOB column in the unformatted event table, but this is not always possible. To check that the rows in the unformatted event table have been inlined, use the ADMIN_IS_INLINED function. If the rows have not been inlined, use the ADMIN_EST_INLINE_LENGTH functions to determine how much space the rows need.

Your other options, when you create an event monitor, are to specify any existing table space or to not specify any and have one chosen by default.

To setup a package cache event monitor using defaults and best practices, complete the following steps:

Create the event monitor by issuing the CREATE EVENT MONITOR statement. The following example uses defaults where possible and specifies to store the unformatted event table in an existing table space MY_EVMON_TABLESPACE:
```
CREATE EVENT MONITOR MY_PKGCACHE_EVMON
  FOR PACKAGE CACHE
   WRITE TO UNFORMATTED EVENT TABLE (IN MY_EVMON_TABLESPACE)
```

Enabling data collection

To enable data collection, you must activate the event monitor using the SET EVENT MONITOR STATE statement. The package cache event monitor is not passive; following activation, it automatically starts collecting data whenever a statement is flushed from the package cache and meets the filter criteria set at the time of creation of the package cache event monitor.

Accessing event data captured by a package cache event monitor

This type of event monitor writes data in a binary format to an unformatted event table. You can access this data using the following table functions:

EVMON_FORMAT_UE_TO_XML - extracts data from an unformatted event table into an XML document.
EVMON_FORMAT_UE_TO_TABLES - extracts data from an unformatted event table into a set of relational tables.

The schema file ~/sqllib/misc/DB2EvmonPkgCache.xsd is used to document the expected output of the package cache event monitor report in an XML document. The schema file will reference a common monitor schema file (DB2MonCommon.xsd) to avoid duplicating the common contents.

An XML stylesheet is provided in ~/sqllib/samples/jdbc/DB2EvmonPkgCache.xsl.

Use these table functions to specify the data to extract using a SELECT statement. You have full control over selection, ordering, and other aspects provided by the SELECT statement.

You can also use the db2evmonfmt command to perform the following tasks:

Select events of interest based on the following attributes: executable ID, section type, query cost estimate, statement package cache ID, and flush time.
Choose whether to receive the output in the form of a text report or a formatted XML document.
Control the output format by creating your own XSLT style sheets instead of using the ones provided by the db2evmonfmt command.

For example, the following command provides a package cache report that:

Selects package cache events that have occurred in the past 24 hours in the database SAMPLE. These event records are obtained from the unformatted event table called SAMPLE_PKGCACHE_EVENTS.
Provides formatted text output using the DB2EvmonPkgCache.xsl style sheet.

java db2evmonfmt -d SAMPLE -ue SAMPLE_PKGCACHE_EVENTS -ftext -ss DB2EvmonPkgCache.xsl -hours 24