About Invalidating Cached Statements

After DB2 prepares a SQL statement and places it in the global statement cache, it is reused. If you are performing SQL tuning and you want to force DB2 to prepare and reoptimize a cached statement, the global SQL statement cache must be invalidated.

To invalidate an entry in the global statement cache for a specific SQL statement, one of the tables referenced by the SQL statement or the table catalog statistics must be altered in some way.

You can invalidate a dynamically cached statement using one of the following methods:

• Use the RUNSTATS utility to automatically invalidate cached statements that refer to objects against which RUNSTATS is executed. You can run the RUNSTATS utility on any table or table space referenced in the SQL statement.

  You can use the REPORT NO UPDATE NONE option to invalidate the cache entries without incurring the overhead of executing all the RUNSTATS logic.

• Use the DB2 command STOPOBJECT or the SQL DROP, ALTER, or REVOKE statements (for example, REVOKEALLFROM PUBLIC) on any object related to the plan to invalidate both global and local caches. While some ALTER statements require that an object is stopped, others do not; for example, STOP OBJECT is not required for DROP or REVOKE.

• Use the command ALTER TABLE tablename AUDIT NONE to purge statements that reference the named table from the cache.

• Use a CREATE INDEX statement on tables used in the DB2 access path.

• Stop the DB2 subsystem and restart it.