DB2 for z/OS: Clearing the Air Regarding CLOSE YES for Table Spaces and Indexes

This is another of my DB2 for z/OS blog entries motivated by a desire to clear up a matter about which there is some misunderstanding in the DB2 community. In this case, the misunderstanding concerns the CLOSE option of the CREATE and ALTER statements for DB2 for z/OS table spaces and indexes.

The confusion here, as I've encountered it, is mainly related to differences between "soft close" and "hard close" of DB2 data sets (table spaces or indexes, or partitions of same if the objects are partitioned). "Soft close" is the term that some people use to describe pseudo-close processing, while "hard close" refers to the physical closing of a data set that had been open and allocated to DB2. The primary point I want to make can be summed up simply: specification of CLOSE YES or CLOSE NO affects "hard close" activity, not "soft close" activity. Information provided below is intended to flesh out and provide context for that point. 

I'll start by explaining what pseudo-close (aka "soft close") is about. Pseudo-close is done largely for the purpose of updating a table in the DB2 for z/OS directory called SYSLGRNX. This table contains information about the time periods (expressed as ranges within the DB2 transaction log) during which DB2 data sets are open and in a read/write state. Why does DB2 record this information? To speed up RECOVER utility jobs. Typically, when RECOVER is run for a table space or index (or partition of same, if partitioned), the job involves recovery "to currency" -- that is, to what would be the current state of the object. In that case, the RECOVER utility restores the most recent image copy backup of the target object, and applies data changes made subsequent to that backup to the object to bring it to currency. These data changes are obtained from the transaction log, and therein we find the benefit of pseudo-close processing. [Note that even if recovery will be to a prior point in time, versus "to currency," if that point in time is "later" than the time of the restored image copy, post-copy changes will be applied as necessary from the transaction log. RECOVER with the BACKOUT option also involves transaction log access, but no image copy restoration.]  

