DB2 10.5 for Linux, UNIX, and Windows

FLUSH OPTIMIZATION PROFILE CACHE statement

Multiple statements can be compiled using the same optimization profile.

To make optimization profile processing more efficient, the optimization profile is processed the first time it is used to optimize a statement, and the output is stored in the optimization profile cache. Subsequent references to the optimization profile use the processed version in the optimization profile cache.

An optimization profile should be removed from the optimization profile cache when the version stored in SYSTOOLS.OPT_PROFILE has been updated. When the old version is removed from the cache, the new version will be used upon optimization of subsequent statements that use the optimization profile.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared.

Authorization

The privileges held by the authorization ID of the statement must include either SQLADM or DBADM authority (SQLSTATE 42502).

Syntax


                                     .-ALL---------------------------.   
>>-FLUSH OPTIMIZATION PROFILE CACHE--+-+---------------------------+-+-><
                                       '-optimization-profile-name-'

Description

optimization-profile-name: Specifies the name of the optimization profile to be flushed from the optimization profile cache. If the name specified is unqualified, the value of the CURRENT DEFAULT SCHEMA register is used as the implicit qualifier.
ALL: Specifies that all profiles on all active database partitions be flushed from the optimization profile cache.

Notes

The FLUSH OPTIMIZATION PROFILE CACHE statement removes all or a single optimization profile from the optimization profile cache. It also causes the logical invalidation of any cached dynamic SQL statements that were prepared with that optimization profile.
New access plans for any invalidated dynamic plans are regenerated when the next request for the same SQL statement is made.
Packages that reference an optimization profile removed from the optimization profile cache by this statement must be explicitly bound again to allow new access plans to be generated.

Examples

Example 1: The optimization profile "Rick"."Foo" is flushed from the optimization profile cache.
```
   SET CURRENT SCHEMA = '"Rick"' 
   FLUSH OPTIMIZATION PROFILE CACHE "Foo" 
```
Example 2: The optimization profile JOHN.ALL is removed from the optimization profile cache.
```
   SET CURRENT SCHEMA = '"Rick"' 
   FLUSH OPTIMIZATION PROFILE CACHE JOHN.ALL 
```

Messages

No errors are issued if the optimization profile cache is empty or if the specified optimization profiles (specified explicitly or implicitly) do not exist in the optimization profile cache.