Rebuilding an index

Authorities and privileges

To rebuild an index:

1. Open the Rebuild Index window.

The Rebuild Index window displays the selected indexes in a table.
2. Specify work data set properties in the Data Sets page.
3. Specify index statistics properties in the Statistics options page .
4. Specify Correlation Statistics page.
5. Click OK.

• Altering an index
• Dropping an index
• Displaying reports about indexes
• Stopping indexes
• Starting indexes

Specifying work data sets information

1. Type a Data set name, a Device , and a Space in the Temporary work data set fields.

DB2 uses the information that you type in this field to identify the temporary work files used during REBUILD INDEX. If you do not specify a temporary work file, the DB2 uses the default, SYSUTIL.
2. Type a device type for the data facility sort (DFSORT).


DFSORT is a non-DB2 utility used to sort, merge, and copy information. In the device type field, you can specify any IBM direct access storage device or tape device supported by your operating system.
3. Select the Number of DFSORT temporary data sets check box if you want to specify the maximum number of data sets. If you select the check box, type a number in the associated text field. You can type a number between 1 and 255. If you do not select this check box, DFSORT uses a default maximum number of data sets.

Tip: To optimize resource allocation, avoid specifying a large number of work data sets unnecessarily.
4. Optional: Select the Sort index keys in parallel check box to specify the keys are sorted in parallel unless constrained by storage or memory limitations. Sorting the keys in parallel will improve performance. The greatest improvement in elapsed time occur when parallel sorting is applied to:
• Multiple indexes on a simple, segmented, or partitioned table space
• The partitioning index on all partitions of a partitioned table space
• The non partitioning index on a partitioned table space.
5. Optional: Select the Logically reuse and reset data sets check box. This setting specifies that DB2 can use non-empty data sets during the REBUILD. If you have not defined VSAM data sets with the REUSE option or do not specify this option the data sets used during REBUILD must be empty.

Specifying statistics options

1. Specify Yes or No in the Report statistics radio buttons. Specifying Yes indicates that the DB2 catalog will be updated (unless you specify Do not update catalog under Update options on catalog table).
2. Specify how you want the REBUILD to update the DB2 catalog:
Access path and space statistics
The utility updates all the statistics.
Access path statistics only
The utility updates only the catalog tables used for access path selection.
Space statistics only
The utility updates only the catalog table columns that provide statistics to help database administrators assess the status of a particular table space or index.

Do not update catalog tables
The utility updates no catalog tables with statistics.

Specifying correlation statistics properties

1. Specify the Collect key cardinalities check box to collect all of the distinct values in all of the 1 to n key column combinations for the specified indexes, where n is the number of columns in the index.
2. To collection frequent value statistics, click the Add push button and type values in the cells of the Frequent values statistics options table:

Number of Columns

Indicates the number of key columns to concatenate together when collecting frequent values from the specified index. Specifying '3' means to collect frequent values on the concatenation of the first three key columns.

Number of frequent values

Indicates the number of frequent values to be collected. Specifying '15' means collect 15 frequent values from the specified key columns.