For almost four years ago I participated in IDUG's european conference. On this conference IBM told that the use of option SORTKEYS for DB2 utilities LOAD and REORG isn't very well known. If any of you still doesn't known about this option you can read on. There are two other options that I will like to share with you, too.

Option SORTKEYS joined DB2 in version 5 and by using it, LOAD and REORG will build the indexes in parallel with the data load and sorting will take place in storage whenever possible. In short SORTKEYS leads to much shorter elapsed times. Parallellism occurs when there is more than one index related to the tablespace being loaded or reorganized.

SORTKEYS accepts an integer as parameter. For instance you can type in SORTKEYS 5. There is a very complicated formula for calculating the optimal value for this parameter. Fortunately you can leave out the parameter and just type SORTKEYS.

Option SORTDEVT makes it obsolete to allocate sortwork datasets in the JCL. This is a huge advantage and leaves you with far more simple jobs. Another advantage is that the utility itself can estimate and allocate the sortwork datasets. SORTDEVT needs a UNIT name and you can just use SYSDA. Many installations have defined special UNIT names for work datasets so you might ask your storage manager for this name and use it instead of SYSDA. Another benefit by using SORTDEVT is that you will automatically get parallellism when building the indexes in conjunction with SORTKEYS. If you allocate the sortwork datasets in the JCL you must use DD names confirming to the naming standard //SWnnWKmm in order to get parallellism. This means, that if you use SORTKEYS in conjuction with //SORTWKnn then you will not get your indexes build in parallel..

Option SORTNUM only works in conjunction with SORTDEVT and after SORTNUM you tell the utility how many sortwork dataset it must allocate. For instance SORTNUM 6 means that DB2 allocates 6 sortwork datasets for sorting. I will reccommend the use of SORTNUM in conjunction with SORTKEYS, because SORTDEVT alone will give you the number of sortwork datasets that are default in DFSORT and the default of DFSORT may not suit your needs very well. I always use SORTNUM 6 and it works fine. There may be special cases where another value is better.