Successful Internet-based electronic businesses continually face the challenge of managing large increases in transaction workloads while maintaining high levels of application availability. CheckFree Corp. is no exception to this rule. The company recently extended the scalability and availability of its DB2 for OS/390 enterprise data server by implementing a DB2 data sharing group on a System/390 parallel sysplex.

For CheckFree, data sharing was an obvious choice. To stay ahead of the demand curve, we needed to provide order-of-magnitude scalability for Genesis, the DB2-based system that drives our Internet-based electronic billing and payment services. With data sharing, the power of up to 32 System/390 servers can be applied to a single DB2 database. In addition to greater scalability, DB2 data sharing delivers improved availability. Planned outages are greatly reduced because individual data sharing group members can be taken down for maintenance and brought back up without disrupting the application workload. Data sharing also reduces the scope of unplanned outages, with automatic redistribution of incoming transactions should a server fail in the parallel sysplex.

CheckFree executive management was well aware that the DB2 data sharing scalability and availability benefits were not free. One cost was the time and effort required to implement a data sharing group. In our case, the period from initial preparation to production implementation spanned about seven months. Another cost was extra overhead because CPU time per SQL statement execution is higher in a DB2 data sharing environment. Efficiency was just as important to CheckFree as scalability and availability, so our data management team identified and implemented several actions to reduce data sharing overhead. My intent in writing this article is to describe what we did.

To see if your data sharing tuning efforts are producing the results you want, you need to have a basis for a before and after comparison. My preferred yardstick is in-DB2 task control block (TCB) time per SQL data manipulation language (DML) statement execution. This information, captured in DB2 accounting trace class 2 records, is obtainable via a DB2 monitor accounting detail report (or an online DB2 monitor display of thread detail information). At CheckFree, we like to generate accounting detail reports with data grouped by connection ID because we like to have one report per DB2-connected CICS application-owning region (AOR). The calculation of in-DB2 TCB time per SQL DML statement is simple:

A FETCH statement is given a weight of 0.1 in this formula because FETCHes are very inexpensive relative to other SQL DML statements (inexpensive, but not free, so you have to take them into account). A PREPARE is given a weight of 10 because the CPU time required to prepare a relatively simple SQL statement can be five to 10 times that required for statement execution. (I use 10 instead of five because I like to be conservative.) Once you've calculated average in-DB2 TCB time per SQL DML statement, you have your starting point to track the progress of your DB2 data sharing tuning efforts. When you take an action to reduce data sharing overhead, calculate in-DB2 TCB time per SQL statement again to see if you improved data sharing workload CPU efficiency.

Initially, we calculated in-DB2 TCB time per SQL statement in the non-data sharing environment to measure the difference in SQL statement execution costs. A few days after activating two-way DB2 data sharing in the production environment, we analyzed our monitor data and saw more overhead than we wanted to see. Data sharing overhead is largely a function of the coupling facility (CF) request volume generated by a DB2 workload and the average time required to service synchronous CF requests. When we checked our MVS monitor CF activity reports, a large percentage of CF requests were directed to the DB2 lock structure. We decided to reduce our DB2 global lock volume by using selective partition locking.

In a DB2 data sharing environment, the term logical lock, or L-lock, refers to a lock in a non-data sharing DB2 subsystem (data sharing introduces a new kind of lock called a physical lock — you can learn more on this subject by checking out my article, DB2 for OS/390 Data Sharing Locking, Spring 1999, available online at www.db2mag.com). L-locks are further subdivided into two categories: parent and child. Parent locks are basically tablespace-level locks, and requests for such locks almost always go to the DB2 lock structure. In other words, these locks are almost always propagated to the lock structure. Child locks are those taken at the row or page level, and these may or may not be propagated to the lock structure, depending on the existence and type of inter-DB2 parent L-lock conflict.

Inter-DB2 parent L-lock conflict occurs when a program running on one member of the data sharing group has an X-type lock on a tablespace (probably IX), while another program on a different member of the group holds an S-type (usually IS) or an X-type lock (usually IX) on the same tablespace. With selective partition locking, a DB2 Version 5 enhancement activated via LOCKPART YES on a CREATE or ALTER TABLESPACE statement, a program accessing a partitioned tablespace will get a parent lock only on the partition or partitions being accessed, as opposed to a parent lock on the entire tablespace. This reduces inter-DB2 parent L-lock conflict.

