DB2 : How to improve the elapsed time for db2 load jobs using parallel index build

The tables with more than one index can be benefitted by parallel index build (PIB). When load card has non-zero value for SORTKEYS, it instructs the load utility to perform parallel index build.

When we use PIB, load utility skips SORT, BUILD phases and goes directly to SORTBLD phase. SORTBLD phase does all the processing carried out in SORT & BUILD phases.

The RELOAD phase, instead of writing index key values to SYSUT1 file(SORTIN file), it writes them into the main memory.  Also I/O to SORTOUT file is avoided.

PIB reduces the elapsed time for a LOAD job by sorting the index keys and rebuilding multiple indexes in parallel (SORTBLD phase), rather than sequentially. Optimally, a pair of subtasks process each index; one subtask sorts extracted keys while the other subtask builds the index. LOAD begins building each index as soon as the corresponding sort produces its first sorted record.

You don’t have to provide the SORT work files. Based on the SORTKYES value, SORT will automatically allocate the SORT work files for you.  To calculate the SORTKYES values, multiply the number of input records by number of indexes defined on the table.  A sample load job that utilizes parallel index build is given below.

//STEP001  EXEC PGM=DSNUTILB,REGION=0M,                 

//            PARM='DB1T,LOAD'                          

//SYSIN    DD *                                         

 LOAD DATA                                               

  REPLACE  LOG NO  NOCOPYPEND SORTKEYS 2502872091       

  STATISTICS TABLE INDEX UPDATE ALL                     

 INTO TABLE MY.TABLE                                    

 .....                                                  

 .....                                                   

//*                                                     

//SYSREC   DD DSN=INPUT DATASET..                       

//SYSDISC  DD ....                                      

//SYSMAP   DD ....                                       

//SYSERR   DD ....                                      

//SYSUT1   DD ....                                      

//SORTOUT  DD ....                                      

//SYSPRINT DD SYSOUT=*                                  

//UTPRINT  DD SYSOUT=*