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