FLUSH PACKAGE CACHE statement

The FLUSH PACKAGE CACHE statement invalidates cached dynamic SQL statements in the package cache. This invalidation causes the next request for any SQL statement that matches an invalidated cached dynamic SQL statement to be compiled instead of reused from the package cache.

Invocation

This statement can be embedded in an application program or issued by using dynamic SQL statements. It is an executable statement that can be dynamically prepared.

Authorization

The privileges that are held by the authorization ID of the statement must include SQLADM or DBADM authority.

Syntax

Read syntax diagramSkip visual syntax diagramFLUSH PACKAGE CACHEDYNAMICFOR EXECUTABLE IDexecutable idUSING HARD INVALIDATION
FOR EXECUTABLE ID executable id
An input argument of type VARCHAR(32) FOR BIT DATA that contains the executable ID used to identify the section to be removed from the package cache. The executable ID cannot represent a static section (4274L). If the executable ID does not map to a currently cached entry, no action will be taken and an error will be returned (4274L).
USING HARD INVALIDATION
Specifies that, if the identified package cache entries to be invalidated are currently being used, the FLUSH PACKAGE CACHE statement will wait until the entry is no longer being used before completing the invalidation.

Notes

  • This statement affects all cached dynamic SQL entries in the package cache on all active database partitions.
  • As cached dynamic SQL statements are invalidated, the package cache memory that is used for the cached entry is freed if the entry is not in use when the FLUSH PACKAGE CACHE statement runs. Entries are not evicted during the FLUSH PACKAGE CACHE statement if the FOR EXECUTABLE ID clause is used. If the entry still remains in the cache after the statement is executed, the VALID element for that entry will indicate that it is invalid and subsequent requests for that statement will cause a new compilation.
  • Any cached dynamic SQL statement currently in use is allowed to continue to exist in the package cache until it is no longer needed by the current user. The next new user of the same statement will force an implicit prepare of the statement, and the new user will run the new version of the cached dynamic SQL statement.

Examples

To mark all cached dynamic SQL entries invalid to force them to be prepared again:

FLUSH PACKAGE CACHE DYNAMIC

To force a specific cached entry out of the cache, find the target executable ID:

select section_type, executable_id, substr(stmt_text, 1, 30) as stmt_text from table (mon_get_pkg_cache_stmt(null, null, null, -1)) where stmt_text like 'VALUES (17+30)%'

SECTION_TYPE EXECUTABLE_ID                                                       STMT_TEXT                    
------------ ------------------------------------------------------------------- ------------------------------
D            x'0000000100000000000000000000000100000000000220190220155112678301' VALUES (17+30)               

  1 record(s) selected.

And then issue the command to force it out:

flush package cache dynamic for executable id x'0000000100000000000000000000000100000000000220190220155112678301' using hard invalidation
DB20000I  The SQL command completed successfully.