Friendship between REORG and Temp Tablespace's

Let us look into what db2 does when a traditional classic offline REORG on a table is issued

When you issue the REORG TABLE DB2INST1.EMPLOYEE the table EMPLOYEE will go through the following phases shown in the image

Let's go through what actually is happening in these phases

SORT : During this phase if you mention the INDEX with the REORG , sorting will be done based on the INDEX mentioned , otherwise a default table scan sort will be taking place

For Ex :

REORG TABLE DB2INST1.EMPLOYEE INDEX DB2INST1.EMP_INDX

This will sort the table according to the rows in the index DB2INST1.EMP_INDX

REORG TABLE DB2INST1.EMPLOYEE

This will go for the table scan sort

BUILD : During this phase entire table is reorganized either in the tablespace the table is residing or the system temporary tablespace mentioned

REPLACE : During this phase the table is replaced by the sorted table in the temporary tablespace

RECREATE ALL INDEXES : During this phase all the indexes defined on the table are recreated

We can also the phase the table is going through with the db2pd utility or querying SYSIBMADM.SNAPTAB_REORG administrative view

• From SYSIBMADM.SNAPTAB_REORG

  db2 "SELECT TABNAME,PAGE_REORGS,REORG_PHASE,REORG_MAX_PHASE,REORG_CURRENT_COUNTER,REORG_MAX_COUNTER from SYSIBMADM.SNAPTAB_REORG"
  
  TABNAME             PAGE_REORGS          REORG_PHASE      REORG_MAX_PHASE REORG_CURRENT_COUNTER REORG_MAX_COUNTER   
  ---------------------------------------- ---------------- --------------- --------------------- --------------------
  EMPLOYEE                               3 INDEX_RECREATE                 3                   524                 1083
  
  

• From utility db2pd

  db2pd -db sample -reorg
  
  Database Partition 0 -- Database SAMPLE -- Active -- Up 72 days 03:19:25
  
  Table Reorg Information:
  Address    TbspaceID TableID PartID MasterTbs MasterTab TableName          Type    IndexID    TempSpaceID
  0x36EE10BC 2         271     n/a    n/a       n/a       EMPLOYEE          Offline     0          2
  
  
  Table Reorg Stats:
  Address    TableName          Start               End                 PhaseStart          MaxPhase   Phase      CurCount   MaxCount   Status  Completion
  0x3719A6BC EMPLOYEE         01/03/2014 14:12:51 n/a                 01/03/2014 14:12:52      3      IdxRecreat    524          1083       Started    0
  

If you see there is some important information to be looked into

• REORG_PHASE : Tell's us which phase of the REORG is going on i.e., SORT,BUILD,REPLACE or RECREATE INDEXES

  In our example the time i looked the db2pd utility the REORG is going through IdxRecreat phase

• REORG_MAX_PHASE : Tell's us how many phases the REORG utility undergo , we have not mentioned any INDEX while doing a REORG utility so it will omit the SORT phase , so 3 phases
• REORG_CURRENT_COUNTER : current counter the REORG is in phase of recreating index to reach its maximum counter
• REORG_MAX_COUNTER : Maximum counter the REORG has to reach to complete that phase

Watching the REORG_CURRENT_COUNTER and REORG_MAX_COUNTER we can come to know how much that particular phase is completed

Things to be followed while doing a REORG

System temporary tablespaces are very good friends of REORG utility , you need to be a good friend of them too

In my next post we will see the how the system temporary tablespace will save us from crisis in reorg activity and some scripts for creating them

Have a Nice Day