The REBUILD INDEX Utility

The REBUILD INDEX Utility

The REBUILD INDEX utility can be used to re-create indexes from current data. Indexes defined as COPY NO are always recovered from actual table data, not from image copy and log data. If the index is defined as COPY YES , it can be recovered from an image copy or rebuilt from the table data. REBUILD INDEX scans the table on which the index is based and regenerates the index based on the current data. JCL to run the REBUILD INDEX utility is provided in Listing 32.9.

Listing 32.9. REBUILD INDEX JCL

 //DB2JOBU  JOB (UTILITY),'DB2 REBUILD IDX',MSGCLASS=X,CLASS=X, //          NOTIFY=USER //* //**************************************************************** //* //*         DB2 REBUILD INDEX UTILITY //* //**************************************************************** //* //UTIL EXEC DSNUPROC,SYSTEM=DSN,UID='RBLDINDX',UTPROC=" //* //*  UTILITY WORK DATASETS //* //DSNUPROC.SORTWK01 DDUNIT=SYSDA,SPACE=(CYL,(2,1)) //DSNUPROC.SORTWK02 DDUNIT=SYSDA,SPACE=(CYL,(2,1)) //DSNUPROC.SYSUT1 DD DSN=&&SYSUT1, //        UNIT=SYSDA,SPACE=(CYL,(2,1)),DCB=BUFNO=20 //DSNUPROC.UTPRINT DD SYSOUT=X //* //*  UTILITY INPUT CONTROL STATEMENTS //*    1. The first REBUILD INDEX statement rebuilds the //*       DSN8810.XPROJ2 index. //*    2. The second REBUILD INDEX statement rebuilds only //*       the third partition of the DSN8810.XEMP1 //*       partitioning index. //*    3. The third and final REBUILD INDEX statement //*       rebuilds all indexes on all tables in the //*       DSN8D81A.DSN8S81C table space. //* //DSNUPROC.SYSIN    DD  *     REBUILD INDEX (DSN8810.XPROJ2)     REBUILD INDEX (DSN8810.XEMP1) DSNUM 3     REBUILD INDEX (ALL) TABLESPACE DSN8D81A.DSN8S81C /* // 

NOTE

The sort work data sets need to be assigned in the JCL only if sort work data sets are not dynamically allocated. Additionally, you should consider explicitly defining sort work data sets when recovering very large indexes.

Rather than explicitly assigning SORTWK files, consider giving DFSORT more responsibility. The dynamic default for most shops is DYNALOC=(SYSDA,004) . If 4 work files are not enough or the SYSDA pool does not have sufficient space, use the SORTNUM DB2 utility clause to increase the number of dynamically allocated files and use the SORTDEVT clause to assign the disk pool. Let DFSORT assign the primary and secondary allocations .

REBUILD INDEX Phases

There are five phase of the REBUILD INDEX utility:

UTILINIT	Sets up and initializes the REBUILD utility.
UNLOAD	Unloads data from the appropriate table and places it in the data set assigned to the SYSUT1 DD statement (if SORTKEYS is not specified).
SORT	Sorts the unloaded index data.
BUILD	Builds indexes and checks for duplicate key errors. Unique indexes with duplicate key errors are not recovered successfully.
SORTBLD	When the SORTKEYS option is used to invoke parallel index build processing for a simple or segmented table space or table space partition, all activities that normally occur in the SORT and BUILD phases occur in the SORTBLD phase instead.
UTILTERM	Performs the final utility cleanup.

Estimating REBUILD INDEX Work Data Set Sizes

The REBUILD INDEX utility requires work data sets to rebuild DB2 indexes. The following formulas can help you calculate estimated sizes for these work data sets. More complex and precise calculations are in the DB2 Utility Guide and Reference manual, but these formulas should produce comparable results.

 SYSUT1 = (size of the largest index key + 13) x (total number of rows in the associated  table for the index) x (number of indexes on the table) SORTWKxx = (size of SYSUT1) x 2 

