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.

Note: It takes a longer time period to build the compilation environment and to transfer statement text (which can be as large as 2 MB) between members. To improve performance when retrieving a list of all the statements from the package cache, do not to select the STMT_TEXT and the COMP_ENV_DESC columns.

With the above output, we can use the executable_id to find out the details about the most expensive statement (in terms of the average CPU time):

db2 SELECT STMT_TEXT FROM TABLE(MON_GET_PKG_CACHE_STMT)
      (null, x'01000000000000007D0000000000000000000000020020081126172409987719', null, -2))

STMT_TEXT
-------------------------------------------------------------------------------------------
SELECT * FROM EMPLOYEE

As another example, assume a user named Alex has a connection associated to workload A which has the COLLECT ACTIVITY METRICS set. Another user, Brent, is associated to workload B that has the COLLECT ACTIVITY METRICS set to NONE. In addition, the database mon_act_metrics configuration parameter is set to NONE. When Brent executes the query:

SELECT count(*) FROM syscat.tables

all 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_text 
    FROM TABLE (MON_GET_PKG_CACHE_STMT('d', null, null, -1)) AS tf 
    WHERE stmt_text LIKE 'SELECT count%'

shows that the SELECT statement ran twice and one of the execution times had the activity metrics collected.

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.