Something olde,
Something new,
Something borrowed,
Something blue,
oh my goodness,
what’s going on,
within DB2?
Olde, New, Borrowed and Blue… in DB2 11 REBIND processing
Now once you stop laughing, the idea is that:
“olde” is REBIND processing which changes old access paths,
“new” is the new access path that comes from REBIND processing,
“borrowed” is the use, and abuse, of OPT_HINT processing, and finally
“blue” is, of course, IBM itself!!!
New in DB2 11… REBINDs with APREUSE(WARN)
Now in DB2 11 CM and above, you can issue REBINDs with the APREUSE(WARN) feature. This allows you to be nearly 100% sure that your access paths will *not* change, *but* it will regenerate the run time structures which allows fast xPROC processing. How does it do this magic? Quite simply: DB2 uses “internal” Opt Hints. They are not externalized in the PLAN_TABLE and they ignore the OPT_HINT ZPARM setting completely. In fact, they are generated from the “new”, in DB2 9 NF and above, directory element Explain Plan section in the Package, (this contains a sort of compressed version of the PLAN_TABLE access path data but nowhere near all of the access path data is saved away).
So what happens is, DB2 gets told to REBIND a package
– first it extracts the current access plan and stores it away,
– then it calls the optimizer for the new access path using the stored version as an OPT_HINT.
– If the hint is used, the access path is honoured and, if running with EXPLAIN(YES), you get PLAN_TABLE data with “APREUSE” in the column HINT_USED while OPTHINT stays empty.
Unknown access path “deviations” if the hint failed
Now, if a hint failed, a new access path is created but you then get spaces in the HINT_USED column. I bet you see what I am aiming for??? Yep, all this does is create unknown access path “deviations” while also retaining possibly useless and obsolete access paths! The whole system makes nearly no sense to me whatsoever!
APREUSE(ERROR) to migrate to DB2 11
Now with APREUSE(ERROR) at least you either got new code for a complete package or you didn’t get *any* runnable code. It was still not taking advantage of any new optimizer functionality, but on the other hand was 100% stable.
The way I see it today, the only way to actually use this feature is purely in version migration and with some sort of explain tool to actually check out stuff before & after the event.
My way to migrate to DB2 11 is
1) Make sure RUNSTATS is 100% up to date and correct
2) Make sure plan management is active and possibly in EXTENDED mode
3) Make sure all packages are bound with EXPLAIN(YES)
4) Migrate to DB2 11 CM
5) Use a tool to analyse all current access paths and see which ones go *bad* with and without using APREUSE(WARN). Also, check out any CHANGED access paths (table order, different Index etc.)
6) Run with APREUSE(WARN) only those packages where no changes are found and also use APRETAINDUP(NO)
7) Run without APREUSE(WARN) on those packages with improvements to get the full functionality that you have paid good money for
8) Work first on any statements that go bad and then the changed ones
9) From this point on analyse every REBIND package and only do the REBIND if improvements in access path are visible
10) Get ready for DB2 vnext and repeat the above!
APREUSE(WARN)’s drawback
The real dangers with APREUSE(WARN) are that you will end up locking your code into access paths that are completely obsolete and could be dramatically improved with the creation of a new index etc. This new data is not and cannot be used due to the hint nature of this parameter. Worse, in my opinion, is the fact that there are a ton of reasons when the hint is not used and new access paths come charging over the hill like some irate mother-in-law!!!
Use APREUSE(WARN) *just* for release migration and *only* for the first rebind
Last word: As always with new parameters and options there are times to use them and times to *not* use them! In this case *just* for release migration and *only* for the first rebind. Then — just like a wedding dress — never ever use again until the next one.
As usual, any comments or criticisms are greatly welcome!
How do you plan to use this parameter?
TTFN Roy Boxwell
Senior Software Architect