NOTE

If any of these numbers is 0, substitute 1.

After calculating the estimated size in bytes for each work data set, convert the number into cylinders , rounding up to the next whole cylinder. Allocating work data sets in cylinder increments enhances the utility's performance.

Of course, you can set up REBUILD to automatically allocate work data sets as of DB2 V7.

REBUILD INDEX Locking Considerations

Index rebuilding can run concurrently with the following utilities:

CHECK LOB	DIAGNOSE
MERGECOPY	MODIFY
REPORT	STOSPACE
RUNSTATS TABLESPACE	UNLOAD
COPY TABLESPACE SHRLEVEL REFERENCE
REORG TABLESPACE ( UNLOAD ONLY or UNLOAD EXTERNAL ) (without a clustered index)
REPAIR LOCATE TABLESPACE ( PAGE , DUMP , or VERIFY )
REPAIR LOCATE ( RID , DUMP , or VERIFY )

The REBUILD INDEX utility drains all claim classes for the index being recovered and drains the write claim class for the associated table space.

If REBUILD INDEX is being specified for an individual partition, the utility drains all claim classes for the index partition and the logical partition of a type 2 index. The read claim class is drained for non-partitioned type 2 indexes. Also, this utility will drain write claim classes for the associated table space partition.

REBUILD INDEX and Index Versions

Online schema changes (explained in Chapter 7, "Database Change Management and Schema Evolution") can cause multiple versions of a DB2 index to exist in the DB2 Catalog. When a new index version is created, DB2 assigns it the next available version number ”which can range from 0 to 15. After DB2 assigns version number 15, it re- assigns version number 1 again, if that version number is not already used. (Version number 0 is reserved for the original definition of the index before any ALTER statements were applied.)

Running REBUILD INDEX causes the version information to be refreshed. In other words, the range of used version numbers for indexes defined with COPY NO is reset. This means that the OLDEST_VERSION column for the index is set to the current version number, indicating that only one version is active. This allows DB2 to reuse all of the other version numbers.

NOTE

LOAD REPLACE , REORG INDEX , and REORG TABLESPACE can also be used to recycle version numbers for indexes defined with COPY NO .

MODIFY RECOVERY can be used to refresh and recycle version numbers for indexes defined with COPY YES , and for table spaces.

REBUILD INDEX Guidelines

The following guidelines can be applied to ensure effective usage of the REBUILD INDEX utility.

Avoid SYSUT1 if Possible

The SYSUT1 data set is not required to rebuild indexes. By removing SYSUT1 from the JCL, the REBUILD utility will perform faster and will require less work space. However, if SYSUT1 is not included, the REBUILD INDEX utility is not restartable in the UNLOAD phase.

Consider Using CHECK INDEX Before Rebuilding Large Indexes

Execute the CHECK INDEX utility for large indexes before running REBUILD INDEX . If CHECK INDEX indicates that the index is invalid, REBUILD INDEX should be run. If CHECK INDEX indicates that the index is valid, however, you can save valuable processing time because CHECK INDEX is faster than REBUILD INDEX .

Be Aware of Underlying VSAM Data Set Deletions

The underlying VSAM data sets for STOGROUP -defined indexes are deleted and defined by the REBUILD INDEX utility. If the index has been user-defined, the corresponding VSAM data set is not deleted.

Reorganize System Indexes Using REBUILD INDEX

Although the DB2 Catalog and DB2 Directory table spaces and indexes can be reorganized, their indexes can be rebuilt which effectively reorganizes these indexes.

Rerun REBUILD INDEX When Necessary

REBUILD INDEX is not restartable unless the SYSUT1 data set is specified and cataloged (and SORTKEYS is not specified). If the REBUILD INDEX abends, terminate the utility, correct the cause of the abend, and rerun the utility. Typical causes for REBUILD INDEX abends include the unavailability of the applicable table space and VSAM data set allocation failures.