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:

    One:
    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.
        
    Two:

    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: Begin program-specific programming interface information.

    SELECT CARD, NEARINDREF, FARINDREF
       FROM SYSIBM.SYSTABLEPART
       WHERE DBNAME = 'XXX'
       AND TSNAME = 'YYY';
       
    For Example:
    SELECT IXNAME, IXCREATOR, FREEPAGE, LEAFDIST  
    FROM SYSIBM.SYSINDEXPART    
    WHERE LEAFDIST > 200 and IXCREATOR = 'DBPCHIR';    
    
IXNAME        IXCREATOR    FREEPAGE    LEAFDIST
APPIOTEA    DBPCHIR        0            233
OOPIOTEA    DBPCHIR        0            334
QCHIBATE    DBPCHIR        10            3167
QCHICPDA    DBPCHIR        10            15024

Because there are two records with FREEPAGE = 0, so there are two table space need to be reorg.





    End program-specific programming interface information.

    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.
    Begin program-specific programming interface information.

    SELECT PERCDROP
       FROM SYSIBM.SYSTABLEPART
       WHERE DBNAME = 'XXX'
       AND TSNAME = 'YYY';

    End program-specific programming interface information.
    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: Begin program-specific programming interface information.

    SELECT NEAROFFPOSF, FAROFFPOSF
       FROM SYSIBM.SYSINDEXPART
       WHERE IXCREATOR = 'index_creator_name'
       AND IXNAME = 'index_name';

   SELECT NEAROFFPOSF, FAROFFPOSF, IXNAME, IXCREATOR, FREEPAGE, LEAFDIST
   FROM SYSIBM.SYSINDEXPART
   WHERE IXCREATOR = 'DBPCHIR'
   AND IXNAME in
   ( SELECT IXNAME  
        FROM SYSIBM.SYSINDEXPART    
        WHERE LEAFDIST > 200 and IXCREATOR = 'DBPCHIR'
        );

NEAROFFPOSF    FAROFFPOSF    IXNAME        IXCREATOR    FREEPAGE    LEAFDIST
2            15            APPIOTEA    DBPCHIR        0            233
273            86            OOPIOTEA    DBPCHIR        0            334
42,369        59,983        QCHIBATE    DBPCHIR        10            3167
9,123        43,753        QCHICPDA    DBPCHIR        10            15024
   
     End program-specific programming interface information.

    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.