DB2 Version 9.7 for Linux, UNIX, and Windows
DB2 Version 9.7 for Linux, UNIX, and Windows
The MON_GET_PKG_CACHE_STMT_DETAILS table function returns detailed metrics for one or more package cache entries.
The metrics returned by the MON_GET_PKG_CACHE_STMT_DETAILS table function represent the accumulation of all metrics for statements in the package cache. Statement metrics are rolled up to the package cache upon activity completion.
>>-MON_GET_PKG_CACHE_STMT_DETAILS--(--section_type--,-----------> >--executable_id--,--search_args--,--member--)-----------------><
The schema is SYSPROC.
'<modified_within>5</modified_within><update_boundary_time>myPkgEvmon
</update_boundary_time>'Returns only those statement entries that have either been inserted into the cache or executed within the last X minutes (where X is a positive integer value). If the argument is not specified, all entries in the cache are returned.
Updates the event monitor boundary timestamp to the current time for the package cache event monitor specified by evmon_name. If this event monitor specifies where updated_since_boundary_time as an output criteria in its WHERE clause, only package cache entries that subsequently have their metrics updated are captured when evicted from the package cache. This operation only has an effect if the specified package cache event monitor is active when the command is issued.
Includes or excludes the stmt_text and comp_env_desc data in the resulting XML document. This allows you to exclude these relatively large portions of the document when you do not need them (for example, if you are using the XML document to provide input for the MON_FORMAT_XML_* table functions that return formatted row-based output). If this argument tag is not specified, the stmt_text and comp_env_desc data are included by default.
Each input argument can be specified only once. The search argument tags must be specified in lowercase.
EXECUTE privilege on the MON_GET_PKG_CACHE_STMT_DETAILS function.
The first example demonstrates how to examine the package cache and select the 10 statements that have read and returned the largest number of rows. Additionally, the results show the cumulative amount of time spent executing each of these statements (in the STMT_EXEC_TIME output column).
SELECT SUBSTR(DETMETRICS.STMT_TEXT, 1, 40) STMT_TEXT,
DETMETRICS.ROWS_RETURNED,
DETMETRICS.STMT_EXEC_TIME
FROM TABLE(MON_GET_PKG_CACHE_STMT_DETAILS(CAST(NULL AS CHAR(1)),
CAST(NULL AS VARCHAR(32) FOR BIT DATA),
CAST(NULL AS CLOB(1K)), -1)) AS STMT_METRICS,
XMLTABLE (XMLNAMESPACES( DEFAULT 'http://www.ibm.com/xmlns/prod/db2/mon'),
'$DETMETRICS/db2_pkg_cache_stmt_details' PASSING
XMLPARSE(DOCUMENT STMT_METRICS.DETAILS) as "DETMETRICS"
COLUMNS "STMT_TEXT" CLOB PATH 'stmt_text',
"ROWS_RETURNED" BIGINT PATH 'activity_metrics/rows_returned',
"STMT_EXEC_TIME" BIGINT PATH 'activity_metrics/stmt_exec_time'
) AS DETMETRICS
ORDER BY rows_returned DESC
FETCH FIRST 10 ROWS ONLYThe following example is a sample output from this query.
STMT_TEXT ROWS_RETURNED STMT_EXEC_TIME
---------------------------------------- ------------- --------------
SELECT CREATOR, NAME, CTIME FROM SYSIBM. 134 38
SELECT SUBSTR(DETMETRICS.STMT_TEXT, 1, 4 44 336
SELECT SUBSTR(DETMETRICS.STMT_TEXT, 1, 4 10 333
SELECT COLNAME, TYPENAME FROM SYSCAT.CO 10 6
SELECT SUBSTR(DETMETRICS.STMT_TEXT, 1, 4 10 334
SELECT TRIGNAME FROM SYSCAT.TRIGGERS WH 8 1
SELECT COUNT(*) FROM SYSCAT.TABLESPACES 2 0
SELECT POLICY FROM SYSTOOLS.POLICY WHERE 1 0
CALL SYSPROC.POLICY_INSTALL ('I','DB2Tab 1 62
CALL SYSPROC.POLICY_INSTALL ('I','DB2Tab 1 64
10 record(s) selected.The second example shows, for dynamic SQL statements that have waited on a lock while executing, the number of executions, number of lock waits and average time spent per lock wait. The output shows values accumulated over the lifetime of the package cache entries, but restricts information to statements that have executed within the last minute (by setting the modified_within argument tag to 1). The query excludes the statement details (stmt_text and comp_env_desc data) because they are not required and they are computationally expensive to report (by setting the stmt_details argument tag to false).
SELECT NUM_EXEC_WITH_METRICS, LOCK_WAITS,
(LOCK_WAIT_TIME / LOCK_WAITS) AVG_LOCK_WAIT_TIME
FROM TABLE(MON_GET_PKG_CACHE_STMT_DETAILS('D', CAST(NULL
AS VARCHAR(32) FOR BIT DATA),
CLOB(
'<modified_within>1</modified_within><stmt_details>false</stmt_details>')
, -1))
AS STMT_METRICS,
XMLTABLE (XMLNAMESPACES( DEFAULT 'http://www.ibm.com/xmlns/prod/db2/mon'),
'$DETMETRICS/db2_pkg_cache_stmt_details' PASSING
XMLPARSE(DOCUMENT STMT_METRICS.DETAILS) as "DETMETRICS"
COLUMNS "NUM_EXEC_WITH_METRICS" BIGINT PATH 'num_exec_with_metrics',
"LOCK_WAITS" BIGINT PATH 'lock_waits',
"LOCK_WAIT_TIME" BIGINT PATH 'activity_metrics/lock_wait_time'
) AS DETMETRICS
WHERE LOCK_WAITS <> 0
ORDER BY AVG_LOCK_WAIT_TIME DESCThe following example is a sample output from this query.
NUM_EXEC_WITH_METRICS LOCK_WAITS AVG_LOCK_WAIT_TIME
--------------------- -------------------- --------------------
4 2 139
9 3 90The metrics returned by this function represent the accumulation of all metrics for statements in the package cache. Statement metrics are rolled up to the package cache upon activity completion.
The schema for the XML document that is returned in the DETAILS column is available in the file sqllib/misc/DB2MonRoutines.xsd. Further details can be found in the file sqllib/misc/DB2MonCommon.xsd.
| Column Name | Data Type | Description or corresponding monitor element |
|---|---|---|
| MEMBER | SMALLINT | member- Database member |
| SECTION_TYPE | CHAR(1) | section_type - Section type indicator. |
| EXECUTABLE_ID | VARCHAR(32) FOR BIT DATA | executable_id - Executable ID. |
| DETAILS | BLOB(8M) | XML document containing detailed metrics for the unit of work. See Table 2 for a description of the elements in this document. |