Using reorganize in a utility procedure
In the Add Utility Procedure Element window select Reorganize to specify that reorganize utility should be run on the object list when the utility procedure is run. An object list is a collection of predefined objects such as indexes or table spaces that can be referred to in order to run a utility on all of the objects in the object list using only a single command.
Authorities and privileges

To specify Reorganize to be run on an object list through a utility procedure
:
- Open the Table Space Reorganize window.
- From the Options page, specify how you want the reorganization
utility to handle data before during and
after the REORG.
- From the Data Sets page, identify specific data set information,
data set templates and sizes, that are used
with the reorganization utility.
- From the Statistics Options page, you can select options for running
statistics.
- Select the OK push button to add the element to the utility procedure.

- From the Options page on the Log records during reload check box, specify whether records are logged
during the reload phase of the REORG utility.
If the records are not logged, the table
space is recoverable only after an image
copy has been taken. Logging records during
the reload phase is not allowed for any table
space in DSNDB01 or DSNDB06 or if you have
selected SHRLEVEL REFERENCE or CHANGE. This
is required for LOB table spaces.
- Specify the REUSE check box to load data into a non-empty
data set.
- Select the Update statistics check box to enable the statistics options
pages for tables and indexes.
- 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.
- Specify options for improving performance
after unloading the data:
- Specify the SORTDATA option to unload the
data using a sequential scan. If you do not
specify the SORTDATA option, the REORG utility
uses the clustering index to unload the data
.
- Specify the NOSYSREC option to omit the unload
data set. The output of sorting (if there
is a clustering index and SORTDATA is specified)
is the input to reloading, without using
an unload data set. You can use this option
only if you specify SORTDATA, SHRLEVEL REFERENCE,
or SHRLEVEL NONE, and only if you do not
specify UNLOAD PAUSE or UNLOAD ONLY.
- Specify SORTKEYS to pass index keys to sort
in memory rather than to work files. Avoiding
this I/O to the work files improves LOAD
performance.
- Specify whether to type values for OFFPOSLIMIT
and INDREFLIMIT and whether to generate a
report only, without actually reorganizing.
- In the OFFPOSLIMIT field, type a value that
helps determine if you should REORG your
table space. The default value is 10. This
option is not valid for LOB table spaces. The value you
type is compared against the result of the
calculation (NEAROFFPOSF + FAROFFPOSF) ×
100 / CARDF for the specified partition(s)
in catalog table SYSIBM.SYSINDEXPART for
the explicit clustering index(es) for every
table in the table space specified. If the
calculated value exceeds the OFFPOSLIMIT
value that you have typed, REORG is performed
or recommended.
- In the INDREFLIMIT field, type a value that
helps determine if you should REORG your
table space. The default value is 10. This
option is not valid for LOB table spaces. The specified
value is compared against the result of the
calculation (NEARINDREF + FARINDREF) × 100
/ CARDF for the specified partition(s) in
catalog table SYSIBM.SYSTABLEPART for the
table space specified. If the calculated
value exceeds the OFFPOSLIMIT value that
you have typed, REORG is performed or recommended.
- 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 orginal 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. This is required for LOB table spaces.
- From After unloading the data select:
- Select Continue to include the reload phase of the reorganize
utility. Select only if RECOVER TABLESPACE
has terminated during reconstruction of a
page, because of an error. In this case,
the page is marked as broken. After you have
repaired the page, you can use Continue option to recover the page, starting from
the point of failure in the recovery log.
Select the Keepdictionary check box to compress data using an existing
dictionary. If you are satisfied with the
compression you are getting with an existing
dictionary, you can keep that dictionary.
This method saves you the processing overhead
of building the dictionary.
- Select Pause to include the reload phase of the reorganize
utility. The reorganization pauses after
unloading the table space into the unload
data set. You cannot use NOSYSREC and PAUSE.
You can restart the utility REORG using the
phase restart or current restart. Select
the Keepdictionary check box to compress data using an existing
dictionary. If you are satisfied with the
compression you are getting with an existing
dictionary, you can keep that dictionary.
This method saves you the processing overhead
of building the dictionary.
- Select Only to do the reoganize only without including
a reload phase.
- Select the EXTERNAL radio button unload the data and then end
the utility job and remove the status in
catalog table SYSIBM.SYSUTIL corresponding
to this utility ID. Data rows are decompressed,
edit routines are decoded, field procedures
are decoded, and SMALLINT, INTEGER, FLOAT,
DECIMAL, DATE, TIME, and TIMESTAMP columns
are converted to external format. Validation
procedures are not invoked.
[Return to main task ]

The Data Sets page contains the data set template information,
or data definition statements, that will
be used by the REORG utility. Some of these
data sets templates are required depending
on the options specified on options page
of the Reorganize Table Space on Object List notebook.
- Specify the unload data set template. The
created data set will contain the data to
be unloaded. The default name is SYSREC.
The data set must be a sequential data set
that is readable by the MVS BSAM access method.
- Optional: You can specify 2 copy data sets,
primary and backup data sets. These data
sets contain image copy data sets. The default
is SYSCOPY for the primary copy. A full image
copy data set is created for the table specified
when REORG executes. The table space for
which an image copy is produced is not placed
in COPY pending status.
- Optional: You can specify 2 copy data sets
at the recovery site, primary and backup
data sets. These data sets contain image
copy data sets.
- The temporary work files for sort input and
output (SYSUT1 and SORTOUT) data sets are
required when:
- the tables have indexes
- SORTKEYS on the options page of this notebook
is specified with no estimate or an estimate
of 0
- Optional: You can specify a punch data set
template to receive the LOAD utility control
statements generated by REORG TABLESPACE
UNLOAD EXTERNAL or REORG TABLESPACE DISCARD
FROM TABLE. The default is SYSPUNCH. PUNCHDDN
is required if the last partition of a partitioned
table space has had its limit key reduced.
- Optional: You can specify a discard data
set template.
- You can specify the device type for temporary
data sets to be dynamically allocated by
DFSORT. This can be any device type that
is acceptable to the DYNALLOC parameter of
the SORT or OPTION options for DFSORT.
- If you select to use DFSORT temporary data
sets, then you must type a number of data
sets to be dynamically allocated by the sort
application program. This is used only if
a device type is selected in the previous
step.
- Specify to preformat the remaining pages
up to the high allocated RBA in the table
space and indexspaces. The preformatting
occurs after the data has been loaded and
the indexes are built. PREFORMAT can operate
on an entire table space and its index spaces,
or on a partition of a partitioned table
space and its corresponding partitioning
index space. This is not valid if you selected
the ONLY radio button on the Options page
for doing a REORG only.
[Return to main task ]

The Statistics options page contains information on gathering statistics
stored in the DB2 catalog.
- Specify the Report statistics radio button to determine if a set of messages
is generated to report the collected statistics.
The default, NO, means that output is not
sent to SYSPRINT. Yes means that output is
sent to SYSPRINT.
- Specify whether the collected statistics
are inserted into the catalog tables. Select
the Access path and space statistics radio button update all collected statistics
in the catalog. Select the Access path statistics only radio button to update only the catalog
table columns that provide statistics used
for access path selection. Select the Space statistics only radio button to update only the catalog table
columns that provide statistics to help the
database administrator assess the status
of a particular table space or index. You
can also choose to update no catalog table
columns. This is only valid when you have
selected YES on the Report statistics radio button.
[Return to main task ]

Related information: