Freeing saved access paths for static SQL statements
The packages copies that are saved when you rebind with a plan management policy can become stale or invalid, and they use disk space. You free the package copies so that Db2 generates new package copies, or to reclaim the disk space that they use.
Before you begin
You can do this task only if you previously specified a plan management policy to specify that Db2 saves access path information for SQL statements, as described in Saving access path information for static SQL statements. Use the default option EXTENDED in most cases.
About this task
Original package copies in particular can become stale or invalid because Db2 never replaces the original copies after you first rebind with EXTENDED, unless you free them. An original copy that is many years old probably cannot be used. Because the goal is to keep stable and usable access paths available, free the copies and rebind again, so that Db2 saves a new original copy. Do this on a regular basis, when you are satisfied with current copy. Just before migration to a new Db2 release is also a good time to do this. If you do that. then in the first bind on the new release, Db2 saves what was the current copy on the previous release as the new original copy.
When you save access path copies for static SQL statements, some disk space is also used to save the copies. The amount of space that is used depends on the plan management policy that you choose. When you use the EXTENDED plan management policy, the amount of space used might be triple the amount used when you specify only that plan management is ON, if you do not specify the APRETAINDUP option when you bind the packages. Consequently, you might want to remove some or unneeded historical copies of the access path information to free disk space.
Procedure
To remove saved copies of access path information for static SQL statements:
- ALL
- Frees the entire package, including copies. This is the default.
- INACTIVE
- Frees only previous or original copies from the directory, catalog, and access path repository. FREE PACKAGE with PLANMGMTSCOPE(INACTIVE) succeeds even if the package has no inactive copies.
The PLANMGMTSCOPE option cannot be used for remote processing.
Examples
FREE PACKAGE (collection-name.package-name) PLANMGMTSCOPE (INACTIVE)