Invalidating statements in the dynamic statement cache

DB2® invalidates statements in the dynamic statement cache when you run the RUNSTATS utility on objects to which those statements refer. In a data sharing environment, the relevant statements are also invalidated in the cache of other members in the group.

About this task

The invalidation of the cached statements ensures that the next invocations of those statements are fully prepared and use the latest access path changes. The following actions, among others, require that you invalidate cached dynamic statements to get new access paths:

Statistics collection.
Online subsystem parameter changes.
Index changes.
Changes to profile tables for optimization parameters, production system modeling, or query acceleration thresholds.

This invalidation affects only future PREPARE operations. The next time that one of the invalidated statements is prepared, a new statement is built for the dynamic statement cache. However, any DB2 threads that already retrieved a copy of the statement from the dynamic statement cache before RUNSTATS completes continue to use that copy of the statement.

Important: If you received SQLCODE -904 with reason code 00E70081, this procedure for invalidating statements in the dynamic statement cache does not solve the problem.

Procedure

To invalidate statements in the dynamic statement cache:

Run RUNSTATS on the objects that are referenced by the statements that you want to invalidate. To invalidate cached statements without collecting statistics, specify the UPDATE NONE and REPORT NO options.