How to monitor static SQL Statements o DB2 9.7
MON_GET_PKG_CACHE_STMT
New relational interface to monitor dynamic and static SQL statements in package cache
DB2 Version 9.7 provides a new relational interface,
MON_GET_PKG_CACHE_STMT, to monitor dynamic and static SQL statements in
the database package cache. This new relational interface reports information for
both static and dynamic SQL statements, unlike the dynamic SQL snapshot which
only reports information for dynamic statements.
For each dynamic and static SQL statement, the new relational interface returns a
rich set of metrics, aggregated across executions of the statement. The metrics can
help you to quickly determine the reasons for poor performance of an SQL
statement, to compare the behavior and performance of one SQL statement to
another, and to easily identify the most expensive SQL statements along any
number of dimensions (for example, the SQL statements consuming the most CPU
resources, and statements with the longest lock wait times).
EXAMPLES:
List all the dynamic SQL statements from the database package cache ordered by the average CPU time.
db2 SELECT MEMBER,
SECTION_TYPE ,
TOTAL_CPU_TIME/NUM_EXEC_WITH_METRICS as
AVG_CPU_TIME,EXECUTABLE_ID
FROM TABLE(MON_GET_PKG_CACHE_STMT ( 'D', NULL, NULL, -2)) as T
WHERE T.NUM_EXEC_WITH_METRICS <> 0 ORDER BY AVG_CPU_TIME
The following is an example of output from this query.
MEMBER SECTION_TYPE AVG_CPU_TIME EXECUTABLE_ID
------ ------------ -------------------- -------------------------------------------------------------------
0 D 754 x'01000000000000007A0000000000000000000000020020081126171554951791'
0 D 2964 x'0100000000000000790000000000000000000000020020081126171533551120'
0 D 5664 x'01000000000000007C0000000000000000000000020020081126171720728997'
0 D 5723 x'01000000000000007B0000000000000000000000020020081126171657272914'
0 D 9762 x'01000000000000007D0000000000000000000000020020081126172409987719'
5 record(s) selected.
db2 SELECT STMT_TEXT FROM TABLE(MON_GET_PKG_CACHE_STMT)
(null, x'01000000000000007D0000000000000000000000020020081126172409987719', null, -2))
STMT_TEXT
-------------------------------------------------------------------------------------------
SELECT * FROM EMPLOYEE
SELECT count(*) FROM syscat.tablesall metrics are returned as 0 and the value of num_exec_with_metrics is also 0. Then Alex executes the same statement afterwards, but the metrics are collected this time for the execution of the statement and num_exec_with_metrics increments. So, after Brent and Alex execute that statement, the result of this query:
SELECT num_executions, num_exec_with_metrics, SUBSTR(stmt_text,1,50) AS stmt_textshows that the SELECT statement ran twice and one of the execution times had the activity metrics collected.
FROM TABLE (MON_GET_PKG_CACHE_STMT('d', null, null, -1)) AS tf
WHERE stmt_text LIKE 'SELECT count%'
NUM_EXECUTIONS NUM_EXEC_WITH_METRICS STMT_TEXT
-------------- --------------------- --------------------
2 1 SELECT count(*) FROM syscat.tables
1 record(s) selected.
Usage notes
The MON_GET_PKG_CACHE_STMT table function returns a point-in-time view of both static and dynamic SQL statements in the database package cache. This allows you to examine the aggregated metrics for a particular SQL statement, allowing you to quickly determine the reasons for poor query performance. The metrics returned are aggregates of the metrics gathered during each execution of the statement.