Rebinding for Optimal DB2 Access Paths

The access paths formulated by the DB2 optimizer during the BIND and REBIND processes are critical to your application performance. It is these access paths that determine not only how DB2 data is accessed by your program, but how efficiently it is accessed. Whether you are preparing a new program, implementing changes into your existing DB2 applications, upgrading to a new version of DB2, or simply trying to achieve optimum performance for existing applications, an exhaustive and thorough REBIND management policy should be of paramount importance.

However, many organizations are not doing everything possible to keep access paths up-to-date with the current state of their data. So what is the best practice approach for rebinding your DB2 programs? The answer is “The Five R’s.” This methodology of regular rebinding followed by a review of your access paths required the following steps:

1.       Start with an inspection of the RTS (Real Time Statistics) to determine which objects need to be reorganized.
2.       Follow that up by running a REORG on table spaces and indexes as appropriate based on the statistics.
3.       After reorganizing, run RUNSTATS (to ensure the DB2 Catalog is up-to-date).
4.       Follow that with REBINDs of your programs.
5.       Then we need that fifth R – which is to review the access paths generated by the REBIND.

For shops that have avoided rebinding for years this approach represents a significant change. So what new DB2 features are available to help? Well, several releases ago, back in DB2 9 for z/OS, plan stability was added. This feature enables you to save a backup version of your access paths as a precautionary measure. If any of the new access paths after rebinding are less efficient, the DBA can switch back to the backed up access paths. In order to implement this level of stability you can use the PLANMGMT parameter of the REBIND command. There are three options: NONE, BASIC, and EXTENDED. BASIC saves the previous access paths, and EXTENDED saves the previous and an original. You can use REBIND and the SWITCH parameter to revert back to the saved access paths when the new access paths cause degraded performance. 

As of DB2 10 for z/OS you can tell DB2 to try to reused previous access paths for SQL statements whenever possible. This is called access path reuse and is implemented using the APREUSE bind option. When invoked, DB2 uses information about the previous access paths to create a hint.

When BIND PACKAGE or REBIND PACKAGE specifies APREUSE(ERROR), DB2 tries to locate the access path information from a package that has a matching identity. If no such package exists, DB2 tries to locate another recent version of the package that has the matching location, collection ID, and name. The APREUSE option only applies to statements that have identical statement text in both packages. Newly added statements and statements with text changes never reuse previous access paths.

Reusing access paths can help to minimize administrative and performance issues involved in rebinding.

Of course, there are products on the market which can be used to implement a proactive approach to rebinding. These products preview the new access paths and then run them through a rules system to determine if the new access will be improved, unchanged, or degraded. With this information we can rebind everything that would improve and avoid rebinding anything else until we can analyze the cause of the degradation. Using such an approach you should not have degraded access paths sneaking into your production environment.

Summary

At any rate, a systematic approach to DB2 binding and rebinding is necessary to assure optimal performance within your DB2 applications. This short blog entry covers some of the basics and recent changes to DB2 in this area. 

Be sure to take your time and to plan your statistics-gathering and bind/rebind approach... or be ready to be in constant firefighter mode as you address poorly-performing SQL in your applications!

Those Good Old IBM Mainframe Utility Programs

Most mainframe programmers are aware that IBM supplies many utility programs that are useful for system maintenance chores such as copying and deleting files, manipulating partitioned data sets, and the like.  

These utilities typically begin with an IEB, IEF, or IEH prefix.  One of the most common of these is IEFBR14, which is used to catalog a data set.  But few people are aware, however, that IBM also supplies many other utilities that can make the life of a programmer much easier.  Below is a select list of these:

IEBMAGIC        fixes any problem without having to use your brain; perfect for consultants and contract programmers

IEBIQUIT         automatically updates your resume, writes a letter of resignation, forwards it to your boss, and prints reams of paper to make it look like you actually did something when you worked here

IEBIBALL         compares any source file to the last one that actually worked displaying all changes and who made them; perfect tool for technical support personnel overwhelmed by programmers chanting the phrase "I didn't change anything"

IEBPANIC         if all else fails, run IEBPANIC; sometimes it fixes your problem and sometimes it doesn't, but it never tells you how it did it; companion program to IEBMAGIC;

IEBNOTME      alters all trace of your userid from system change logs, SMF, etc.; useful to eliminate finger-pointing; this should always be run before running IEBIQUIT

