Some of my blog posts are about DB2 things that are very new (my previous entry described the high-performance DBATs introduced with DB2 10 for z/OS), and some cover DB2 stuff that's been out there for a while. This entry is an example of the latter. Dynamic allocation of the sort work data sets used in the execution of mainframe DB2 utilities is something that goes back at least to DB2 Version 8, and a lot if DBAs have taken advantage of this feature to improve the reliability and performance of their utility jobs. At the same time, it seems that there are a number of DB2 for z/OS people who are kind of confused about dynamic allocation of utility sort work data sets, and are uncertain as to how they can leverage this product capability. Seeing as how dynamic allocation of the data sets in question (and not just dynamic allocation, but DB2-directed allocation -- more on that distinction momentarily) is highly recommended by IBM, my aim today is to clear things up for folks who could use some clarification. By the way, I want to give props to Christian Michel, one of the utilities developers at the IBM lab in Boeblingen, Germany, who helped me get my arms around the topic.
So, a little background: a number of DB2 utilities -- including LOAD, REORG, REBUILD INDEX, and RUNSTATS -- use DFSORT (an IBM product that's a feature of z/OS) to handle required sorting of data records (typically index keys). DFSORT of course needs some disk space to do this work, and some time ago you had to allocate those data sets via DD statements in the JCL of your utility jobs. SORTWKnn is the DD name that generally comes to mind first when one thinks of these data sets, but there are others: SW01WKnn, DATAWKnn, DA01WKnn, etc.). The problem with the JCL-directed allocation of these data sets was that you could have a utility job fail if the space available in the DFSORT temporary data sets were insufficient, or the job might not perform optimally if the wrong number of data sets were allocated (this could impede parallelization of sort operations).
To address these challenges, IBM provided new keywords for DFSORT-using utilities (again, I'm not sure if this was introduced with DB2 V8 or a prior release) that would enable DFSORT to dynamically allocate the temporary data sets that it needed to do the sorting required by a utility job. The new keywords were SORTDEVT, which indicates the device type to be used for data sets dynamically allocated by DFSORT, and SORTNUM, which can be used to specify the number of temporary data sets to be allocated by DFSORT for each sort operation performed in the execution of a DB2 utility (the value of SORTDEVT is usually set to a so-called "esoteric," which is a z/OS installation-defined group of devices, and the common specification is SYSDA, because that means magnetic disk storage and it's an esoteric that is almost always defined on a z/OS system).
Dynamic allocation of utility sort work data sets by DFSORT was a step in the right direction, but still the situation was less than ideal (SORTNUM might be set to a sub-optimal value, potentially impeding utility sort parallelization, or the estimate of space needed for sort work data sets might be substantially off due to inaccurate statistics in the DB2 catalog). To address these challenges, DB2 utilities were enhanced via APARs PK45916 (for DB2 Version 8) and PK41899 (for DB2 9) to enable them to dynamically allocate sort work data sets before invoking DFSORT (the enhancements delivered via these APARs are part of the base functionality of DB2 10). In doing this, the utilities would optimize the number of sort work data sets dynamically allocated for a job (thereby removing the need for a user-specified SORTNUM value). On top of that, sort work space requirements would be more accurately estimated utilizing information in the real-time statistics tables.
That's all good (REAL good), but some people were (and still are) uncertain as to what had to be done to realize the benefits of these important DB2 utility enhancements. That, as much as anything, is what I want to address in this entry. Here's the deal:
- Make sure that the value of the DB2 ZPARM parameter UTSORTAL is set to YES (note that YES is the default setting for UTSORTAL in a DB2 10 system).
- Remove SORTNUM specifications from your utility control statements, or set the DB2 ZPARM parameter IGNSORTN to YES to have DB2 ignore any SORTNUM specifications found in utility control statements.
- Remove ALL of the sort-related DD statements from the JCL of your DB2 utility jobs -- that's anything with a WKnn string in it (SORTWKnn, SW01WKnn, DATAWKnn, DA01WKnn, STATWKnn, and ST01WKnn), along with UTPRINnn and DTPRINnn (exception: keep the UTPRINT and SORTDIAG DD statements in your JCL).
- Ensure that real-time statistics information is available to the DB2 utilities. If you are still on DB2 V8 or using DB2 9 in Conversion Mode, you need to create the real-time statistics tables if they have not already been defined on your system (starting with DB2 9 in New Function Mode, the real-time statistics tables are part of the DB2 catalog, and you don't have to be concerned with creating them). If you need to set up the real-time statistics tables in your DB2 environment, refer to Appendix I in the DB2 Version 8 Administration Guide (available in PDF form at this url: https://www-304.ibm.com/support/docview.wss?uid=swg27011659).
Another thing: leave SORTDEVT in your utility control statements, or include a SORTDEVT specification in those statements (referring to the utilities for which SORTDEVT is a valid option: CHECK DATA, CHECK INDEX, CHECK LOB, LOAD, REBUILD INDEX, REORG INDEX, REORG TABLESPACE, and RUNSTATS). You need this for dynamic allocation of sort work data sets, regardless of whether this dynamic allocation is done by DB2 (preferred) or DFSORT.
Finally: some people who have been accustomed to allocating sort work data sets via JCL DD statements might be concerned about loss of control over placement of these data sets in the disk subsystem if they go the dynamic allocation route. After all, SYSDA (again, the typical SORTDEVT specification) is usually very generic ("allocate these on magnetic disk devices"). What do people do to direct dynamically allocated sort work data sets to a particular set of disk volumes? My understanding is that the primary means to this end is the utilization of DFSMS Automatic Class Selection (ACS) routines. These routines would look for the DD names of the sort work data sets being dynamically allocated (as mentioned previously, these contain the string WKnn) and would direct those data sets to an SMS storage group that would be comprised of the volumes you want to be used for the "sort pool." Another option would be to define an esoteric that would include the sort pool volumes and then to use the name of this esoteric instead of SYSDA as the value of the SORTDEVT utility control card option.
OK, so all of this is old news to people who have been taking advantage of DB2-directed dynamic allocation of sort work data sets since the functionality was introduced back in 2008. I'm interested in reaching people who are still allocating DB2 utility sort work data sets the old way. If you're in that group, I'm telling you: the new way (let DB2 allocate the data sets) is the better way -- better for performance, and better for reliability. Give it a shot, if you haven't already.
DB2 10 for z/OS, which became generally available this past October, is loaded with features and functions that can reduce the CPU cost of applications. One of my favorites is high-performance DBATs (DBATs, or database access threads, are associated with SQL statements that are issued by remote requesters using the DRDA protocol and processed via DB2's Distributed Data Facility). I'm big on high-performance DBATs because I'm big on mainframe DB2's use as a super-scalable, super-available, super-secure data server for applications that run on Java, .NET, and other application servers. For DB2 for z/OS to realize its full value-delivery potential in client-server computing environments, it has to provide to DBAs and to systems administrators the same performance management and tuning options that are available for local-to-DB2 applications (such as those that run in CICS regions). This has been happening for a period that stretches back a good 20 years or so. First we got static SQL for client-server applications (back when the DRDA protocol and package bind were introduced with DB2 Version 2 Release 3). With DB2 Version 4 we got stored procedures, a critical enhancement with respect to driving up the scalability and manageability of DB2-based client-server computing (stored procedures themselves have been substantially enhanced since their introduction, with features such as WLM-managed stored procedure address spaces and, more recently, native SQL procedures). Management of DB2 DDF transaction priority through WLM service classes provided a means of setting performance objectives and report classes that could be very granular, with transaction classification possible by way of all kinds of attributes including collection, package, stored procedure, client application name, and user ID.
All this is great, and plenty of organizations are running very high-volume OLTP workloads in mainframe DB2 client-server environments today. That said, there was one thing annoyingly missing from the DDF transaction-tuning tool kit: the ability to leverage the CPU efficiency benefits of packages bound with RELEASE(DEALLOCATE), used in combination with threads that persist through multiple transaction executions. That gap was filled very nicely by the introduction, with DB2 10 for z/OS, of high-performance DBATs.
Before getting into the particulars of high-performance DBATs, I want to do a little illustration by comparison. High-performance DBATs are conceptually similar to CICS-DB2 protected entry threads (with some important differences, which I'll cover momentarily). Like CICS-DB2 protected entry threads, high-performance DBATs persist through multiple transaction executions (typically, a non-protected CICS-DB2 thread will terminate when the transaction using that thread completes). This persistence, in and of itself, is not such a big deal in the DB2 DDF world. For one thing, thread creation and termination doesn't consume a whole lot of resource (the DB2 systems services address space, aka MSTR, handles this function, and you can see in a DB2 monitor statistics report that MSTR consumes relatively little CPU). More importantly, if you have the DSNZPARM parameter CMTSTAT set to INACTIVE (the default), DB2 DDF connections are disassociated from DB2 DDF threads, and the latter can be pooled and used to support a very large number of connections, so that DBAT re-use will typically be very high and the incidence of DBAT creation will typically be very low.
So, if the re-usability of high-performance DBATs is not the big news, what is? The big news is that high-performance DBATs allow you to realize the CPU efficiency benefit of the RELEASE(DEALLOCATE) option of BIND PACKAGE -- a benefit previously available only for packages associated with local-to-DB2 programs. Back to the CICS-DB2 protected entry thread analogy. The real bang for the buck there is the use of such threads for the execution of packages bound with RELEASE(DEALLOCATE). Why? Because, for a package so bound, certain resources, such as table space locks and EDM pool elements, will be retained until the thread used in the execution of the package is deallocated -- this as opposed to being released and then reacquired, over and over again, as a high-volume CICS-DB2 transaction is executed (and retention of table space locks is generally not a big deal, as these are usually intent locks, and intent locks do not conflict with each other). Think about it: suppose a CICS-DB2 transaction accesses data in four table spaces, and the package has several sections that are stored in the EDM pool (the place where packages -- the run-time structures created from binding programs with embedded static SQL -- are cached in memory). If the transaction program's package is bound with RELEASE(COMMIT), when the transaction executes it will get locks (again, almost certainly intent -- versus exclusive -- locks) on the four table spaces and space in the EDM pool for the required package sections, and when it completes (driving a DB2 commit) those tablespace locks and EDM pool elements will be released. If the same transaction is executed right after that first one, those same tablespace locks and EDM pool elements will be reacquired and then released again at transaction completion. All that acquisition and release of the same stuff adds to the CPU cost of the transaction.
If the CICS-DB2 transaction program's package is bound with RELEASE(DEALLOCATE), and IF the thread used to execute the package can persist through executions of the program (that's where CICS-DB2 protected entry threads come in), the tablespace locks and EDM pool elements acquired for the program will be retained until the thread is deallocated, and that will likely not happen until the transaction has been executed a LOT of times; so, RELEASE(DEALLOCATE) plus protected entry threads together deliver a nice CPU efficiency benefit for high-volume CICS-DB2 transactions.
What kept that benefit from being available for transactions that execute in a client-server DB2 environment? It wasn't thread persistence -- as mentioned, DBATs, once created, can be reused many, many times. The problem was the RELEASE(DEALLOCATE) part of the equation. See, for a long time (since DB2 V6, I believe), although you could bind a package executed by remote DRDA requesters with RELEASE(DEALLOCATE), DB2 would not honor that bind specification in a DDF setting -- if an execution of the package were associated with a DBAT (versus a local-to-DB2 program's thread), DB2 would handle the package as though it had been bound with RELEASE(COMMIT). Why? Because, as noted, DBATs can stick around for a long time, and having tablespace locks -- even intent locks -- held by such long-duration threads could interfere with certain database maintenance operations (utilities, in particular). Now that high-performance DBATs have made the scene, RELEASE(DEALLOCATE) can be honored by DB2 even for packages executed on behalf of remote requesters via DBATs.
I mentioned that there are some important differences between CICS-DB2 protected entry threads and high-performance DBATs. One of those key differences concerns the actions required to bring the two different kinds of threads into existence. To have CICS-DB2 protected entry threads, one has to use CICS resource definition online functionality (aka RDO) to define a DB2ENTRY resource with a PROTECTNUM (and a THREADLIMIT) value that is greater than zero. A high-performance DBAT, on the other hand, is instantiated when it is used in the execution of a package bound with RELEASE(DEALLOCATE) -- there is no set-up, per se, required in order to have high-performance DBATs in your DB2 system (aside from the need to set the MAXDBAT and CONDBAT DSNZPARM parameters to values that are greater than zero, and to have CMTSTAT in DSNZPARM set to INACTIVE -- but that's what you've probably done anyway if you do any DDF processing on your system). Let's say that a remote requester issues an SQL statement that causes a DB2 package bound with RELEASE(DEALLOCATE) to be executed. Here's what will happen:
- If there is already a high-performance DBAT in existence and associated with this particular connection (more on that in a moment), and it's available, it will be used in the execution of the package.
- If there is not already a high-performance DBAT in existence for this connection, or if there is and it is tied up in the execution of another package, a "regular" DBAT in the pool will become a high-performance DBAT and will be used to execute the package (or a new DBAT will be created and will be a high-performance DBAT, if there are no available "regular" pooled DBATs).
Once the high-performance DBAT has been instantiated, it will be used to process 200 units of work and will then be terminated (if it is not terminated before as a result of hitting the idle thread timeout threshold). Auto-termination after 200 units of work is done to periodically release resources held by the high-performance DBAT. Even with that periodic break in the action, there will be times when you want resources acquired in the execution of packages to be released at commit, no matter how the packages are bound (perhaps during a period of time during which you want to run a number of utilities involving tablespaces targeted by SQL statements in the client-server packages). That can be easily accomplished thanks to a new command, -MODIFY DDF, that was introduced with DB2 10. If you issue -MODIFY DDF PKGREL(COMMIT), packages executed via DBATs will release resources at commit, no matter what RELEASE option was specified when the package was created (again, we're talking about tablespace locks and EDM pool elements -- retention of page and row locks is not affected by the RELEASE option of BIND PACKAGE). When this period of utility (for example processing is done, you can issue -MODIFY DDF PKGREL(BNDOPT) to have DB2 once again honor the RELEASE(DEALLOCATE) specification for packages so bound that are executed via DBATs.
Now, I implied previously that a high-performance DBAT, once instantiated, will be dedicated to the connection through which the request invoking the RELEASE(DEALLOCATE) package was issued. This is in fact the case. A high-performance DBAT does not go into the pool when it is freed up, to be usable by other connections to the DB2 subsystem. Instead, if the RELEASE(DEALLOCATE) package invocation came from a connection established by application server XYZ, the high-performance DBAT instantiated as a result of that request will persist to process 199 more units of work from application server XYZ; furthermore, there is no requirement that all -- or any -- of those 199 additional units of work be associated with packages bound with RELEASE(DEALLOCATE) -- a high-performance DBAT can be used for the execution of packages bound with RELEASE(COMMIT), too.
If you're using DB2 10 now, or you're planning your migration to this new DB2 release, I encourage you to think about taking advantage of high-performance DBATs to improve the CPU efficiency of your DDF workload. In other words, I want you to think about binding packages executed frequently via DBATs with RELEASE(DEALLOCATE); and, don't limit your consideration to packages associated with frequently executed programs that issue static SQL statements (though such packages, which could be related to DB2 stored procedures, can be excellent candidates for binding with RELEASE(DEALLOCATE)). You should also consider binding with RELEASE(DEALLOCATE) packages used by remote programs that issue dynamic SQL statements. Here, I have particularly in mind the packages used by the IBM Data Server Driver for JDBC and SQLJ. You can read more about binding these packages with RELEASE(DEALLOCATE) in the IBM "red book" titled "DB2 10 for z/OS Technical Overview" (see section 9.5).
The high-performance DBAT feature of DB2 10 is one of the latest in a long line of enhancements that have made DB2 for z/OS more and more valuable as a foundation for high-volume, transactional client-server applications. Go and rev up your DDF workload.
