Minimizing Lock Contention Issues

I frequently get e-mails with DB2 questions and I plan to start posting answers to some of the more common ones up here.

One issue that comes up a lot is dealing with locking issues. Usually it is posed by someone who is experiencing timeouts in an online environment and they want to know how to minimize them. Here is some guidance.

When you experience a timeout, it means that another process holds a lock on the data that you are trying to modify. So, it stands to reason that you should try to minimize the duration of locks that are being held in your system. There are some approaches you can take to achieve this, but for the most part, they require programming changes.

First of all, make sure that all of your batch processes -- especially any that run during the same timeframe, but really all batch process -- have a COMMIT strategy. This means that your programs should issue a COMMIT after processing "a set number of" inserts, updates, and deletes. A COMMIT will tell DB2 to make the changes permanent and releases locks.
A good approach is to set a counter that is incremented after every modification. Then, check it and when it exceeds a predefined threshold -- say 25 or 50 or 100 modications -- then issue a COMMIT. You should make the threshold an input parameter so that you can change it as the workload in your system changes. For example, make it 25 when concurrent activity is high, but ramp it up to 100 or higher when it is low. Failure to issue COMMITs will result in timeouts, as well as possibly deadlocks and lock escalation.

(Also, please note that these are just sample numbers and not necessarily the correct numbers to start with at your shop.)

A good article to read regarding COMMIT strategies is "The Woes of Commitment" by Bonnie Baker.

Next, look at all of your programs, batch and online, and try to save the data modification statements to as close to the COMMIT as possible. By saving the data modification until right before you issue a COMMIT, you reduce the overall average lock duration. This should result in reducing contention and therefore, the number of timeouts.

If you want to investigate the timeout details, be sure to examine the statistics trace class 3 and IFCID 0196 for timeouts (IFCID 0172 is for deadlocks).

Help Defining DB2's Business Value

Every now and then those of us working on mainframe platforms are confronted by folks who are looking to eliminate mainframes. This can happen for any number of reasons. Maybe the confronter view mainframes as “old technology,” or “mainframes are too costly,” or some other trumped up charge. Too often, however, we respond to such claims emotionally instead of with reason and knowledge.

Fortunately, IBM has come up with some help for those of us working with DB2 for z/OS. IBM has published – and made available for free download – a great little manual titled The Business Value of DB2 for z/OS. In this book you can learn all about the ROI that mainframe DB2 can offer your organization. Instead of all of those technical details that DBAs tend to savor, this document will assist you in analyzing and communicating the many business benefits provided by the combination of DB2 running on z/OS.

The book covers topics such as up-to-date details on how DB2 can integrate with, and enable sharing of information across multiple platforms. It discusses how the mainframe scales to manage large volumes of data. And the manual also describes how IBM’s on-going “on demand” initiative has improved the manageability of DB2.

If you use DB2 for z/OS, you should download a copy of this redbook. It is free, after all. And the next time that someone challenges the mainframe as an on-going, viable platform for business computing you will have a powerful, unemotional tool to help battle that misconception.

The Aging Mainframer

A continuing, lingering perception that the mainframe is dead continues on in some parts of the IT industry. It seems that we constantly hear that big IT shops are getting rid of their mainframes. But rarely do we ever hear about it after the fact. No, it is usually reported right when someone thinks that it is a good idea.

Now don't get me wrong. I'm sure there are some shops that have removed their mainframe. But I'm also sure that there are many more that thought about it but couldn't do it -- as well as those who wouldn't even consider it.

A bigger problem for the mainframe than the misguided notion that it is more costly than other computing platforms is the aging of the mainframe workforce. This is a reality. If you don't believe me, go to a SHARE conference and fix your eyeballs on some of the dinosaurs attending mainframe sessions there (myself included).

Basically, the problem is that mainframe experts are getting older and slowly retiring. And who will replace them? Most young IT professionals do not choose to work on mainframe systems, instead choosing to concentrate on the latest technology bandwagons -- things like Windows and Linux, open source and so on. Put one of these newbies in front of a terminal and introduce them to the joys of JCL, ISPF and COBOL, then watch them scream out the door yelling "I want my Java!" (And who can blame them?)

But this is actually an inaccurate perception. You see, mainframe no longer means ugly old green screens. Today's mainframe environment is quite different from the mainframe of yesteryear. That hulking, water-cooled beast you may remember has been replaced with chip-based, CMOS, air-cooled systems. Today's mainframes are easier to hook together using Parallel Sysplex technology. And all of the "modern" technology used on Windows and Linux platforms works on the mainframe, too. Yes, that means XML, TCP/IP, Java and so on all work on the mainframe, too.

Nowadays, the biggest mainframe "problems" are training and PR. Let's focus on training first. Mainframe technology is not taught by most universities these days; this really needs to change. What is needed is a comprehensive educational program delivered through major universities, as well as IT-focused institutions like DeVry and NorthFace universities. The program should be sponsored by major mainframe vendors, which could provide hardware and software, as well as a conduit for hiring graduates. Actually, IBM is doing something just like this nowadays. An ongoing mainframe program in the universities will help to further promote and extend the mainframe. And that is goodness.

And why would universities be interested in such a program? Employability of their graduates! As the current crop of mainframe experts retire, companies will have to replace them. I'd venture to guess that 10 years or so down the line, it will be easier for an IMS DBA, for example, to get a job offer than an Oracle DBA. The demand will be greater for the IMS talent because the supply is so low.

The publicity component is a bit more difficult. So much has been written and implied about the mainframe being dead that a lot folks believe it. But the mainframe continues to be a robust, viable component of today's IT infrastructure. Organizations continue to add more MIPS, deploy more applications and run their most important, mission-critical applications on mainframe computers. Until this aspect of the mainframe is publicized more, the existing perception is likely to linger.

Or maybe we should just give the mainframe a new name and pretend that it is a new technology with better availability, scalability and performance than the existing platforms - how about a name like the "AlwaysAvailable"?

This posting is a slightly revised version of a piece I wrote for Search390.com.
If you'd like to read the original, click here (registration required).