Reverting to saved access paths for static SQL statements

In the event that package that contains static SQL statements suffers performance regression after a rebind, you can fall back to a copy of the better performing access paths for the package.

Before you begin

The following prerequisites have been met:

• You previously specified a plan management policy to specify that DB2® saves access path information.

To revert a package to use previously saved access paths, use one of the following approaches:

• Specify the SWITCH option when you issue one of the following commands:
  • REBIND PACKAGE
  • REBIND TRIGGER PACKAGE
  You can specify one of the following options:

  Option	Description
  SWITCH(PREVIOUS)	DB2 toggles the active and previous packages: The existing active copy takes the place of the previous copy The existing previous copy takes the place of the active copy. Any existing original copy remains unchanged.
  SWITCH(ORIGINAL)	DB2 replaces the active copy with the original copy: The existing active copy replaces the previous copy. The existing previous copy is discarded. The existing original copy remains unchanged.

  You can use wild cards (*) in the syntax to restore the previous or original packages for multiple packages. When you specify the SWITCH option, package copies that are stored as rows of data in the following objects are modified:
  • DSNDB01.SPT01 table space
  • SYSIBM.SYSPACKDEP catalog table
  • SYSIBM.SYSPACKCOPY catalog table

  If no previous or original package copies exist, DB2 issues a DSNT217I error message for each package the does not have copies and processes the remainder of the packages normally.

  
• If the previous or original package copy is invalid, or it was bound in a release prior to Version 6 Release 1, take the following actions:
  1. Rebind the package and specify the SWITCH bind option that corresponds to the invalid package.
  2. Rebind the package again and specify the APREUSE(WARN) or APREUSE(ERROR) bind option.