Using reorganize in a utility procedure
Use the Reorganize Index on an Object List notebook to reorganize an object list containing
only indexes to improve access performance
and reclaim fragmented space.
Authorities and privileges

To reorganize an index:
- Open the Reorganize Index on an Object List
window.
- 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.
- 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.
- On the Options page, specify how you want the reorganization utility
to run.
- On the Data Sets page, identify specific data set information, data set templates and sizes, that the
reorganization utility uses.
- On the Statistics Options page, specify how the utility should report statistics
for the REORG .
- On the Correlation Statistics page, specify properties for gathering correlation
statistics.
- Click OK.

Specifying how the utility runs
To specify how the utility runs:
- Optional: Select the Specify options for performance improvement check box:
- Specify a value in the LEAFDISTLIMIT field.
- Specify whether you want to Generate a report
only, by selecting the Yes or No radio button.
- From Type of access allowed select:
- 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.
- 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.
- 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.
- 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
To specify data set information:
- Type a name for the UNLOAD data set template.
- 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.
[Return to main task ]

Specifying statistics options
To specify statistics options on the Statistics
Options page:
- 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).
- 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.
[Return to main task ]

Specifying correlation statistics properties
To specify correlation statistics properties:
- 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.
- 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.

Related information: