Or should I use the term plan management, or maybe package management, plan stability, or even package stability? This feature has more names than a spy in an Ian Fleming novel. Of course, it doesn’t matter what you call it, the bottom line is this process has to be one of the coolest improvements to a plan or package since, well since forever. It allows for a fairly simply mechanism for getting back to a previous package. This is way beyond optimization hints (a somewhat misunderstood feature which may or may not do exactly what you think it really does every time you try to use it; but I digress)…… For now, I’m sticking with “Access Path Management”. That’s what Terry Purcell called it in his optimization update webcast he did last week. (Just in case you don’t know the name, Terry is with IBM’s Silicon Valley Lab as an STSM on the DB2 for z/OS Optimizer Development team.)
Access Path Management (plan management) almost feels new, like we have just started “kicking it’s tires” to see if it’s really worth pursuing. When in fact, it has been around for quite sometime.
There are lots of very tasty improvements/enhancements/new function delivered in DB2 10 in conjunction with access path management; multiple copies of a package, validating access at bind, explaining access path, and cleaning up those multiple copies.
Let’s look at what arrived first, other than EXPLAIN of course. It’s the ability to manage what happens to the working access path when you decide to do a rebind.
Other than time and cost, the above is a huge reason why some customers will do all they can to avoid performing a rebind after upgrading to a new version of DB2. They just don’t want to lose that access path their application is using. So, as stated above, you can put aside the old and still create the new.
Access path management, in its first incarnation referred to as plan management, first appeared back in DB2 9 as a couple of DSNZPARM keywords: PLANMGMT and PLANMGMTSCOPE, both on the DSN6SPRM macro. These ZPARMs set up the DB2 subsystem defaults for the corresponding REBIND PACKAGE (and REBIND TRIGGER PACKAGE) options. When using the PLANMGMT, it could be set to OFF (disabled), BASIS (previous copy of package is retained by REBIND), or EXTENDED (previous and original packages are retained at REBIND).
If needed, you can specify REBIND… SWITCH(PREVIOUS) to get you back to the previously retained copy of the package making the previous copy the now current version and the current version the new previous copy. This process works for PLANMGMT(BASIC) and PLANMGMT(EXTENDED). However, for PLANMGMT(EXTENDED) only, you can also use a REBIND…
SWITCH(ORIGINAL) to get back to the original copy of the package making the current copy the previous version. The existing previous copy is discarded.
With all these copies of packages hanging around, DB2 next delivered a method to clean them up. The PLANMGMTSCOPE option on the FREE PACKAGE command would remove all copies of a package (ALL) or just those copies no longer in use (INACTIVE )
This was a definitely a step in the right direction, but the lab didn’t stop there. They had even more to deliver to aid in the management of you access paths.
The next access path management enhancements DB2 would makes available are APCOMPARE, APREUSE, and EXPLAIN(ONLY) on the BIND and REBIND commands.
First, lets discuss ACOMPARE. As the name implies, this BIND and REBIND PACKAGE command option compare the new access path (the access path you’re about to create) against the old access path (the access path you’re about to replace).
APCOMPARE comes with a couple of parameters: NONE, WARN, ERROR. You get to choose one or just leave the option off the BIND/REBIND. If an APCOMPARE parameter is not specified it defaults to APCOMPARE(NONE), no compare is performed. NONE is the default. If the APCOMPARE option is not specified on the BIND/REBIND command, you get the behavior that existed prior to when APAR PM25679 was applied.
If APCOMPARE(WARN) is specified, the BIND (assume from this point forward in the blog post that you’re reading BIND/REBIND every time you see BIND) replaces your package with your new access path even if a different access path is detected.
The last parameter for this option is APCOMPARE(ERROR); do not replace the package with a new access path because a difference in the previous and current access paths has been detected. The original access path prior to attempting the BIND is reused. This is the option that has the greatest potential of helping you to avoid a runtime access path surprise.
The other option introduced by APAR PM25679 for BIND/REBIND is APREUSE. This options lets the BIND/REBIND process attempt to reuse the current (or existing) access path. The APAR stresses that if an access path reuse is successful, the before and after information in the plan_table is identical.
Valid APREUSE option keywords are ERROR and NONE (NO). APREUSE(ERROR) prevents the package BIND from succeeding if DB2 cannot use the original access path. With APREUSE(NO) no reuse action is attempted.
If this option is used in conjunction with the BIND/REBIND EXPLAIN(YES/ONLY) option, the success or failure of the reuse is reported in the HINT_USED column of the PLAN_TABLE. There is no guarantee of access path reuse, There are conditions, like a missing index from the original access path, that simply make reuse impossible.
DB2 10 did something else very nice for you to help make managing all these packages a little easier. There is a Catalog table in DB2 10, SYSIBM.SYSPACKCOPY, that hold all that cool information we are so fond of about the previous and original copies of the package. You no longer need to guess what is going to happen when you do a SWITCH.. and yes, you did guess before this table was added.
Another DB2 10 improvement and the last REBIND option that affects this whole access path management discussion that needs to be discussed is APRETAINDUP; the available values are YES or NO. What happens if you you turn on access path management (PLANMGMT set to BASIC or EXTENDED) and the package you are about to write out to SPT01 is identical to the one that already exist. Do you really need both copies, the current and previous, if they are the same? Isn’t that a bit of a waste of space? That people in DB2-land thought so… so they slipped in the REBIND option APRETAINDUP. If you set it to YES the old copy of the package sticks around even though it is the same as the one you are rebinding. However, if you set it to NO, DB2 will NOT retain the old copy thus saving you a bit of storage space.
How much space there is available in a table space can always be a concern. When that table space happens to be in the DB2 directory, then it can become of significantly more concern. We have just been discussing placing two or maybe even three copies of a package into SPT01. That can, probably will, have an affect on the available space in SPT01. You still have a 64GB limitation on SPT01 size. One possible solution, if you choose to us access path management, is compression. The DSNZPARM COMPRESS_SPT01 in the DSN6SPRM allows you to enable compression in SPT01. And don’t think for a minute that because DB2 10 uses LOBs in SPT01 that compression is no longer necessary. DB2 10 uses inline LOBs for packages so you may still want to consider setting this ZPARM on.
Finally, just a few last things to make sure you are completely confused, or perhaps for you to avoid future confusion.
With all the new stuff that has been done to using an EXPLAIN, how about a quick review of what’s available (I also got this idea for a quick review from that same webcast Terry did).
First we have what we have always had. If you want to generate a brand new shiny access path, create a new package, and you want to populate the EXPLAIN tables, you perform a BIND or REBIND specifying EXPLAIN(YES).
However, if you only need a new access path written out to the EXPLAIN tables (NO new package), then all you have do is BIND or REBIND with EXPLAIN(ONLY).
So much for the bind stuff….
Then there is the situation (again something we have been doing for almost forever) where you want to generate a new access path and write the results out to the EXPLAIN tables, you can use the good old EXPLAIN PLAN (usually issued form something like SPUFI or DSNTEP4).
But what if you already have an existing package that behaves just fine but you just need to know what access path it is using, you have the DB2 10 option of running an EXPLAIN PACKAGE. EXPLAIN PACKAGE only populates the EXPLAIN tables; no messing around with access paths. BTW, this only works on a package created in DB2 9 or later.
All of this new EXPLAIN stuff is pretty cool. If you want a little more detail, and to prevent this posting from getting way too long, I did a blog post back on January 13, 2011 that discussed this very topic. Check out “DB2 10 is enhancing good old EXPLAIN: La deuxième tentative” for additional EXPLAIN detail.
BTW, we haven’t even touched on using optimization hints to get back to a previous preferred access path. Maybe we’ll get to that topic in the near future.
To close out today’s blog post (which did end up being a whole lot longer than I had planned), I wanted to list out a couple of APARs you might want to check out. At a minimum make sure they are all applied to your DB2 subsystem. The other nice things about these APARs, they have lots of detail about all the stuff just discussed in today’s blog.
PK52522: PRECONDITIONING FOR NEW FUNCTION — PLAN STABILITY (December 5, 2007)
PK52523: NEW FUNCTION — PLAN STABILITY (December 6, 2007)
PK80375: NEW FUNCTION – COMPRESS_SPT01 DSNZPARM keyword (October 13, 2009
PM27811: NEW FUNCTION – SPT01_INLINE_LENGTH ZPARM keyword (March 31, 2011)
PM25679: ACCESS PATH ENHANCEMENT – BIND APCOMPARE / APREUSE (July 27, 2011)
….While writing today’s blog entry, I was listening to the Chris Strandring’s new album 3/2012) “Electric Wonderland”. this is my seventh album by Chris. To say the least, I enjoy his playing and the way he makes every new album a new experience., This is just an excellent listen and perfect to as a background for my writing….