What are the different phases of Online & Offline Reorg in DB2 ?

Different Phases of Reorg:

Basically Reorg sorts the data using index scan or table scan and moves the records from one page to a previous page which has some free space due to deletion of rows from a table to reclaim the space.

There are "TWO" types of reorgs:

• ==> Offline reorg or Classic reorg
• ==> Online reorg or Inplace reorg

Each reorg type consists of different phases of execution.

Phases of Offline or Classic reorg:

There are total "FOUR" phases in Offline or Classic reorg.

SORT:

In this phase the rows of the table sorted by using an index scan if there is any cluster index on the table or if we specify index with reorg command or else it uses the table scan to sort the rows. This phase is applicable for only clustering reorg. 

BUILD:

Builds the copy of the reorganizing table in the tablespace where the table resides or in a temporary tablespace specified in reorg command.

REPLACE:

By copying the organized data from the temporary tablesapce or by pointing to the new build objects in the tablespace where the table is being reorganized. 

RECREATE ALL INDEXES:

In this phase all the indexes defined on the table are recreated.

Phases of Online or Inplace reorg:

Online reorganization also have "FOUR" phases

SELECT n PAGES:

The database manager selects 'n' pages for the reorganization, which is minimum of 32 sequential pages.

VACATE THE RANGE:

In this phase database manager moves rows within this range to the free space on the table. In this phase it uses Reorg Pointer to point the rows new location using RID. After moving some data the utility waits for the applications to be finished, the current applications accesses the data using old RIDs. The application which starts newly is going to use the new RIDs to access the data. 

Fill the range:

After vacating the rows from the selected range database manager starts writing data in an organized manner, sorted according to the indexes. Once the all the pages in the range have been rewritten, the process repeated for the next 'n' sequential rows.

Truncate the table:

Once all pages reorganized in the table, to reclaim the space the database manager truncates the table by default. If you want the table not to be truncated, then you can use NOTRUNCATE option.