Reorganizing an index

To determine when to reorganize an index, use the Query Catalog window to find indexes with large leaf page distances. Generally, leaf page distances greater than 200 might indicate a disorganized index. This rule is merely a general indicator, and in some cases large leaf distances are acceptable.

Authorities and privileges

To reorganize an index:

1. Open the Reorganize Index window.
   
2. If your table space is partitioned, select the Specify partition check box and then click the up or down arrows to define a partition number or range. You can reorganize a single partition of a partitioned table space, or a range of partitions within a partitioned table space. The integer selected must be in the range from 1 to the number of partitions that are defined for the partitioning index. The maximum is 254.
   
   
3. 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.
   
   
4. Select the Update statistics check box. This allows you to gather statistics for the tables and indexes in the table space. This enables the Statistic Options, and Correlation Statistics pages of the notebook.
   
5. On the Options page, specify how you want the reorganization utility to run.
   
6. On the Data Sets page, identify specific data set information, data set names and sizes, that the reorganization utility uses.
   
7. On the Statistics Options page, specify how the utility should report statistics for the REORG .
   
8. On the Correlation Statistics page, specify properties for gathering correlation statistics.
   
9. Click OK.
   

Related information

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

Specifying how the utility runs

1. Optional: Select the Specify options for performance improvement check box:
   
   1. Specify a value in the LEAFDISTLIMIT field.
      
   2. Specify whether you want to Generate a report only, by selecting the Yes or No radio button.
      
2. From Type of access allowed select:
   
   1. The Read-only application access radio button to unload data from the area being reorganized (while applications can read but cannot write to the area), reloading into a shadow copy of that area (while applications can read but cannot write to the original copy) switching applications' future access from the original copy to the shadow copy by exchanging the names of the data sets, and then allowing read/write access again. Click the Options push button to open the Reference Option window.
      
   2. The Read-write application access radio button to unload data from the area being reorganized (while applications can read and write to the area), reloading into a shadow copy of that area (while applications have read/write access to the original copy of the area), applying the log of the original copy to the shadow copy (while applications can read and usually write to the original copy), switching applications' future access from the original copy to the shadow copy by exchanging the names of the data sets, and then allowing read/write access again. Click the Options push button to open the Change Option window.
      
   3. The No application access radio button to unload data from the area being reorganized (while applications can read but cannot write to the area), reloading into that area (while applications have no access), and then allowing read/write access again.
      
3. From After unloading the data select:
   

   Continue processing

   After unloading the data, the utility continues processing.
   

   Pause and store status

   After unloading the data, the utility ends and the RELOAD status is stored in SYSIBM.SYSUTIL so that processing can be restored with the RELOAD RESTART (PHASE) command.
   

   End and remove status

   After unloading the data, the utility ends and the status stored in SYSIBM.SYSUTIL that corresponds to this utility ID is removed.

Specifying data set information

1. Type a name for the UNLOAD data set. SYSUT1 is the data definition (DD) name that is associated with this data set name.
   
2. If you want DB2 to preformat pages, select the Preformat remaining pages in the index space after REORG check box.Preformatting pages increases execution performance on spaces that have high volume INSERTS by formatting the pages beforehand. Preformatting transfers the formatting delay and increases the REORG time. The additional REORG time depends in the amount of DASD being formatted Recommendation: You should use preformatting on indexes that:
   
   • Have a high ratio of inserts to reads
     
   • Will receive a predictable amount of data.
     
   Preformatting is not recommended for indexes that have a high ratio of reads to insert if these reads will cause unnecessary reads of empty pages. Preformatting provides the greatest improvement when used on spaces that start out empty and are populated through high insert activity before any query access is executed.
   
   

Specifying statistics options

1. Specify Yes or No in the Report statistics radio buttons. Specify Yes if you want the set of messages output to the SYSPRINT data set. The default is that no messages are sent to SYSPRINT).
   
2. Specify how you want the REBUILD to update the DB2 catalog:
   

   Access path and space statistics

   The utility updates all the collected 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 that provide statistics to help database administrators assess the status of a space or index.
   

   Do not update catalog tables

   The utility does not update the collected 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. The default is 1, which means collect frequent values on the first key column of the index.
   

   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. The default is 10.