You are currently on IBM Systems Media’s archival website. Click here to view our new website.

Bookmark and Share

Recent Posts

Parameter Places Retry Claims on Stopped Objects

May 23, 2017

This should be obvious to anyone who reads this blog, but each DB2 release has literally dozens of new features and enhancements. I write about them on a weekly basis, but occasionally even I miss something. For instance, I recently learned about a new parameter introduced in DB2 10 that's designed to improve productivity (and reduce frustration) in your test and development environment.

To understand this parameter requires some basic knowledge of DB2 table space storage. This DB2 12 Knowledge Center document covers DB2 for z/OS concepts on storage structures for DB2 table spaces:

A DB2 table space is a set of volumes on disks that hold the data sets in which tables are actually stored. Every table is stored in a table space.

Now about this (fairly) new parameter: Years ago, in my DBA days, I dreaded those calls from developers about sqlcode -904, which was accompanied by the error message reason code 00C90081: “An attempt was made to allocate a resource that is stopped for all access.”

If you've never encountered this previously, figuring out what's going on isn't easy. After doing some research I learned that storage management rules figured prominently in this issue. Of course all shops establish such rules. For many, table space is automatically migrated from disk storage to tape storage whenever a dataset goes untouched for a set amount of time?say, 24 hours. Naturally I'm talking about test or development environments. I wouldn't expect to see this happening in production.

In any event, the problem should be pretty apparent now: datasets go a day or more without being accessed all the time. So if datasets weren't restored, DB2 would fail and return sqlcode -904 back to the application. Prior to DB2 10, developers would re-execute the application. It would then run without an error provided the storage management system had restored the dataset. And to preemptively combat this issue, DBAs would setup daily runstats jobs that did nothing more than access every table space to keep them from being migrated. While this did its job, maintaining table spaces on disk just to avoid application outages was, as you can imagine, a pricey proposition.

But the new online system parameter delivered in DB2 10 provided a far more effective solution. It enables DB2 on failure to acquire a retry claim on the given dataset. If the dataset is not available, DB2 will continue to retry a claim against the stopped object up to the IRLM timeout limit before rejecting the request. Once the dataset is restored and the claim acquired, DB2 continues and the application requesting data processes without a failure. For more about APAR PI36922, go here.

If you've been using this feature, please share your experiences in comments.

Posted May 23, 2017| Permalink