MON_GET_PKG_CACHE_STMT_DETAILS table function - Get package cache statement metrics as an XML document

Authorization

Default PUBLIC privilege

None

Syntax

The schema is SYSPROC.

Routine parameters

section_type

An optional input argument (either "D" or "S") of type CHAR(1) that specifies information type for the returned statement. If the argument is NULL or an empty string, information is returned for all SQL statements. Not case sensitive: D stands for dynamic; S for static.

executable_id

An optional input argument of type VARCHAR (32) for bit data that specifies a unique section of the database package cache. If a null value is specified, information is returned for all SQL statements. When the executable_id is specified, the section_type argument is ignored. For example, if an executable_id is specified for a dynamic statement, the dynamic statement details will be returned by this table function even if section_type is specified as static ("S").

search_args

An optional input parameter of type CLOB(1K), that allows you to specify one or more optional search argument strings. For example:

'<modified_within>5</modified_within><update_boundary_time>myPkgEvmon
     </update_boundary_time>'

The available search argument tags are as follows:

• '<modified_within>X</modified_within>'

  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.

• '<update_boundary_time>evmon_name</update_boundary_time>'

  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.

• '<stmt_details>true</stmt_details>' or '<stmt_details>false</stmt_details>'

  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.

member

An optional input argument of type INTEGER that specifies a valid member in the same instance as the currently connected database when calling this function. Specify -1 for the current database member, or -2 for all active database members. If the null value is specified, -1 is set.

Information returned

Usage notes

The 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.

Examples

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 ONLY

The following is an example of 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 DESC

The following is an example of output from this query.

NUM_EXEC_WITH_METRICS LOCK_WAITS           AVG_LOCK_WAIT_TIME       
--------------------- -------------------- --------------------
                    4                    2                  139
                    9                    3                   90