PKGCACHE procedure - Generate a summary report of package cache metrics
The Package Cache Summary report lists the top statements accumulated in the package cache as measured by various metrics.
Syntax
Parameters
- cache_interval
- An optional input argument of type INTEGER that specifies the report should only include data for package cache entries that have been updated in the past number of minutes specified by the cache_interval value. For example a cache_interval value of 60 produces a report based on package cache entries that have been updated in the past 60 minutes. Valid values are integers between 0 and 10080, which supports an interval of up to 7 days. If the argument is not specified (or if null is specified), the report includes data for package cache entries regardless of when they were added or updated.
- section_type
- An optional input argument of type CHAR(1) that specifies whether the report should include data for static SQL, dynamic SQL, or both. If the argument is not specified (or if null is specified), the report includes data for both types of SQL. Valid values are: d or D (for dynamic) and s or S (for static).
- member
- An optional input argument of type SMALLINT that determines whether to show data for a particular member or partition, or to show data summed across all members. If this argument is not specified (or if null is specified), the report shows values summed across all members. If a valid member number is specified, the report shows values for that member.
Authorization
The following privilege is
required:
- EXECUTE privilege on the MONREPORT module
The following examples demonstrate various ways to call
the PKGCACHE procedure. The first example produces a report based
on all statements in the package cache, with data aggregated across
all members:
call monreport.pkgcache;
The
next example produces a report based on both dynamic and static statements
in the package cache for which metrics have been updated within the
last 30 minutes, with data aggregated across all members:
call monreport.pkgcache(30);
The
next example produces a report based on all dynamic statements in
the package cache, with data aggregated across all members:
call monreport.pkgcache(DEFAULT, 'd');
The
next example produces a report based on both dynamic and static statements
in the package cache for which metrics have been updated within the
last 30 minutes, with data specific to a member number 4:
call db2monreport.pkgcache(30, DEFAULT, 4);