On the mainframe, change has traditionally been strictly controlled. But one exception has been DB2 access paths. So this month I’d like to focus on access path management for those who administer DB2 for z/OS environments. Whether you are implementing changes into your DB2 applications, upgrading to a new version of DB2, or simply trying to achieve optimum performance for existing application plans and packages, an exhaustive and thorough bind and rebind management process is a necessity.
In most mainframe shops everything is tightly controlled. If we make even a minor change to an application program, that program is thoroughly tested before it ever reaches a production environment. The program progresses through unit testing, QA testing, volume testing, and so on. Testing minimizes the risk of unintended consequences. We do the same type of due diligence with most other changes in the mainframe world. Database changes are planned and thoroughly tested. System software (e.g., CICS, IMS, etc.), including subsystem and DB2 changes, are all subject to strict change control procedures. This is done to minimize disruption to the production work being conducted by our business folks.
But there is one exception to this tight change control environment: Binds and rebinds are typically done in the production environments without the benefit of oversight or prior testing. This lack of change control results in unpredictable performance impacts. In most shops, programs are moved to production and bound there. So they are at the mercy of the DB2 optimizer, which generates access paths on the fly when we Bind or Rebind our programs. Any issues with inefficient access paths are then dealt with in a reactive mode. That is, problems are addressed after the fact.
One of the biggest reasons for not implementing strict change control processes for access paths is the lack of built-in methods for ensuring access path change control discipline. Manually evaluating thousands of packages and tens of thousands of SQL statements is quite impractical. But there are things that can be done to help alleviate this problem, such as implementing bind impact analysis software.
Another problem faced by DB2 shops is the rogue access path. In fact, many organizations avoid regular rebinding because they fear having the optimizer generate an access path that is less efficient than the one currently in use. This is the “if it ain’t broke don’t fix it” school of thought. The biggest problem with this approach is that you are penalizing every program in your subsystem for fear that a program or two may have a few degraded access paths. This results in potentially many programs having sub-optimal performance because the optimizer never gets a chance to create better access paths as the data changes.
Of course, the possibility of degraded performance is real–and that is why this approach has been adopted at some sites. The problem is how to find which statements may have worsened. The ideal situation would consist of having the ability to review the access path changes beforehand to determine if they are better or worse. But DB2 itself does not provide any systematic method of administering access paths that way. There are third-party tools that can help you achieve this though.
The best approach is to perform regular rebinds over time as your data changes. The shorthand for this approach is the five Rs, consisting of the following steps:
1. Real-time statistics (RTS) inspection
2. REORG
3. RUNSTATS
4. REBIND
5. Review the results
We start by checking the RTS information to determine which database objects need to be reorganized. After reorganizing, we run RUNSTATS to gather statistics for the DB2 optimizer to use during the subsequent Rebind. Afterward, we review the results to see what changes were made and to ensure that no rogue access paths were created.
Following the five Rs will help you reach the goal of keeping your access paths up-to-date with the current state of your data. Failing to do this means that DB2 is accessing data based upon false assumptions.
Forward-thinking organizations will adopt a liberal bind/rebind process to ensure optimal access paths based on up to date statistics. Keeping abreast of data changes and making sure that your programs are optimized for the current state of the data is the best approach to DB2 application management. And if you are worried about rogue access paths, consider investing in a third-party tool that can assist with access path changes management issues.
Failing to keep your access paths aligned with your data is a sure recipe for declining DB2 application performance.