Identifying dynamic SQL statements to stabilize

You can observe the dynamic statement cache to identify dynamic SQL statements that might benefit from dynamic SQL plan stability.

To identify dynamic SQL statements to stabilize:

1. Clear and populate the DSN_STATEMENT_CACHE table.
   For example, you might issue the following statements:

   

   DELETE FROM DSN_STATEMENT_CACHE_TABLE;
   COMMIT;
   EXPLAIN STMTCACHE ALL;
   COMMIT;
   

   
2. Query the dynamic statement cache table.
   For example, you might issue the following statement:

   

   SELECT STMT_ID, STBLGRP, QUERY_HASH, PER_STMT_ID, 
        STABILIZED, CURSQLID, STAT_EXECB, STAT_CPU, STAT_ELAP, 
        STMT_TEXT 
   FROM  DSN_STATEMENT_CACHE_TABLE
   WHERE LITERAL_REPL = ''
       AND   STAT_EXECB > 10
       AND   CURSQLID = 'APP1ID'
   ORDER BY STAT_EXECB DESC;

   

   The WHERE clause of the example statement applies the following filters:

   WHERE LITERAL_REPL = ‘’

   Excludes statements with concentrated literal values

   AND STAT_EXECB >= 10

   Exclude queries that run infrequently.

   AND CURSQLID = 'APP1ID'

   Return only queries used by a particular SQLID.

   The ORDER BY STAT_EXECB DESC clause arranges the result by the frequency of execution.

   The example query selects the following columns.

   STBLGRP

   The stabilization group.

   QUERY_HASH

   A hash key generated from the SQL statement text, which is a stable identifier.

   PER_STMT_ID

   The identifier of a stabilized dynamic SQL statement when it is loaded from the catalog

   STABILIZED

   Whether the statement is already stabilized. A statement might have been stabilized in another member after it entered the cache.

   CURSQLID

   The current SQL authorization identifier for the statement. This value is useful for selectively capturing of statements for stabilization.

   STAT_EXECB

   The number of times that the statement has been executed, which is useful for determining the threshold for capturing statements.

   STAT_CPU and STAT_ELAP

   The performance characteristics of the statement.

   STMT_TEXT

   The statement text.

3. Analyze the query result for answers to the following questions
   • How many queries might be stabilized for a particular threshold?
   • Is the existing performance acceptable? You do not want to stabilize poor performance.