The best way to explain this is with an example. Suppose that you don't use selective partition locking in a data sharing group. A program running on member DB2A in the group is updating data in partition 1 of tablespace XYZ. Another program, running on member DB2B, is updating data in partition 10 of the same tablespace. Thus, X-type parent locks are held on the same tablespace by programs running on two different members of the data sharing group. That's inter-DB2 parent L-lock conflict.

As a result, all page-level locks requested by the two programs (assuming that the tablespace is defined with LOCKSIZE PAGE) are propagated to the DB2 lock structure in the CF. If selective partition locking is in effect, the program running on DB2A will acquire a parent lock on partition 1 of the tablespace, and the program running on member DB2B will acquire a parent lock on partition 10 of the tablespace. The parent locks on the two different partitions do not conflict with each other, so there is no inter-DB2 parent lock conflict. That being the case, no page-level locks acquired by the two programs will be propagated to the lock structure.

After we activated selective partition locking at CheckFree, the proportion of our local locks propagated to the lock structure dropped considerably, as did our data sharing overhead. We discovered this by comparing the ratio of global lock requests to local lock requests in before and after DB2 monitor accounting detail reports.

Having reduced data sharing child lock propagation, we next sought to reduce the data sharing lock contention levels on our system. This was a logical next move because global lock contention has a significant impact on the data sharing workload CPU efficiency. Our approach involved two steps. First, we dramatically increased our rate of CICS-DB2 thread reuse by specifying some protected entry threads for our highest-volume transactions. (You can find more information on this subject in one of my previous columns CICS DB2 News You Can Use, Fall 1999, available at www.db2mag.com.) Second, we rebound programs executed via these transactions with RELEASE(DEALLOCATE). Our goal was to reduce tablespace lock requests because we knew that in a typical DB2 data sharing system, a large percentage of the global lock contention events are associated with tablespace lock requests. RELEASE(DEALLOCATE) causes tablespace locks, once acquired, to be retained until thread deallocation. We predicted that reducing tablespace lock requests would result in lower levels of data sharing lock contention. This is, in fact, what we achieved at CheckFree. We verified these results by comparing global lock contention rates using our MVS and DB2 monitors.

Note that while the combination of CICS-DB2 protected threads and RELEASE(DEALLOCATE) is great for reducing DB2 data sharing lock contention, you need to proceed with caution when taking this route. Why? Because along with a reduction in tablespace lock activity, you can expect to see an increase in environmental descriptor manager (EDM) pool utilization. (RELEASE(DEALLOCATE) causes the EDM pool control blocks associated with a program to be retained until thread deallocation.) If you use too much of your EDM pool space, programs can fail with an "unavailable resource" SQL error code. Use your DB2 monitor to check EDM utilization. If the percentage of free pages is less than 10 percent of the pool size, I recommend that you increase the size of your EDM pool before you start protecting threads and rebinding programs. Even if the percentage of free pages in your EDM pool exceeds the 10 percent level, take it easy. Rebind a few programs at a time, and monitor those EDM pool usage statistics. Keep in mind that prior to DB2 for OS/390 Version 6, a database descriptor (DBD) had to be stored in contiguous EDM pool pages. If the MODIFY and REORG utilities are not run on a regular basis for a tablespace, the associated DBD can get to be really large (as in several hundred kilobytes). Thus, even with a percentage of free pages that exceeds 10 percent of your pool size, RELEASE(DEALLOCATE) and protected threads can result in EDM pool fragmentation. In a DB2 Version 5 (or prior release) environment, this could prevent the loading of a large DBD, so keep those DBDs small or be willing to have a big EDM pool to avoid "EDM pool full" failures. With DB2 Version 6, DBDs are stored in 32KB "chunks," and the chunks used to hold a particular DBD do not have to be contiguous. This enhancement should reduce the incidence of "EDM pool full" failures.