IEFINGER        when designing on-line systems sometimes intermediate screens are sent that serve no purpose other than to tie together a series of related transactions; these intermediate screens generally direct the user to "Press ENTER to Continue"; IEFINGER simulates the end user pressing the ENTER key thereby eliminating unnecessary screens

IEHAMMER      forces a square peg into a round hole; for example, if you try to compile a COBOL program using the FORTRAN compiler, attaching IEHAMMER to the compile will make it work

IEBPIG               finds all unused resources of any type and assigns them to any specified job

IEBHAHA         randomly changes source code in test libraries; it is often speculated that IEBHAHA is the cause of most program problems that IEBIBALL is used to correct

IEBEIEIO          run this utility when you have too many problems to handle for one person;  it corrects the old "with an abend here, and a meeting there, e-i-e-i-o" syndrome by causing a system problem so large (in someone else's application) that all direction is diverted away from you to them

So did I forget your favorite?  Drop a comment below to share it!

The Twelve DBA Rules of Thumb... a summary

Over the past couple of months this blog has offered up some rules of thumb for DBAs to follow that can help you to build a successful and satisfying career as a DBA. These twelve rules of thumb worked well for me as I worked my way through my career and I have shared them with you, my faithful readers, so that you can benefit from my experiences. I hope you find them useful... and if I have missed anything, please post a comment with your thoughts and experiences on being a good DBA.

As a reminder of what we have discussed, I am posting a short synopsis of the Twelves DBA Rules of Thumb here, along with links to each blog post.

 1. Write Down Everything
 2. Automate Routine Tasks
 3. Share Your Knowledge
 4. Analyze, Simplify and Focus
 5. Don't Panic!
 6. Be Prepared
 7. Don't Be a Hermit
 8. Understand the Business, Not Just the Technology
 9. Ask for Help When You Need It
10. Keep Up-to-Date
11. Invest in Yourself
12. Be a Packrat


Good luck with your career as a DBA...

DBA Rules of Thumb - Part 12 (Be a Packrat)

Today's post in the DBA Rules of Thumb series is short and sweet. It can be simply stated as "Keep Everything!"

Database administration is the perfect job for you if you are a pack rat.

It is a good practice to keep everything you come across during the course of performing your job. When you slip up and throw something away, it always seems like you come across a task the very next day where that stuff would have come in handy... but you you threw it out!

I still own some printed manuals for DB2 Version 2. They are packed up in a plastic tub in my garage, but I have them in case I need them.

DBA Rules of Thumb - Part 11 (Invest in Yourself)

Most IT professionals continually look for their company to invest money in their ongoing education. Who among us does not want to learn something new — on company time and with the company’s money? Unless you are self-employed, that is!

Yes, your company should invest some funds to train you on new technology and new capabilities, especially if it is asking you to do new things. And since technology changes so fast, most everyone has to learn something new at some point every year. But the entire burden of learning should not be placed on your company.

Budget some of your own money to invest in your career. After all, you probably won’t be working for the same company your entire career. Why should your company be forced to bankroll your entire ongoing education? Now, I know, a lot depends on your particular circumstances. Sometimes we accept a lower salary than we think we are worth because of the “perks” that are offered. And one of those perks can be training. But perks have a way of disappearing once you are "on the job."

Some folks simply abhor spending any of their hard-earned dollars to help advance their careers. This is not a reasonable approach to your career! Shelling out a couple of bucks to buy some new books, subscribe to a publication, or join a professional organization should not be out of the reach of most DBAs.

A willingness to spend some money to stay abreast of technology is a trait that DBAs need to embrace. 

Most DBAs are insatiably curious, and many are willing to invest some of their money to learn something new. Maybe they bought that book on NoSQL before anyone at their company started using it. Perhaps it is just that enviable bookshelf full of useful database books in their cubicle. Or maybe they paid that nominal fee to subscribe to the members-only content of that SQL Server portal. They could even have forked over the $25 fee to attend the local user group.

Don’t get me wrong. I’m not saying that companies should not reimburse for such expenses. They should, because it provides for better-rounded, more educated, and more useful employees. But if your employer won’t pay for something that you think will help your career, why not just buy it yourself?

And be sure to keep a record of such purchases because unreimbursed business expenses can be tax deductible.