Sample REORG INDEX control statements

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

Example 1: Reorganizing an index

The following control statement specifies that the REORG INDEX utility is to reorganize index XMSGTXT1. The UNLOAD PAUSE option indicates that after the data has been unloaded, the utility is to stop. Processing can be restarted in the RELOAD phase. This option is useful if you want to redefine data sets during reorganization.

REORG INDEX DSN8D10.XMSGTXT1
 UNLOAD PAUSE

Example 2: Collecting inline statistics while reorganizing an index.

The following control statement specifies that REORG INDEX is to collect statistics for index XEMPL1 while reorganizing that index. The SHRLEVEL REFERENCE option indicates that during this processing, only read access is allowed on the areas that are being reorganized.

REORG INDEX DSN8D10.XEMPL1
   SHRLEVEL REFERENCE STATISTICS

Example 3: Updating access path statistics in the catalog and catalog history tables while reorganizing an index

The following control statement specifies that while reorganizing index IU0E0801, REORG INDEX is to collect statistics and update access path statistics in the catalog and catalog history tables. The utility is also to send any output, including space and access path statistics, to SYSPRINT.

REORG INDEX IUOE0801       
        STATISTICS           
        REPORT YES        
        UPDATE ACCESSPATH 
        HISTORY ACCESSPATH

Example 4: Reorganizing a list of indexes

In the following example, the OPTIONS statement specifies that the subsequent TEMPLATE and LISTDEF utility control statements are to run in PREVIEW mode. If the syntax of these statements is correct, Db2 expands the REORG_INDX list and the data set names in the SREC, SUT1, and SOUT templates and prints these results to the SYSPRINT data set. The second OPTIONS control statement turns off the PREVIEW mode, and the subsequent REORG INDEX job runs normally.

The REORG INDEX statement specifies that the utility is to reorganize the indexes that are included in the REORG_INDX list. The SHRLEVEL CHANGE option indicates that during this processing, read and write access is allowed on the areas that are being reorganized, with the exception of a 100-second period during the last iteration of log processing. During this time, which is specified by the MAXRO option, applications have read-only access. The WORKDDN option indicates that REORG INDEX is to use the data set that is defined by the SUT1 template. If the SWITCH phase does not begin by the deadline that is specified on the DEADLINE option, processing terminates.

//STEP2    EXEC DSNUPROC,UID='HUHRU257.REORGI',TIME=1440,    
//         UTPROC='',                                        
//         SYSTEM='SSTR',DB2LEV=DB2A                         
//SYSIN    DD *                                              
   OPTIONS PREVIEW                                           
   TEMPLATE SREC                                             
            UNIT(SYSDA) DISP(NEW,CATLG,CATLG)                
            DSN(HUHRU257.REORG.&ST..SREC)                    
   TEMPLATE SUT1                                             
            UNIT(SYSDA) DISP(NEW,DELETE,CATLG)               
            DSN(HUHRU257.REORG.&ST..SUT1)                    
   TEMPLATE SOUT                                             
            UNIT(SYSDA) DISP(NEW,DELETE,CATLG)
            DSN(HUHRU257.REORG.&ST..SOUT)                  
   LISTDEF REORG_INDX INCLUDE INDEX ADMF001.IPHR5701       
                      INCLUDE INDEX ADMF001.IXHR570*       
   OPTIONS OFF                                             
    REORG INDEX LIST REORG_INDX                            
      PREFORMAT                                            
      SHRLEVEL CHANGE                                      
      DEADLINE 2010-2-4-23.10.12                           
      MAXRO 100                                    
        WORKDDN (SUT1)                                     
/*

Example 5: Creating a FlashCopy image copy with REORG INDEX

The following REORG INDEX control statement reorganizes the index spaces associated with table space DSN8S81E and creates a FlashCopy® image copy of the index.

//SYSADMA JOB (ACCOUNT),'NAME',NOTIFY=&SYSUID  
//*  
//UTIL EXEC DSNUPROC,SYSTEM=VA1A,UID='TEMP',UTPROC=''  
//DSNUPROC.SYSREC DD DSN=SYSOPS.DSNAME,  
// DISP=(NEW,DELETE),  
// SPACE=(CYL,(20,20),RLSE),  
// UNIT=SYSDA,VOL=SER=SCR03  
//DSNUPROC.SYSUT1 DD DSN=SYSOPS.SYSUT1,  
// DISP=(NEW,DELETE,DELETE),  
// SPACE=(CYL,(9,90),RLSE),  
// UNIT=SYSDA,VOL=SER=SCR03  
//DSNUPROC.SYSIN DD *    
LISTDEF COPY_LIST INCLUDE INDEXSPACES TABLESPACE DSN8D81A.DSN8S81E PARTLEVEL ALL   
TEMPLATE SCOPY UNIT(SYSDA) DISP(NEW,CATLG,DELETE)    
DSN(DSNT1.&DB..&TS..CPY1.D&TIME.)    
TEMPLATE FCOPY UNIT(SYSDA) DISP(NEW,CATLG,DELETE)    
DSN(DSNFC.&DB..&TS..P&PA..D&TIME.)    
REORG INDEX LIST COPY_LIST SHRLEVEL REFERENCE FLASHCOPY YES    
FCCOPYDDN(FCOPY) COPYDDN(SCOPY)

Example 6: Reorganizing clone indexes

The following control statement specifies that REORG INDEX is to reorganize only the specified index spaces that contain indexes on clone tables. The SHRLEVEL CHANGE option indicates that during this processing, applications can read and write to the area.

REORG INDEX ADMF001.IPJM0901 SHRLEVEL CHANGE CLONE