When a DB2 data set that is open for read/write access has gone for a certain interval of time without any data changes, it will be pseudo-closed. That interval of time is determined by the value of two DB2 ZPARM parameters, PCLOSEN and PCLOSET. The former parameter refers to a number of checkpoints (the default is ten), and the latter to a number of minutes (the default is ten), and the pseudo-close action is taken when the first of those thresholds is reached; so, if PCLOSEN and PCLOSET are at their default values, a DB2 data set open for read/write will be pseudo-closed if it goes for 10 DB2 system checkpoints or 10 minutes (whichever happens first) without being updated (with the default pseudo-close parameters in effect, 10 minutes will typically pass before 10 DB2 checkpoints have occurred -- it's unusual for DB2 checkpoints to be separated by less than a minute). When a data set is pseudo-closed, DB2 switches the data set's state to read-only, and records that action (in terms of log RBA, or relative byte address, and -- in a data sharing environment -- log record sequence number, or LRSN, information) in the SYSLGRNX table in the DB2 directory. Though the data set, after being pseudo-closed, is in a read-only state, it is still available for update, and the state will be switched back to read/write from read-only when the next data-change operation (e.g., INSERT, UPDATE, DELETE) targeting the data set comes along -- and that (the switching of the data set's state from read-only back to read/write) is also recorded in SYSLGRNX. If the data set is recovered via the RECOVER utility at some time, RECOVER will get information pertaining to the data set that will indicate portions of the log that can be skipped over during change-apply processing -- if SYSLGRNX information indicates that the data set was in a read-only state between points X and Y (RBA or LRSN values) in the log, there's no need for RECOVER to process that part of the log because there's no way that data-change operations involving the data set will be recorded in that log range. The more of the log that RECOVER can skip over in recovering an object, the sooner the job will complete.

By the way, you can monitor pseudo-close activity for a DB2 subsystem by way of a DB2 monitor-generated statistics long report (or an online display provided by your monitor). In a statistics long report, find the section of information with the heading OPEN/CLOSE ACTIVITY. In that section, find the field with a label like DSETS CONVERTED R/W -> R/O. That shows the rate of pseudo-close actions. As a very rough rule of thumb, if the number of open data sets for the subsystem is somewhere in the vicinity of 10,000, I'm comfortable with a pseudo-close rate of around 20-40 per minute (you'd adjust that range proportionately for smaller or larger numbers of open data sets). 

That, then, is pseudo-close, and it is NOT affected by the specification of CLOSE YES or CLOSE NO for a DB2 data set. The CLOSE specification for a data set does affect "hard close" processing -- that is, the physical closing of data sets that had been open and allocated to DB2. These physical close actions can happen for two reasons -- one of which applies only to a DB2 data sharing environment (and I'm not talking about the data set close actions that are a normal part of shutdown processing for a DB2 subsystem). First, every DB2 subsystem has a limit on the number of data sets that can be open and allocated to the subsystem at one time. That limit is determined by the value of the ZPARM parameter DSMAX. If the number of data sets open and allocated to a DB2 subsystem reaches the DSMAX limit, DB2 will physically close some of those open data sets, starting with those that are a) defined with CLOSE YES and b) have gone the longest time without being accessed. If the DSMAX limit is reached for a subsystem and there are no open data sets defined with CLOSE YES, or if DB2 needs to close a number of open data sets beyond those defined with CLOSE YES, some data sets defined with CLOSE NO that have gone a long time without being referenced will be physically closed. Thus, CLOSE YES provides a means whereby you can indicate to DB2 that, in the event of the DSMAX open data set limit being reached, certain data sets are to be candidates for physical closing before other data sets are so considered. With this said, I'll note that my preference is to have DSMAX set to a value that is either not reached, or reached infrequently. More information about DSMAX, including how associated "hard close" activity can be monitored for a DB2 subsystem, can be found in an entry I posted to this blog a few months ago.

In a DB2 data sharing environment, there is an interesting relationship between "soft close" and "hard close" activity. Imagine that you have a 2-way DB2 data sharing group comprised of subsystems DB2A and DB2B. Suppose that data set X is being updated ("update" meaning "changed" -- via INSERT, UPDATE, and/or DELETE) by processes running on both DB2A and DB2B. If data set X goes for a pseudo-close interval without being changed on DB2B, it will be pseudo-closed on that member, and its state will be changed to read-only. If another pseudo-close interval goes by without any access at all (no read, no nothing) to data set X from DB2B, data set X will be physically closed on DB2B if it is defined with CLOSE YES. This physical closing of data set X on DB2B could result in DB2A getting an exclusive page set P-lock (physical lock -- used for data coherency versus access concurrency purposes) on the data set, causing it to become non-group-buffer-pool-dependent. That would reduce data sharing overhead by eliminating, from the system on which DB2A is running, group buffer pool-related coupling facility requests that would otherwise be required as part of accessing data set X.

So, in a DB2 data sharing environment, should objects be defined with CLOSE YES or CLOSE NO? There is, as you might expect, no one-size-fits-all answer to this question. CLOSE YES could be a good choice for an object if you think it likely that there will be significant stretches of time during which the object (or a partition thereof, if it is a partitioned table space or index) will be accessed exclusively by a process or processes running on a single member of the DB2 data sharing group (meaning, no access from other group members). In that case, CLOSE YES could enable acquisition by the one DB2 member of an exclusive page set P-lock on the object (or partition thereof). During the time interval in which that exclusive page set P-lock is held by the DB2 member, a lot of group buffer pool accesses could be eliminated, and that would be good from a CPU efficiency perspective. On the other hand, if you think that it would be unlikely for objects to be accessed for any significant period of time solely from one member of the data sharing group, CLOSE NO could be a better choice. Why? Because if there is a lot of pseudo-close activity going on in the data sharing group -- that is, if data sets are quite frequently being switched by DB2 members to a read-only state and then, very shortly, back to a read-write state -- then CLOSE YES could result in data sets frequently going from group buffer pool-dependent to non-group buffer pool-dependent and back again, and that could drive a lot of coupling facility activity (due to things like large-scale page registration and group buffer pool write activity). Ultimately, the CLOSE YES / CLOSE NO decision in a data sharing environment comes down to knowing how a given database object is likely to be accessed by processes running on various members of the DB2 data sharing group.

And there you have it. CLOSE YES has an impact on DB2 data set "hard close" (physical close) activity, but not on "soft close" (pseudo-close) activity, though in a data sharing environment CLOSE YES can lead to the physical closing of a data set as a result of a pseudo-close action. I hope that this information will be helpful to you.