April 15, 2014

Dynamic Statement Cache: Sharing Prepared Statements

An SQL statement can be executed by an application with either static or dynamic text. Each type of statement execution has its own benefits. The biggest reason to use static SQL is to avoid the overhead with preparing a statement for execution.

Over the past decade, the use of dynamic SQL has grown dramatically, as IBM continues to improve its performance. Much of this improvement has come through providing DBAs with alternatives to having to prepare a dynamic statement each time one is executed. The first innovation in this area was the introduction of the dynamic statement cache, a pool of memory in which DB2 saves the control structures for prepared SQL statements that can be shared among different threads (execution of a program). To enable dynamic statement cache, YES must be specified for the CACHEDYN subsystem parameter.

When a statement is prepared, DB2 will search for the statement in the dynamic statement cache and reuse the control structures. This works great for applications that code predicates with host variable rather than literals. The problem with literals is that potentially every execution of a statement is unique, so in this case the cache will fill up with many statements, none of which will ever be reused. To help in this circumstance, IBM has introduced a new PREPARE parameter: CONCENTRATE STATEMENTS WITH LITERALS.

When using CONCENTRATE STATEMENT WITH LITERALS, the statement is stored in the cache and each literal is replaced with "&."  For example:

SELECT COL1, COL2 from T1 WHERE COL2 = ‘ABC’ is stored in cache as SELECT COL1, COL2 from T1 WHERE COL2 = &

When the statement is prepared, DB2 will search for the statement in the dynamic statement cache searching for the literal such as "ABC." When not found, DB2 searches the cache for the statement containing the replaced character, &. Once it's found, the control structures are reused. This process doesn't occur if the statement is coded with the "?" parameter marker; for example, this statement cannot take advantage of CONCENRATE STATEMENT WITH LITERALS:

            SELECT COL1, COL2 from T1 WHERE COL2 = ‘ABC’ and COL1 > ?

The avoidance of preparing a statement can provide a tremendous CPU savings. See the DB2 10 Performance Management guide for details about dynamic statement cache, including possible reasons why DB2 won't share certain identical statements in the statement cache.