Dynamic statement cache enhancements

In DB2® 10, when dynamic statement caching is active, you can specify that DB2 exclude the literal constants that are in dynamic SQL statements when searching for a statement text match in the dynamic statement cache. This enhancement results in more sharing and reuse of cached statements for your applications.

This enhancement to dynamic SQL statement sharing is provided by the new CONCENTRATE STATEMENTS WITH LITERALS clause that is within the ATTRIBUTES clause of the PREPARE SQL statement. When you specify this new clause in a PREPARE statement and DB2 determines that there is no match in the dynamic statement cache for the dynamic SQL statement, DB2 replaces certain literal constants with the ampersand character (&) in the dynamic SQL statement. Then, DB2 repeats the cache matching search by using this new version of the SQL statement. If DB2 does not find a statement text match in the cache, DB2 inserts a new statement into the cache. The SQL statement text that is inserted into the cache contains ampersand characters in place of the original literal constants.

When an application runs an instance of this dynamic SQL statement that has different literal constants, DB2 searches the cache and replaces the literal constants with ampersand characters. Now, DB2 finds matching statement text in the cache during the repeated cache search. If the additional cache matching criteria is satisfied, the new instance of the dynamic SQL statement that has different literal constants will share or reuse the cached version of the SQL statement that contains ampersand characters. In this example, your application avoids unnecessary preparation processes, and another statement is not inserted into the dynamic statement cache. These benefits can improve the overall performance of applications that use these statements and reduce storage growth in both the cache and DB2 storage pools.

This statement sharing enhancement does not apply to dynamic SQL statements that contain both parameter markers (?) and literal constants.

If a dynamic statement does not qualify to be inserted in the dynamic statement cache, a statement identifier is not generated for that statement. For example, dynamic DDL statements and the dynamic LOCK TABLE statement do not qualify for the dynamic statement cache. In DB2 10, to help with problem determination and diagnostics, a predefined statement identifier with a value of 1 is reserved for dynamic DDL statements and the dynamic LOCK TABLE statement.

DB2 10 also provides JDBC and ODBC properties for this enhancement so that you do not need to specify the new clause on the PREPARE statement. For IBM® Data Server Driver for JDBC and SQLJ type 2 connectivity, the new connection property is statementConcentrator. For ODBC drivers, the new keyword is LITERALREPLACEMENT.