Problem solve Get help with specific problems with your technologies, process and projects.

When do we need to reorganize a table? Part II

When do we need to reorganize a table? We are currently under version 5 and next month we are going for version 7.

For the first part of this answer, please see part I of this question

NEAROFFPOSF and FAROFFPOSF are measures to gauge the organization of the
data in the underlying table. It assumes that the index in question is the
clustering index. The values indicate how many of the rows in the table are
ill-placed given that assumption. If the index is not the clustering index,
FAROFFPOSF and NEAROFFPOSF are not useful as indicators of data

The NEARINDREF and FARINDREF columns for a tablespace indicate the number of
rows that have been relocated either near (2 to 15 pages) or far away (16 or
more pages) from their original location. This relocation (also called
indirection) can occur as the result of updates to variable length rows
(that is, rows with VARCHAR columns, tables with EDITPROCs, or compressed

LEAFDIST helps determine the relative efficiency of each index. LEAFDIST
indicates the average number of pages between successive index leaf pages.
The more intervening pages, the less efficient the index will be.

Finally, you can use CLUSTERRATIOF to determine the overall condition of the
index as it corresponds to the physical order of the tablespace data. The
more clustered an index is, the greater its conformance to the order of the
rows as they are physically aligned in the tablespace. A cluster ratio of
100 percent indicates that the index and the tablespace ordering matches
exactly. As the cluster ratio diminishes, access that uses the index becomes
less efficient.

Also, please note that CLUSTERRATIOF for partitioned indexes can be found in
SYSIBM.SYSINDEXSTATS. This CLUSTERRATIOF is at the partition level and can
help to determine if only a subset of the partitions needs to be

The following table can be used as guide to determine how frequently
tablespaces and indexes should be reorganized. A + indicates that you should
REORG more frequently as the value in that column gets larger. A - (subtract
character) indicates that you should REORG more frequently as the value gets
smaller. As the number of + or - characters increases, the need to REORG
becomes more urgent. For example, as PERCDROP gets larger, the need to REORG
is very urgent, as indicated by five plus signs. For CLUSTERRATIOF, as the
value gets smaller, the need to REORG increases.

Table Reorganization indicators.

Column	Object	 Impact
----------- -----------  ----------
PERCDROP      Tablespace + + + + +
NEAROFFPOSF   Tablespace +
FAROFFPOSF    Tablespace + + + +
NEARINDREF    Index      +
FARINDREF     Index      + + + +
LEAFDIST      Index      + + +
CLUSTERRATIOF Index      - - - - -

(Note: the above information is culled from my book, DB2 Developer's Guide.
It can be ordered online at the following link:

Dig Deeper on IBM DB2 management

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.