Friday, April 22, 2011

You DO Let DB2 for z/OS Allocate Utility Sort Work Data Sets, Don't You?

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:
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. 


  1. Great clarification for newbies (like me)

  2. Hi Robert,

    thanks for all the information!! I have one question. I am giving SORTDEVT DISK SORTNUM 90 in most of my Reorg jobs. If utility required very less sort work datasets than 90. Then specifying large value of SORTNUM will effect the degree of parallelism or not??

    1. in addition to that will it be a better option If I completely omit the SORTNUM parameter from the job??

    2. Yes, that would be a better option, IF you are set up to enable Db2 to direct the dynamic allocation of sort data sets (as pointed out in the blog post, that involves setting UTSORTAL in ZPARM to YES, having no DFSORT-related DD cards in the job's JCL, and not specifying SORTNUM in the utility control statement). You might want to validate this approach with a few of your REORG jobs, and then (assuming positive results, which I'd expect) set IGNSORTN in ZPARM to YES so that any SORTNUM specification in a REORG utility control statement will be ignored.

      If you provide a SORTNUM value, there's a good chance that your provided value will be sub-optimal. A too-high value can indeed reduce the degree of parallelism for a REORG job. Best to let Db2 figure this out.


  3. Hi Robert,

    Does SORTNUM elimination use solely RTS.

    If it does so, then would DB2 estimate very inaccuratelty, if RTS is out of sync for any reason.
    I am asking this, since I have heard and read at a couple of places that RTS is still not 100% reliable , although it has been improved over the years.
    I am particularly concerned for objects which don't get Runstats/Reorg often.

    Thank you.

    Tushar Jha

    1. And one more thing, when DB2 is estimating the SORTWK* datasets , is it aware of the Storage Pool where these SORTWK* data sets will be allocated , by any chance.
      I don't think it will have any idea, but just want to ask you.

      I ask this, because I guess DB2 will allocate the minimum number of data sets to maximise parallelism , but what that might result in is bigger sortwork data sets and thus more difficult to have them fit on Storage Volumes, particularly when many other Reorgs are running in parallel. This might result in B37 or similar errors.

      Or is this not true.

      Thanks a lot, in advance.

    2. If you do not have a SORTNUM specification in a utility control statement for a utility such as LOAD or REORG, or you do have a SORTNUM specification but the parameter IGNSORTN in ZPARM is set to YES, Db2 will use real-time statistics to estimate the number of records that will be sorted, and that determines the size of dynamically allocated sort work data sets. The real-time statistics should be accurate. Yes, it could be some time since REORG or RUNSTATS was last executed for an object, but the insert and delete counters are updated when those statements are executed, so the record-count estimate should still be quite accurate.

      My understanding is that Db2 does not take availability of space into account when determining the size of sort work data sets to be used when a utility is executed. Could disk space available for sorting be exhausted? Yes, that is possible, depending on the size of the pool available for that purpose and on the number of sort-driving utilities that are executing concurrently. If running out of sort space is a problem, a possible solution is to implement the IBM Db2 Sort product. Db2 Sort does a very good job of adjusting sort resources used for Db2 utilities on the fly, and that can lead to more utility jobs running successfully to completion, versus failing due to sort space problems.


  4. Hi Robert,You have mentioned "regardless of whether this dynamic allocation is done by DB2 (preferred) or DFSORT." how is the dynamic allocation done by db2 & how it is done by dfsort? Do you mean specifying sortwk* dd name will cause db2 to do sort? and having parm sortdevt cause dfsort to do sort?

    1. Db2 never does the sorting for utilities. That sorting is always done by DFSORT (unless the IBM software product Db2 Sort is installed on the system). My understanding is that DFSORT can dynamically allocate sort work data sets when you have a SORTDEVT and a SORTNUM specification for a REORG job (you can read about those REORG options on this page in the Db2 documentation: If you want DFSORT's dynamic allocation of sort work data sets to be directed by Db2, either leave SORTNUM out of the REORG utility control statement, or set the ZPARM parameter IGNSORTN to YES. Of course, dynamic allocation of sort work data sets, whether done by DFSORT on its own or by DFSORT as directed by Db2, means not allocating them via DD statements in the job's JCL.


    2. Thanks much for clarification.