The dynamic statement cache (DSC) was introduced in DB2 for OS/390 Version 5 to support applications that use dynamic SQL, such as PeopleSoft and SAP.

Each SQL statement must be prepared before it is executed. For static SQL, most of this work is done during the BIND process and is performed only once. For dynamic SQL, the PREPARE request is done before the SQL statement is executed. Before the DSC existed, a dynamic SQL statement had to be prepared every time it was executed, even if it was identical to a previously prepared statement.

The main reason for the DSC is based on the fact that the PREPAREs for the same SQL statement should be preserved: the statement is prepared only once and executed multiple times.

This white paper attempts to answer the following questions about the DSC and outlines the features that were introduced after DB2 for OS/390 Version 5 to meet the challenges posed by these questions.

When is an SQL statement considered to be the same?
What happens if the underlying database design is changed?
What does the selected access path look like?

It also explains new functionality that was introduced by IBM DB2 Analytics Accelerator or by DB2 12 for z/OS, such as dynamic plan stability.

This white paper provides suggested best practices. It is not intended to be exhaustive and is provided on an as-is basis.