Invalidation of stabilized dynamic SQL statements

Various actions and events can invalidate stabilized dynamic SQL statements.

Stabilized dynamic SQL statements are not affected by events that invalidate statements in the dynamic statement cache. However, they can be invalidated. Statements that result in invalidating static packages also invalidate stabilized dynamic queries. For example, changing objects that are referenced by the statement, such by issuing ALTER, REVOKE, and DROP statements can invalidate stabilized dynamic SQL statements. See Changes that invalidate packages.

When these situations occur, Db2 marks the corresponding SYSDYNQRY row for the stabilized access path as invalid. The next time an invalidated statement is prepared, Db2 uses the full prepare process to generate new access paths. Invalidated statements are not stabilized until another START DYNQUERYCAPTURE command is issued to stabilize them again.

The VALID column of the SYSDYNQRY catalog table indicates the validity of stabilized dynamic statements. The COPYID=0 indicates the valid copy, and COPYID=4 indicates the invalidated copy.

You can also remove stabilized dynamic SQL statements from the catalog by issuing a FREE STABILIZED DYNAMIC QUERY command. For more information, see Removing stabilized dynamic SQL statements from the Db2 catalog.