Determining whether an object requires reorganization

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.

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