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

Begin program-specific programming interface information.

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

Start of change 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. Start of change Just before migration to a new Db2 release and before activation of a higher function level are also good times to do this. End of change If you do that, 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.

Start of change In particular, you might want to remove inactive package copies that have become invalid, and especially original copies that are invalid. Such package copies cannot be used and result in wasted disk space. End of change

Procedure

To remove saved copies of access path information for static SQL statements:

Issue FREE PACKAGE commands and specify the PLANMGMTSCOPE value for the package copies that you want to free.

You can selectively free specific inactive package copies. That is, you can specify that you want to free only the previous or original package copies. End of change

You can specify the following PLANMGMTSCOPE values:

ALL

Frees the entire package, including copies. This is the default. This option is not supported for trigger packages or SQL routines.

INACTIVE

Frees only previous, original, or eligible phased-out copies from the directory, catalog, and access path repository. FREE PACKAGE with PLANMGMTSCOPE(INACTIVE) succeeds even if the package has no inactive copies. Inactive package copies can be freed while applications are executing the current copy of the package. The phased-out copies can also be freed regardless of the INVALIDONLY option.

ORIGINAL

Frees original package copies from the directory, catalog, and access path repository. FREE PACKAGE with PLANMGMTSCOPE(ORIGINAL) succeeds even if the package has no original package copies. Original package copies can be freed while applications are executing the current copy of the package.

PREVIOUS

Frees previous package copies from the directory, catalog, and access path repository. FREE PACKAGE with PLANMGMTSCOPE(PREVIOUS) succeeds even if the package has no previous package copies. Previous package copies can be freed while applications are executing the current copy of the package.

PHASEOUT

Frees eligible phased-out copies from the directory, catalog, and access path repository. FREE PACKAGE with PLANMGMTSCOPE(PHASEOUT) succeeds even if the package has no phased-out copies. Phased-out package copies can be freed while applications are executing the current copy of the package.

When PLANMGMTSCOPE(PHASEOUT) is specified, phased-out package copies are freed regardless of whether the INVALIDONLY(YES) option is also specified.

Start of change If you want to free only inactive copies that are also marked invalid, you can specify the INVALIDONLY(YES) option. End of change

However, if PLANMGMTSCOPE(INACTIVE) or PLANMGMTSCOPE(PHASEOUT) is specified, Db2 ignores INVALIDONLY(YES) and frees all phased-out package copies.

Start of change After you free the original copy, the next rebind saves the current copy as the new original copy. End of change

The PLANMGMTSCOPE option cannot be used for remote processing.

Start of change The PLANMGMTSCOPE(ALL) option is not supported for packages for triggers and SQL routines. To free active package copies for such packages, you must issue a DROP statement for the trigger or SQL routine. End of change

End program-specific programming interface information.

Examples

The following command frees only original package copies:

FREE PACKAGE (collection-name.package-name) PLANMGMTSCOPE (ORIGINAL)

The following command frees any original or previous package copies that are marked invalid:

FREE PACKAGE (collection-name.package-name) PLANMGMTSCOPE (ALL) INVALIDONLY (YES)

If the subsystem on which you run this command has any packages that represent Db2 REST services, message DSNT279I is issued to indicate that those packages cannot be freed using the FREE PACKAGE command. That message can be ignored. End of change

The following command frees all original and previous package copies:

FREE PACKAGE (collection-name.package-name) PLANMGMTSCOPE (INACTIVE)