How to limit degree of parallelism of DB2 utilities?

Normalerweise werden Sort/DFSORT-Probleme von DB2 Utilities gelöst, in dem DB2 mit dem „SORTNUM elimination feature“ die dynamische Allokation der erforderlichen Datasets überlassen wird.

Was ist aber zu tun, wenn es Probleme bei der Verwendung von DFSORT gibt, wie beispielsweise …

ICE039A INSUFFICIENT MAIN STORAGE 
ICE046A SORT CAPACITY EXCEEDED 
ICE083A RESOURCES WERE UNAVAILABLE FOR DYNAMIC ALLOCATION OF WORK DATA SETS

Dazu wollen wir einen Blick auf ein paar besondere Datasets werfen.

Neben anderen Work- und Print-Datasets verwendet DFSORT, das in LOAD, REORG und REBUILD zum Einsatz kommt, folgende Datasets, die wir genauer betrachten wollen:

• UTPRINT: A data set that contains messages from DFSORT. Required for the LOAD, REBUILD, and REORG utilities.
• UTPRINnn: Data sets that contain messages from DFSORT from utility subtasks pairs. These data sets are dynamically allocated when UTPRINT is allocated to SYSOUT.
• DTPRINnn: Data sets that contain messages from UNLOAD parallelism functions. These data sets are dynamically allocated when UTPRINT is allocated to SYSOUT.

Data sets used by REORG which influence degree of parallelism:

• Data sets for unload parallelism. The DD names have the form DTPRINnn.
  Every time you invoke REORG TABLESPACE, new DTPRINnn data sets are dynamically allocated. REORG TABLESPACE does not reuse DTPRINnn data sets from previous job steps. This behavior might cause the available JES2 job queue elements to be consumed more quickly than expected.
• The DD name UTPRINnn defines the sort work message data sets that are used by the utility subtask pairs. nn identifies the subtask pair.
  Every time you invoke REORG TABLESPACE, new UTPRINnn data sets are dynamically allocated. REORG TABLESPACE does not reuse UTPRINnn data sets from previous job steps. This behavior might cause the available JES2 job queue elements to be consumed more quickly than expected.

REORG does not attempt to unload and reload table space partitions in parallel in the following situations:

• If the DATAWKnn DD statements are coded in the JCL.
• If you do not specify the SORTDEVT keyword.
• If the UTPRINT data set is not allocated to SYSOUT.
• If you specify the REBALANCE keyword.
• If rows might move from one partition to another as a result of alter limit keys or partition-by-growth table space.
• If you specify the UNLDDN keyword with a template name and specify UNIT(TAPE) STACK(YES).).
• When the number of subtasks that are started in parallel exceeds the value of the PARALLEL option or the PARAMDEG_UTIL subsystem parameter.

LOAD or REBUILD INDEX always sorts the index keys and builds them in parallel for partitioned table spaces unless constrained by available memory, sort work files, or UTPRINnn file allocations.

Die größte Kontrolle über der Parallelität der Sort-/DFSORT-Operationen kann somit erzielt werden, wenn folgende JCL Dataset-Spezifikationen verwendet werden:

1. Bereitstellung von DD-Statements mit DD-Namen in der Form SWnnWKmm.
   Temporary data sets for sort input and output when sorting keys if Parallel Index Build (PIB) is used. nn identifies the subtask pair (one per index), and mm identifies one or more data sets that are to be used by that subtask pair when executing LOAD, REBUILD, or REORG. The used message data set is UTPRINnn.

//* First group of sort work data sets for parallel index rebuild 
//SW01WK01 DD UNIT=SYSDA,SPACE=(CYL,(10,20),,,ROUND) 
//SW01WK02 DD UNIT=SYSDA,SPACE=(CYL,(10,20),,,ROUND) 
//SW01WK03 DD UNIT=SYSDA,SPACE=(CYL,(10,20),,,ROUND) 
//* Second group of sort work data sets for parallel index rebuild 
//SW02WK01 DD UNIT=SYSDA,SPACE=(CYL,(10,20),,,ROUND) 
//SW02WK02 DD UNIT=SYSDA,SPACE=(CYL,(10,20),,,ROUND) 
//SW02WK03 DD UNIT=SYSDA,SPACE=(CYL,(10,20),,,ROUND) 

2. Bereitstellung von DD-Statements mit DD-Namen in der Form UTPRINnn.

        //*limit data sort parallel degree 
        //DTPRINnn DD SYSOUT=* 

3. (Optional) Bereitstellung von DD-Statements mit DD-Namen in der Form DTPRINnn

        //* limit data sort parallel degree 
        //DTPRINnn DD SYSOUT=* 

Diese Methode befreit nicht von der Notwendigkeit ein UTPRINT DD-Statement im Utility Step anzugeben.

Für die Lösung von DFSORT-Problemen wird empfohlen stets auch ein //SORTDIAG DD DUMMY Statement in die Utility JCL einzufügen, was zu zusätzlichen Analyse-Nachrichten in den Datasets führt, die unter DD-Namen UTPRINT, UTPRINnn und DTPRINnn allokiert wurden. Das Hinzufügen eines DFSPARM DD-Statements sorgt für weitere Nachrichten zur Fehleranalyse.

//SORTDIAG DD DUMMY 
//DFSPARM DD * 
DEBUG ABEND 
/*

Quellen: IBM DB2 for z/OS Utility Guide, DB2 9 for z/OS: Using the Utilities Suite, IBM DB2 Support Pages.