Bookmark and Share
RSS

Recent Posts

DB2 12 Plan Stability with APREUSESOURCE

February 16, 2017

During a migration, DB2 12 will automatically rebind any package bound in DB2 9 and earlier versions. The automatic rebind results in a new package, while the previous package copy is automatically discarded. However, to ensure plan stability, a BIND should be done manually, because auto rebind won't copy the original package to the previous one. So if a regression in access path occurs, the REBIND SWITCH option is not available.

Before migrating to DB2 12, it's recommended to run the DSNTIJPM pre-migration job to return a list of packages that are flagged for automatic bind. Rebind all such packages with PLANMGMT(EXTENDED) while in DB2 11. This will provide a safety net so you can reclaim the optimum access path from the previous copy of the package.

At times while performing a migration -- or even through the normal course of development --  you could find that a previous or original package copy is marked invalid. However, that original or previous package can still be used as the current package with the REBIND SWITCH feature, provided you're using it in DB2 11.

That said, even after using REBIND SWITCH, your rebind could fail, leaving the current package invalid. If this happens, use REBIND APREUSE(WARN). This will reuse as many access paths as possible from the current package, and any failing statements would go through normal optimization, enabling a valid access path to be built.

So as you can see, this is a two-step process. First perform a REBIND SWITCH (with either the PREVIOUS or ORIGINAL setting), and then REBIND APREUSE(WARN) to regenerate a valid package. DB2 12 function level V12R1M500 (the equivalent of NFM in DB2 11) will not allow the REBIND SWITCH to copy in an invalid package, so this process can be performed in a single step regardless of the package status by using REBIND APREUSE(WARN) along with the new parameter, APREUSESOURCE (CURRENT, PREVIOUS or ORIGINAL). When PREVIOUS is specified, DB2 will use the previous copy access path as input into the current copy bind. When ORIGINAL is specified, the original copy access path is used as input into the bind. (CURRENT is the default when no specification is made.)

To reuse the previous copy's access path in the current copy package, issue this command:

    REBIND APREUSE (WARN) APREUSESOURCE(PREVIOUS)

If a statement access path is invalid, a new access path is created. The REBIND ends with a new package in the current copy.

Posted February 16, 2017 | Permalink

comments powered by Disqus