Claims and Drains on the Main(frame)

It's nice when knowledge gained some time ago becomes useful again in a new context. Take DB2 for z/OS drain locking. This technology was delivered in the mid-1990s, I think via DB2 V3 - though it may have veen V4. In any case, drains - and their counterpoint, claims - were initially spoken of primarily in the context of DB2 utility operations (more on this momentarily). Just last week, I had the opportunity to share some of what I know about drains and claims in responding to a question about EXCHANGE, a new SQL statement associated with the clone table functionality of DB2 for z/OS V9. Basically, EXCHANGE can be used with a clone table to effect a very quick (usually) replacement of a table's contents with new data by way of the DB2 SQL interface - this in contrast to the traditional utility-driven LOAD REPLACE approach. The person asking the question wanted to know of the locking and concurrency implications of EXCHANGE, and that prompted the exchange of information about drains and claims. As the question was posted to the popular DB2-L forum, several other DB2 experts chimed in with related and very useful comments. I'll cite these as I go along.

First, a bit of background. A number of DB2 for z/OS utilities require some degree of exclusive access to a target tablespace during one or more phases of their execution - either total exclusivity (no other programs can read data from, or update data in, the tablespace), or write-exclusivity (concurrent read access by other programs is permitted, but concurrent data-change access is not). In the early years of mainframe DB2, this exclusive access was secured through the acquisition of tablespace locks. This was problematic when an application process held a tablespace lock for a long period of time, thereby preventing the utility from acquiring the lock it needed to operate on the targeted database object. Quite often, the contention was not between a utility and a long-running batch job; rather, it was between a utility and one or more threads used by DB2-accessing online transaction programs. A batch workload tends to be fairly predictable: you know when jobs will start (especially when they are submitted through a job scheduling system), you know how long they usually run, and you know which tables (and, therefore, tablespaces) they access. You can schedule utilities around them.

An online transaction-associated thread can be a different matter when 1) it is reused by many transaction programs and 2) some of those programs are bound with RELEASE(DEALLOCATE). The first of these factors leads to thread persistence, and the second (in combination with the first) enhances CPU efficiency (as described in a previous post to this blog). In particular, there are plenty of sites with high-volume CICS-DB2 transaction workloads that aim for high levels of thread reuse (aided by the utilization of so-called protected entry threads) and have a lot of DB2-accessing CICS programs bound with RELEASE(DEALLOCATE). In former times this could lead to utility lock-out situations because a given CICS-DB2 thread could persist for hours (or even days for a 24X7 transactional workload), and tablespace locks acquired by programs executed through the thread and bound with RELEASE(DEALLOCATE) would not be released until the thread was terminated (while a utility might be locked out in that case, other application processes typically would not be, as user programs almost always get non-exclusive locks on tablespaces). Drains to the rescue!

The drain locking mechanism enabled DB2 utilities (and certain commands) to "break in" on a persistent (i.e., long-lived) thread holding one or more tablespace locks, even if those locks were acquired for programs bound with RELEASE(DEALLOCATE). Here's why: a DB2 application process has to acquire a read or a write claim on an object to be accessed, and that claim will be released (and must subsequently be reacquired if needs be) at each commit point, regardless of the RELEASE option specified at program bind time. When a DB2 utility or command issues a drain lock request for an object (such as a tablespace), no new claims can be acquired on that object by application processes. When claims already held at the initiation of the drain process are released in the course of commit activity, the drain lock is obtained and the utility or command can proceed with execution. When execution is complete and the drain lock is released, application processes can again acquire claims on the target object (note that some drains affect only data-change activity and allow a continuance of read access, while other drains affect all claimers).

So, what does this have to do with the EXCHANGE statement? Well, when the statement is issued, DB2 will drain both the base table and the corresponding clone table (the one that will be switched with the base table to achieve what appears to application programs to be a table-content replacement). If frequent commits are the rule in the DB2 environment, the drain locks should be acquired in short order, the switch will occur (also quickly), and program access to the "new" base table (formerly the clone table) can resume (again, there is no visibility at the program level of the fact that access after the EXCHANGE operation is to a physically different table - it just appears that it's the same table with new content). Workload disruption should be minimal. Suppose, though, that the drain locks on the base and clone tables can't be acquired because an application process has a claim on the base or the clone table and does not release that claim through a commit? Because the drain process (as previously mentioned) prevents new claims from being acquired on a target object, you are now looking at a potentially very noticeable workload interruption for some users. That's something to consider. If you want to use EXCHANGE (and it is definitely a cool feature of DB2 V9), you might want to review your DB2 application workload to ensure that programs commit frequently (this is, of course, most often an issue for programs that run in batch mode). In doing that, don't overlook read-only programs - they need to commit to release claims, just as data-changing programs do. If certain long-running programs don't issue frequent commits, and if they can't be changed to do so (or if they commit frequently but utilize cursors defined WITH HOLD, as explained below), you'll need to schedule EXCHANGE operations accordingly if said programs access tables named in the EXCHANGE statement.

Now, to give associated credit where it's due, as promised (referring to people who contributed useful information to the DB2-L thread that sparked this blog entry):

• Phil Grainger, DB2 expert and Senior Principal Product Manager at CA, noted that if an EXCHANGE operation is to be minimally disruptive in terms of application access to affected data, it must be committed or rolled back in a timely manner. So, the program issuing the EXCHANGE relies on the commit frequency of programs that are concurrently accessing the base (and/or clone) table, and those programs in turn rely on the EXCHANGE-ing program to commit following the table switch.
• Peter Backlund, a DB2 consultant based in Sweden, reminded that 1) claims are acquired on database objects even for programs bound with ISOLATION(UR), and 2) claims are NOT released at commit when they are associated with cursors defined using the WITH HOLD option.
• Peter Vanroose, a DB2 specialist at ABIS Training and Consulting in Belgium, followed up on Peter's comment regarding cursors defined WITH HOLD, explaining why we should be GLAD that DB2 behaves this way: in retaining a claim through commits, the WITH HOLD option of DECLARE CURSOR provides a means whereby a programmer can be assured that the content of a table from which a long-running FETCH loop is retrieving rows will not be switched for a clone table's content before the FETCH loop completes.
• Steen Rasmussen, a DB2 expert and Principal Technical Specialist at CA, mentioned that he had delivered a presentation about DB2 for z/OS V9 clone tables at IDUG last year. Steen's presentation is currently available on the IDUG Web site (http://www.idug.org/), in the Premier Technical Library in the Members Access Area of the site (IDUG Premier-level membership is included in the registration fee for IDUG conferences, and is available to others for only $25 per year). In a few months, the presentation will also be available in IDUG's basic Technical Library (basic membership in IDUG is available free of charge). The title of Steen's presentation is "The Clones Have Landed - Watch Out!"

Happy cloning, and remember: release those claims, so that others may drain.