For each DB2 for z/OS release, the same recommendation is put forth for static SQL – REBIND your packages to take advantage of any new optimizer enhancements. And we all know that the DB2 for z/OS optimizer does a fantastic job in choosing the most efficient access path. Nevertheless, a mass rebind involving several thousand SQLs can be a daunting task for many customers.
In this article we will cover the new Access Path Stability feature in DB2 9 for z/OS. And while there may have been articles already discussing this feature, the goal here is to address some of the common questions that DB2 for z/OS Optimizer Development regularly hears from customers.
Why REBIND in DB2 9 for z/OS?
The obvious answer is to take advantage of optimizer improvements in DB2 9 for z/OS. However, that is not the only reason to rebind.
Each release may introduce a revised format for the SQL runtime structures. While packages from a prior release will execute in the new release, there are performance overheads that occur because DB2 has to perform a translation of runtime structures.
In some cases, the runtime optimizations that were created at the last rebind may be invalidated. A good example is an SPROC (Select PROCedure), which is also known as fast column processing. Basically, DB2 will set up a structure that supports faster movement of the rows internally. The general rule of thumb is that the CPU saving is in the range of 0-10%, dependent on the complexity of the statement and number of columns retrieved. And while a migration to DB2 9 will invalidate this runtime saving and thus introduce a CPU overhead of 0-10%, a REBIND will re-establish these SPROCs
Another reason to REBIND in DB2 9 is to move part of the cursor table (CT) and package table (PT) above the 2GB bar , thus providing some virtual storage constraint relief below the bar.
When REBINDing in DB2 9, it is important to note that the clusterratio formula has been enhanced, and a new statistic DATAREPEATFACTORF is added. It is strongly recommended to run RUNSTATS in DB2 9 before REBINDing, to ensure you have the DB2 9 optimizer with DB2 9 statistics.
What protection do I have that some of the REBINDs will not result in sub-optimal query performance?
The goal of any optimizer is to choose the lowest cost access path. And there is no question that DB2 for z/OS has always had one of the strongest cost-based optimizers in the industry. But even though we continue to make progress on ensuring that the safest and most robust access path is always chosen, there is still opportunity for some queries to be assigned sub-optimal access paths, and therefore, regress in performance.
Customers can limit the risk to their applications by using the Access Path Stability feature of DB2 9 for z/OS.
Now that I understand why I need to REBIND, what is this Access Path Stability feature?
When a package is bound, DB2 stores all relevant package information (metadata, statement text, dependencies, authorizations, access paths, etc.) as records in several catalog tables and the directory. When the same package is rebound, many of these package “pieces” are deleted and new records take their place.
Access Path Stability introduces a mechanism whereby DB2 will retain certain package pieces instead of replacing them with new ones. In the event of a performance regression, the user can direct DB2 to fall back to the older copy of the package. Depending on the options used, a package can have up to three ‘copies’ that exist on disk. However, only one of these copies acts as the active copy. All other copies are considered inactive.
What types of packages are supported?
The package types supported are regular packages, non-native SQL procedures, external procedures, and trigger packages. However, this support is not currently available for native SQL stored procedures.
How can I control the usage of Access Path Stability?
This feature can be controlled by the PLANMGMT ZPARM or REBIND PACKAGE parameter. The ZPARM sets the default at the system level, which can be overridden by the REBIND parameter setting. Both have three possible values – OFF, BASIC, or EXTENDED. The default value is OFF.
With PLANMGMT = OFF, there will be little change in the existing (prior to DB2 9) REBIND behavior. REBIND will simply create a new current copy.
With BASIC, DB2 will retain 2 copies of a package – the current active copy and one previous copy. When the command is invoked, any previous copy is discarded, and the current copy becomes the previous copy. The incoming copy then becomes the current copy.
With EXTENDED, DB2 will retain 3 copies of a package – the current active copy, a previous copy, and an original copy. When the command is invoked, any previous copy is discarded, and the current copy becomes the previous copy. The original copy is managed as follows:
- If no original copy exists, the current copy is cloned to become the original.
- If an original copy exists, it will be retained as the original.
After these steps, the incoming copy then becomes the new current copy.
Why have both BASIC and EXTENDED options? Isn’t always better to keep 3 copies?
The major benefit of EXTENDED is that it keeps your original copy from when you first began using Access Path Stability. The previous copy is always replaced for every REBIND with either BASIC or EXTENDED. When using BASIC, upon first REBIND in DB2 9, your V8 copy will become the previous and your new DB2 9 copy will become the current. A subsequent REBIND will discard the previous, which was your V8 copy. If you had used EXTENDED instead, then the 2nd REBIND would still discard your previous copy, but you would still have the original copy, which is your V8 copy.
The only downside to EXTENDED is that 3 copies can triple the storage requirements for SPT01 and SYSIBM.SYSPACKDEP, whereas keeping 2 copies with BASIC only doubles the storage requirement. The question of what to do if your environment does not have enough space is addressed later.
What is the overhead of using Access Path Stability?
Maintaining multiple package copies does not come without cost. These additional costs are extra DASD consumption as well as the time taken to REBIND:
- Multiple package copies consume additional disk space in directory (SPT01) as well as certain catalog tables. BASIC can double, and EXTENDED can triple, the disk requirements used by SPT01. A similar overhead is seen in catalog table SYSIBM.SYSPACKDEP.
- Use of BASIC or EXTENDED can cause a 10-30% increase in the time it takes to REBIND packages.
Presence of extra package copies has NO effect on the running time of applications.
What if I do not have enough space to keep additional copies of all of my packages?
Firstly, you may want to track the progress of APAR PK80375, which provides the capability for compressing SPT01. At the time of writing, this APAR was still open.
As an alternative to compression, you may be able to selectively choose which packages are critical, and only REBIND those with the Access Path Stability feature.
Lastly, if you do have enough space for two copies (BASIC) and not three (EXTENDED), you could consider using a mix of BASIC and OFF. Consider a situation wherein you’ve just migrated to DB2 9, and you’d like to preserve the DB2 V8 access paths. Perform your first REBINDs with BASIC. From that point on, perform all subsequent REBINDs with OFF. This method will preserve your previous V8 packages. Note that rebind with BASIC twice will wipe out your V8 copies, so you’ll need to be careful.
How to get the saved copies back?
In DB2 9, we have introduced another rebind option, SWITCH. SWITCH will restore all relevant package information in the catalog tables and directory to that of the specified package copy. SWITCH comes in two flavors:
- SWITCH(PREVIOUS) - The current copy takes the place of the previous, and vice versa.
- SWITCH(ORIGINAL) - Moves the current copy to the previous copy. Any previous copy that existed is purged. The original copy would be cloned to take place of the current copy. The original is not purged.
If SWITCH is specified and no previous/original package copy exists, DB2 will issue a DSNT217I error. In addition, SWITCH must not be specified with any other REBIND
options. Otherwise, DB2 will issue a DSNT218 error message.
What if I change between using OFF, BASIC or EXTENDED?
If you have previously used only OFF or BASIC, and you begin using EXTENDED, then your first usage will copy the current to original, as well as copying the current to previous and discard the existing previous. Remember, only EXTENDED impacts the original copy, and this will only be updated by EXTENDED when there is no original in place.
If you use OFF after using either BASIC or EXTENDED, neither the previous or original copies are impacted. That is, they will remain intact.
How can I tell how many copies I have for each package?
The following SQL can be run against the catalog to determine the number of copies. This query applies to packages that have at least one static SQL statement. If Access Path Stability is not used for a package, the count will return 1. BASIC and EXTENDED return a count of 2 and 3 respectively.
select sp.collid, sp.name, sp.version,
count(distinct spd.dtype) as copycount
from sysibm.syspackage sp, sysibm.syspackdep spd
where sp.name = spd.dname
group by sp.collid, sp.name, sp.version
Are there any REBIND options that cannot be specified when using Access Path Stability?
When rebinding with BASIC or EXTENDED, the following options cannot be changed: OWNER, QUALIFIER, DBPROTOCOL, ENABLE, DISABLE, PATH, PATHDEFAULT, or IMMEDWRITE.
If an attempt is made to change any of these options, DB2 will issue a DSNTI216 error.
Does Access Path Stability support BIND?
In DB2 9 for z/OS, only REBIND PACKAGE is supported. Issuing a BIND of the package will result in all inactive copies being removed – both original and previous.
What happens with invalid or inoperable packages?
Package invalidation in DB2 is triggered when any object on which a package depends is altered or dropped. For example, this could involve dropping an index, dropping a table, altering a column in a table, etc. The catalog table SYSIBM.SYSPACKDEP records all dependencies of each copy of a package.
Depending on the change, one or more copies of the same package could be invalidated. The copy invalidated could be the ‘current’, ‘previous’, or ‘original’. Certain changes to the system, such as dropping a table, would invalidate all copies. Other changes, such as dropping an index, will only invalidate those copies that actually depend on the presence of the index.
Auto-bind kicks in when the current copy is found invalid. Auto-bind is not affected by the ‘invalidation’ status of the previous/original copies. Auto-bind will only replace the current copy. It will have no effect on the previous/original copies. If an invalid or inoperable package is rebound manually (using an explicit REBIND command), any
previous/original copies are left untouched. In other words, the current copy will not overwrite any previous/original copies. If the REBIND command utilized BASIC or EXTENDED, those directives are essentially ignored. The user is warned of this situation using DSNT219I message.
How can I tell if the previous or original copies are invalid?
The VALID indicator is stored in SYSIBM.SYSPACKAGE. So given that only the current copy is stored in SYSPACKAGE, you would need to SWITCH the previous or original to become the current to determine if they are valid. This will populate SYSPACKAGE, and at this point it is possible to determine if that package is valid.
A reminder that when switching the original, the previous copy will be discarded and the current will become the previous, and the original will be copied to the current. This is because the original is never replaced by a REBIND.
How can I determine what access path is related to each copy?
If your package was bound with EXPLAIN(YES), you’d have existing PLAN_TABLE records for one or more copies. To find the records that pertain to the current package copy, you can compare PLAN_TABLE.BINDTIME with SYSPACKAGE.BINDTIME. To determine the PLAN_TABLE records for a previous or original copy, you’d first have to switch to either of those copies.
How do I FREE the packages?
The FREE PACKAGE command has been extended to allow users to manually free inactive copies of packages. These would be any previous and original copies. This will help reclaim space.
FREE PACKAGE (collection. package) [ PLANMGMTSCOPE (ALL | INACTIVE) ]
- If the PLANMGMTSCOPE clause is not specified, or if PLANMGMTSCOPE(ALL) is used, the package will be freed in its entirety. This includes all copies.
- If PLANMGMTSCOPE(INACTIVE) is specified, the command will only free any previous and original copies. It will succeed even if a package has no inactive copies.
What should I do if I need to fall back to DB2 V8 after my migration to DB2 9?
If you would like to obtain your DB2 V8 access path as it was before the DB2 9 REBIND, then it is recommended to use the REBIND SWITCH to bring back the original or previous – whichever is your V8 copy. You can verify that it actually is your V8 copy without the corresponding PLAN_TABLE records because the SYSPACKAGE.BINDTIME will be a date earlier than your DB2 9 migration date.
The next step is to FREE PLANMGMTSCOPE(INACTIVE) to remove all inactive copies before the fallback to DB2 V8. If this step is not done, then the inactive copies will remain until the next REBIND in V8, which will clear all inactive copies.
Don’t forget also that after you fall back to DB2 V8, your statistics will likely be from DB2 9 RUNSTATS. Therefore you may wish to re-run RUNSTATS in DB2 V8, so that you truly have the DB2 V8 optimizer with V8 statistics.
In prior DB2 migrations, I have not been exposed to access path regression. So why should I consider utilizing this feature?
Past experience can be a guide, but may not guarantee your future. In each DB2 release, new access path choices are delivered that improve the ability for the DB2 optimizer to choose a more efficient plan. With more plan choices available in newer releases, there are also more opportunities for the optimizer to pick a plan that might not be optimal for your particular circumstances.
Therefore, your decision to utilize access path stability should be based upon your ability to deal with a problem if it arises, and not based upon prior experience.
If I migrate to DB2 9 and do not see any access path regressions in my development or test environments, can I conclude that I will also avoid such regressions in production?
For determining the lowest cost access path, the DB2 optimizer considers the catalog statistics, Bufferpool, RID and sortpool sizes, as well as CPU speed and the number of CPUs. While customers often copy production statistics to other environments for pro-active access path analysis, it is rare that everything else is equal between production and non-production environments.
While access path analysis in a representative environment is a good indicator of what to expect in production, it may not be a firm guarantee.
Conclusion
If you have read this article and concluded that we are encouraging REBIND after migration to DB2 9 with the Access Path Stability feature, then you would be correct. The best practices approach is to REBIND with the Access Path Stability feature in DB2 9 Conversion Mode once your migration is stable and you believe that fallback to DB2 V8 is unlikely. Whether you choose to perform a mass rebind or incrementally by application is dependent on space limitations for SPT01 and your ability to monitor and switch back to the previous/original package saved by Access Path Stability.
About the Authors
Adarsh Pannu is a Senior Software Engineer on IBM's DB2 for z/OS development team. He has almost two decades of experience developing database management systems, primarily focusing on query optimization and execution.
Terry Purcell is a Senior Technical Staff Member with the IBM Silicon Valley Lab, where he works on the design and development of the DB2 z/OS Optimizer. Terry has more than 18 years experience with DB2 in Database Administration and application development as a customer, consultant and DB2 developer.
TrackBack URI for this entry
Subscribe to this comment's feed
