Capturing performance information for dynamic SQL statements

Db2 maintains statement caching performance statistics records when dynamic statements are cached. The statistics include cache hit ratio and other useful data points that you can use to evaluate the overall performance of your statement caches and statement executions.

Before you begin

  • Set the value of the CACHEDYN subsystem parameter to YES.
  • Create DSN_STATEMENT_CACHE_TABLE, and the associated LOB and auxiliary tables and indexes. You can find the sample statements for creating these objects in member DSNTESC of the SDSNSAMP library.

About this task

When Db2 prepares a dynamic SQL statement, it creates control structures that are used when the statements are executed. When dynamic statement caching is in effect, Db2 stores the control structure associated with each prepared dynamic SQL statement in a storage pool. If that same statement or a matching statement is issued again, Db2 can use the cached control structure, avoiding the expense of preparing the statement again.

Tip: Enhanced query tuning capabilities that can help you with this task are available in IBM® Db2 Query Workload Tuner for z/OS® and IBM Db2 Administration Foundation for z/OS.

Procedure

To externalize the statement cache statistics for performance analysis:

  1. To externalize the statement cache statistics for performance analysis: 
    START TRACE(P) CLASS(30) IFCID(316,317,318)
    IFCID 0316 contains the first 60 bytes of SQL text and statement execution statistics. IFCID 0317 captures the full text of the SQL statement. IFCID 318 enables the collection of statistics. Db2 begins to collect statistics and accumulates them for the length of time when the trace is on. Stopping the trace resets all statistics.
  2. Run the SQL workload that you want to analyze.
  3. Issue the following SQL statement in a DSNTEP2 utility job: 
    EXPLAIN STMTCACHE ALL
    Important: Run the workload and issue the EXPLAIN statement while the traces are still running. If you stop the trace for IFCID 318, all statistics in the dynamic statement cache are reset.
    Db2 extracts all statements from the global cache and writes the statistics information to for all statements in the cache that qualify based on the user's SQLID into the DSN_STATEMENT_CACHE_TABLE. If the SQLID has SYSADM authority, statistics for all statement in the cache are written into the table.
  4. Begin your evaluation of the statement cache performance by selecting from the inserted rows from the DSN_STATEMENT_CACHE_TABLE table.
    For example, you can use the following clauses in your query to identify the n queries that have the highest total accumulated CPU time for all the executions of the query during the trace interval:
    ORDER BY STAT_CPU DESC
FETCH FIRST n ROWS ONLY;
    Similarly, you might use the following clauses in your query to identify the top n queries that have the highest average CPU time per query execution during the trace interval:
    SELECT STAT_CPU / STAT_EXEC
FETCH FIRST n ROWS ONLY;