Personal tools
You are here: Home DB2 How To's What is the 4 phases of Offline Reorg
Navigation
Log in


Forgot your password?
 
Document Actions

What is the 4 phases of Offline Reorg

sort - build - replace - recreate all indexes



There are four phases in a classic or offline table reorganization:

   1. SORT

      If an index is specified with the REORG TABLE command, or if a clustering index is defined on the table, the rows of the table are first sorted according to that index. If the INDEXSCAN option is specified, an index scan is used to sort the table, otherwise, a table scan sort is used. This phase only applies to a clustering REORG. Space reclaiming reorganizations begin at the build phase.

   2. BUILD

      In this phase, a reorganized copy of the entire table is build, either in the table space that the table being reorganized resides, or in a temporary table space specified with the REORG command.

   3. REPLACE

      In this phase, the original table object is replaced by either copying back from the temporary table space, or by pointing to the newly built object within the table space of the table being reorganized.

   4. RECREATE ALL INDEXES

      All indexes defined on the table are recreated

You have two command two monitor the reorg on db2 V9:



db2pd -d <dbname> -reorg

or

db2 "select * from SYSIBMADM.SNAPTAB_REORG"

or

SELECT SUBSTR(TABNAME, 1, 15) AS TAB_NAME, SUBSTR(TABSCHEMA, 1, 15) 
AS TAB_SCHEMA, REORG_PHASE, SUBSTR(REORG_TYPE, 1, 20) AS REORG_TYPE,
REORG_STATUS, REORG_COMPLETION, DBPARTITIONNUM
FROM SYSIBMADM.SNAPTAB_REORG ORDER BY DBPARTITIONNUM


Where SYSIBMADM.SNAPTAB_REORG is a table that contain the following data:

Column                         Type      Type
name                           schema    name               Length   Scale Nulls
------------------------------ --------- ------------------ -------- ----- ------
SNAPSHOT_TIMESTAMP             SYSIBM    TIMESTAMP                10     0 Yes  
TABNAME                        SYSIBM    VARCHAR                 128     0 Yes  
TABSCHEMA                      SYSIBM    VARCHAR                 128     0 Yes  
PAGE_REORGS                    SYSIBM    BIGINT                    8     0 Yes  
REORG_PHASE                    SYSIBM    VARCHAR                  16     0 Yes  
REORG_MAX_PHASE                SYSIBM    INTEGER                   4     0 Yes  
REORG_CURRENT_COUNTER          SYSIBM    BIGINT                    8     0 Yes  
REORG_MAX_COUNTER              SYSIBM    BIGINT                    8     0 Yes  
REORG_TYPE                     SYSIBM    VARCHAR                 128     0 Yes  
REORG_STATUS                   SYSIBM    VARCHAR                  10     0 Yes  
REORG_COMPLETION               SYSIBM    VARCHAR                  10     0 Yes  
REORG_START                    SYSIBM    TIMESTAMP                10     0 Yes  
REORG_END                      SYSIBM    TIMESTAMP                10     0 Yes  
REORG_PHASE_START              SYSIBM    TIMESTAMP                10     0 Yes  
REORG_INDEX_ID                 SYSIBM    BIGINT                    8     0 Yes  
REORG_TBSPC_ID                 SYSIBM    BIGINT                    8     0 Yes  
DBPARTITIONNUM                 SYSIBM    SMALLINT                  2     0 Yes

 

Security Awareness
Would you like your company to implement gamification into your security awareness program?





Polls