Sample REBUILD INDEX control statements

Use the sample control statements as models for developing your own REBUILD INDEX control statements.

Example 1: Rebuilding an index

The following control statement specifies that the REBUILD INDEX utility is to rebuild the DSN8D10.XDEPT1 index.

//STEP1    EXEC DSNUPROC,UID='IUIQU2UT.RBLD1',TIME=1440,
//         UTPROC='',
//         SYSTEM='DSN'
//SYSREC   DD DSN=IUIQU2UT.RBLD1.STEP1.SYSREC,DISP=(MOD,DELETE,CATLG),
//         UNIT=SYSDA,SPACE=(8000,(20,20),,,ROUND)
//SYSIN    DD *
REBUILD INDEX (DSN8D10.XDEPT1)
/*

Example 2: Rebuilding index partitions

The following control statement specifies that REBUILD INDEX is to rebuild partitions 2 and 3 of the DSN8D10.XEMP1 index. The partition numbers are indicated by the PART option.

REBUILD INDEX (DSN8D10.XEMP1 PART 2, DSN8D10.XEMP1 PART 3)

Example 3: Rebuilding multiple partitions of a partitioning or secondary index

The following control statement specifies that REBUILD INDEX is to rebuild partitions 2 and 3 of the DSN8D10.XEMP1 index. The partition numbers are indicated by the PART option. The SORTDEVT and SORTNUM keywords indicate that the utility is to use dynamic data set and message set allocation. Parallelism is used by default.

If sufficient virtual storage resources are available, Db2 starts one pair of utility sort subtasks for each partition. This example does not require UTPRINnn DD statements because it uses DSNUPROC to invoke utility processing. DSNUPROC includes a DD statement that allocates UTPRINT to SYSOUT.

//SAMPJOB  JOB  …
//STEP1    EXEC DSNUPROC,UID='SAMPJOB.RBINDEX',UTPROC='',SYSTEM='DSN'
//SYSIN    DD *
REBUILD INDEX (DSN8D10.XEMP1 PART 2, DSN8D10.XEMP1 PART 3)
 SORTDEVT SYSWK
 SORTNUM  4
/*

Example 4: Rebuilding all partitions of a partitioning index

The following REBUILD INDEX statement specifies that the utility is to rebuild all index partitions of the DSN8D10.XEMP1 partitioning index. Parallelism is used by default. For this example, REBUILD INDEX allocates sort work data sets in two groups, which limits the number of utility subtask pairs to two. This example does not require UTPRINnn DD statements because it uses DSNUPROC to invoke utility processing. DSNUPROC includes a DD statement that allocates UTPRINT to SYSOUT.

//SAMPJOB  JOB  …
//STEP1    EXEC DSNUPROC,UID='SAMPJOB.RCVINDEX',UTPROC='',SYSTEM='DSN'
//* 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)
//SYSIN    DD *
  REBUILD INDEX (DSN8D10.XEMP1)
/*

Example 5: Rebuilding all indexes of a table space

The following control statement specifies that REBUILD INDEX is to rebuild all indexes for table space DSN8D13A.DSN8S13E. The SORTDEVT and SORTNUM keywords indicate that the utility is to use dynamic data set and message set allocation. Parallelism is used by default.

If sufficient virtual storage resources are available, Db2 starts one utility sort subtask to build the partitioning index and another utility sort subtask to build the nonpartitioning index. This example does not require UTPRINnn DD statements because it uses DSNUPROC to invoke utility processing. DSNUPROC includes a DD statement that allocates UTPRINT to SYSOUT.

//SAMPJOB  JOB  …
//STEP1    EXEC DSNUPROC,UID='SAMPJOB.RCVINDEX',UTPROC='',SYSTEM='DSN'
//SYSIN    DD *
REBUILD INDEX (ALL) TABLESPACE DSN8D13A.DSN8S13E
 SORTDEVT SYSWK
 SORTNUM  4
/*

Example 6: Rebuilding indexes only if they are in a restrictive state and gathering inline statistics

The following REBUILD INDEX statement specifies that the utility is to rebuild partition 9 of index ID0S482D if it is in REBUILD-pending (RBDP), RECOVER-pending (RECP), or advisory REORG-pending (AREO*) state. This condition that the index be in a certain restrictive state is indicated by the SCOPE PENDING option. The STATISTICS FORCEROLLUP YES option indicates that the utility is to collect inline statistics on the index partition that it is rebuilding and to force aggregation of those statistics.

//STEP6    EXEC DSNUPROC,UID='JUOSU248.CHK6',             
//         UTPROC='',                                     
//         SYSTEM='SSTR'                                  
//UTPRINT  DD  SYSOUT=*                                   
//SYSREC   DD DSN=JUOSU248.CHKIXPX.STEP6.SYSREC,          
//         DISP=(MOD,DELETE,CATLG),                       
//         UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)        
//SYSCOPY  DD DSN=JUOSU248.CHKIXPX.STEP6.SYSCOPY,         
//         DISP=(MOD,DELETE,CATLG),                       
//         UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)        
//SORTOUT  DD DSN=JUOSU248.CHKIXPX.STEP6.SORTOUT,         
//         DISP=(MOD,DELETE,CATLG),                       
//         UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)        
//SYSIN    DD * 
    REBUILD INDEX (IDOS482D PART 9)                     
            STATISTICS FORCEROLLUP YES                  
            SCOPE PENDING                               
/*

Example 7: Rebuilding indexes with SHRLEVEL CHANGE.

The following control statement specifies that during the rebuild, applications can read from and write to ADMF001.IUKQAI01.

REBUILD INDEX (ADMF001.IUKQAI01) 
         SHRLEVEL CHANGE

Example 8: Rebuilding indexes that are on clone tables

The following control statement specifies that REBUILD INDEX is to reconstruct only the specified indexes that are on clone tables.

REBUILD INDEX (ADMF001.IUKQAI01) 
         CLONE