You must reorganize an object if it is in the REORG-pending
(REORP) restrictive status. Also, consider reorganizing an object
if it is in an advisory REORG-pending status (AREO* or AREOR) or if
analysis shows that reorganization might improve performance. Use
the REORG INDEX or REORG TABLESPACE utility to reorganize the object.
About this task
Recommendation: Run the RUNSTATS
utility if the statistics are not current. If the object should also
be reorganized, run REORG with STATISTICS and take inline copies.
If you run REORG PART and nonpartitioning indexes exist, subsequently
run RUNSTATS for each nonpartitioning index.
Procedure
To determine whether an object requires reorganization,
use any of the following approaches:
- Reorganize table spaces or partitions that are in REORG-pending
status. Use the DISPLAY DATABASE RESTRICT command
to display those table spaces and partitions that require reorganization.
- Run the REORG TABLESPACE utility and specify the OFFPOSLIMIT
and INDREFLIMIT catalog query options with the REPORTONLY option. REORG produces a report with one of the following return
codes, but the object is not reorganized.
- 1
- No limit met; no reorganization is performed or recommended.
- 2
- A reorganization is performed or recommended.
- Use the SYSTABLEPART and SYSINDEXPART catalog tables to
find which table spaces and indexes qualify for reorganization. The
information in these catalog tables can also be used to determine
when the DB2® catalog table spaces
require reorganization. For catalog table spaces SYSDBASE, SYSVIEWS,
and SYSPLAN, do not use the value for columns FAROFFPOSF and NEAROFFPOSF
of SYSINDEXPART to determine whether to reorganize.
Information
from the SYSTABLEPART catalog table can also indicate how well disk
space is being used. If you want to find the number of varying-length
rows that were relocated to other pages because of an update, run
RUNSTATS, and then issue the following statement:
SELECT CARD, NEARINDREF, FARINDREF
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = 'XXX'
AND TSNAME = 'YYY';
A large number (relative
to previous values that you received) for FARINDREF indicates that
I/O activity on the table space is high. If you find that this number
increases over a time, you probably need to reorganize the table space
to improve performance. You probably also need to increase PCTFREE
or FREEPAGE for the table space with the ALTER TABLESPACE statement.
The following statement returns the percentage of unused
space in nonsegmented table space YYY. In nonsegmented table spaces,
the space that is used by dropped tables is not reclaimed until you
reorganize the table space.
SELECT PERCDROP
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = 'XXX'
AND TSNAME = 'YYY';
Issue
the following statement to determine whether the rows of a table are
stored in the same order as the entries of its clustering index:
SELECT NEAROFFPOSF, FAROFFPOSF
FROM SYSIBM.SYSINDEXPART
WHERE IXCREATOR = 'index_creator_name'
AND IXNAME = 'index_name';
Several
indicators are available to signal a time for reorganizing table spaces.
A large value for FAROFFPOSF might indicate that clustering is deteriorating.
In this case, reorganize the table space to improve query performance.
A
large value for NEAROFFPOSF might indicate also that reorganization
might improve performance. However, in general NEAROFFPOSF is not
as critical a factor as FAROFFPOSF.
FAROFFPOSF and NEAROFFPOSF do not have query
performance considerations for the following
DB2 catalog tables:
- DSNDB06.SYSDBASE
- DSNDB06.SYSDBAUT
- DSNDB06.SYSGROUP
- DSNDB06.SYSPLAN
- DSNDB06.SYSVIEWS
What to do next
For any table, the REORG utility repositions rows into
the sequence of the key of the clustering index that is defined on
that table.
For nonclustering indexes, the statistical information
that is recorded by RUNSTATS in SYSINDEXES and SYSINDEXPART might
be even worse after the clustering index is used to reorganize the
data. This situation applies only to the CLUSTERING and CLUSTERED
columns in SYSINDEXES and to the NEAROFFPOS and FAROFFPOS columns
in SYSINDEXPART.