Reverting to saved access paths for static SQL statements

If a 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

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.

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

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

  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.

  Db2 also issues DSNT330I messages for any packages that are invalid or that were bound in any release prior to DB2® 10.

  
• If the package copy is invalid, or it was bound in a release prior to DB2 10, rebind the package and specify the APREUSE(WARN) or APREUSE(ERROR) bind option with the APREUSESOURCE(PREVIOUS) or APREUSESOURCE(ORIGINAL) bind option.