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 the index is partitioned, specify which partition you want to reorganize:
   
   1. Select the Specify partition check box.
      
   2. 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. On the Options page, specify how you want the reorganization utility to run.
   
4. On the Data Sets page, identify specific data set information, data set names and sizes, that the reorganization utility uses.
   
5. Click OK.

Related information

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

Specifying how the utility runs

1. 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.
2. 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.

[Return to main task ]

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.