Many years ago I made some comments about how to avoid surprises when you do BIND or REBIND of your DB2 packages. A lot have happened in DB2 regarding this subject and in DB2 10 all the wishes of a DB2 DBA came through. These wishes are:

1. BIND/REBIND fails if the access path for the package changes
2. BIND/REBIND uses the existing access path when possible
3. BIND/REBIND reports the new access path without changing the existing one
4. BIND/REBIND can be directed to use a predefined access path

The first wish is fulfilled with option APCOMPARE(ERROR). If just one of the statements in the existing package changes its access path the BIND/REBIND fails. A marvelous option when you want to do REBIND after migrating to a new DB2 version or after a RUNSTATS. Then you may want to know which packages get a new access path without impacting existing performance.

Second wish is fulfilled with option APREUSE(WARN) (actually this option is new in version 11). The access path for all unchanged statements are reused if possible. If the DB2 package worked before REBIND using this option it will also work afterwards and use the same access path. Compared with APCOMPARE the difference is that APCOMPARE will try to give you a new access path while APREUSE is about keeping the existing access path. APREUSE is most versatile when migrating to a new DB2 version, because IBM typically promises better performance by doing a REBIND without impacting the access path.

The third wish is fulfilled with option EXPLAIN(ONLY). The new access path is as usual reported in PLAN_TABLE and other explain tables, but the actual REBIND is not carried out. Then you can compare the the rows in PLAN_TABLE for the current package with the to-be package if rebound. APCOMPARE and APREUSE are pretty straightforward to understand and take advantage of, but it takes more effort to benefit from EXPLAIN(ONLY) as it requires you to be able to compare the current access path with the to-be accecss path.

The fourth and last wish is fulfilled with option OPTHINT which makes it possible to make DB2 consider an alternative access path compared to the one DB2 will choose. IBM has made it quite difficult to benefit from this option, but it works! I will be back on how to use OPTHINT.

Please remark the existance of APCOMPARE(WARN) and APREUSE(ERROR). Both of these options were introduced in version 10. I will recommend you to read all about APCOMPARE and APREUSE in the DB2 commands manual.