Data sharing tuning actions are not limited to those that impact global lock activity. In analyzing CF activity reports, we noticed that average service times were surprisingly high for synchronous requests to the DB2 lock structure and group buffer pools (GBPs). With 100-MIP engines in our S/390 servers and in our CFs, and fast CF links, we had expected average synchronous request service times to be in the 50 to 70 microsecond range for lock requests and in the 70 to 100 microsecond range for GBP requests. You don't want it to take too long to service synchronous CF requests because longer synchronous request service times mean more dwell time (dwell time refers to time that a S/390 server engine driving a CF request spends waiting on that request to complete a time during which the engine can do no other work). More dwell time means more data sharing overhead.

When investigating our high CF synchronous request service times, we saw that our CF utilization was not overly high and that our link contention was about nil. We also knew that our CF logical partitions (CF LPARs) had dedicated central processors. So why the long synchronous request service times? Noticing the high standard deviation figures reported with the average service times, we theorized that the problem might be symptomatic of an overabundance of long-running CF requests. Such requests can be generated as a result of data sets going into and out of GBP dependency. A DB2 data set is GBP-dependent if it is open on more than one member of the data sharing group, and open for read/write on at least one member of the group. One thing that can cause a GBP-dependent data set to become non-GBP-dependent is pseudo-close. We checked our DB2 monitor statistics reports. Sure enough, our rate of pseudo-close activity was quite high. We experienced about 50 data sets pseudo-closed per minute on average (the field to check is "data sets converted from read/write to read-only," in the "data set open/close activity" section of the report).

Pseudo-close is a good thing from a recovery/restart perspective, but you can have too much of a good thing. The level of pseudo-close activity on a DB2 subsystem can be increased or decreased through the PCLOSEN and PCLOSET parameters of DSNZPARM. The default parameter values are five and 10, respectively. Here is what that means: if a DB2 data set that has been updated (for example, via UPDATE, DELETE, and/or INSERT) goes for five DB2 checkpoints or 10 minutes whichever occurs first with no update activity, it will be pseudo-closed. At that time, changed-but-not-externalized pages belonging to the data set will be written to DASD, the DB2 directory table SYSLGRNX will be updated (indicating the end of a range in the DB2 log during which the data set was open for update), and the data set will be switched to a read-only state (until the next data-changing SQL statement comes along, at which time the data set will be automatically switched back to a read/write state).

It is the switch to read-only (and subsequent switch back to read/write, if this occurs) that affects GBP dependency. If you have too many data sets going into and out of GBP dependency, the resulting CF activity may negatively impact synchronous CF request service times.

We decided to change the value of the PCLOSEN parameter of DSNZPARM from five to 20, and the value of PCLOSET from 10 to 40. As expected, we saw pseudo-close activity decrease from around 50 data sets pseudo-closed per minute to about 10. This is a more comfortable level. Remember, you want some pseudo-close activity —you just don't want too much. The effect on average service times for synchronous requests to the DB2 lock structure and group buffer pools? These went right down to where we'd originally expected them to be, with a big drop in the standard deviation values reported by our MVS monitor. Note that DB2 for OS/390 Version 7, recently announced by IBM, will address the issue of high standard deviation values in CF request service times by taking advantage of a CF function called "name class queues." Until you get to DB2 Version 7, keep an eye on your pseudo-close activity. Actually, even after you get to DB2 Version 7 you should keep the pseudo-close rate at a reasonable level to optimize the efficiency of your DB2 system.

DB2 data sharing and S/390 parallel sysplex are complex technologies, but don't let that fact dissuade you from getting in there and tuning your data sharing system. Remember, data sharing overhead is basically a function of the number of CF requests generated by a workload, and the average time required to service synchronous CF requests.

In this column I've described a few of the things that we did at CheckFree to reduce CF request volumes and synchronous CF request service times. These tuning actions could yield positive results at your site, and you may come up with data sharing tuning tips of your own. In any case, don't just settle for a data sharing system that works. Go for one that runs efficiently. You'll appreciate the scalability and availability benefits of your data sharing group even more knowing you've done your part to deliver these goods in a low-cost, low-overhead way.