IBM Support

100 Tech Tips, #100 CLASSIC REORG vs INPLACE REORG

Technical Blog Post


Abstract

100 Tech Tips, #100 CLASSIC REORG vs INPLACE REORG

Body

There are two approaches to table reorganization: classic reorganization (offline) and inplace reorganization (online).

During offline reorganization as the name suggests, you do not have access to the table where as an inplace table reorganization enables you to reorganize a table while you have full access to its data. To specify an online reorganization operation, use the INPLACE option on the REORG TABLE command.

Classic table reorganization uses a shadow copy approach, building a full copy of the table that is being reorganized.

During an inplace or online table reorg operation, portions of a table are reorganized sequentially. Data is not copied to a temporary table space; instead, rows are moved within the existing table object to reestablish clustering, reclaim free space, and eliminate overflow rows.


Each Operation has its advantages and disadvantages: 

Advantages of offline reorganization

  • The fastest table reorganization operations, especially if large object (LOB) or long field data is not included
  • Perfectly clustered tables and indexes upon completion
  • Indexes that are automatically rebuilt after a table has been reorganized; there is no separate step for rebuilding indexes
  • The use of a temporary table space for building a shadow copy; this reduces the space requirements for the table space that contains the target table or index
  • The use of an index other than the clustering index to re-cluster the data

Disadvantages of offline reorganization

  • Limited table access; read access only during the sort and build phase of a reorg operation
  • A large space requirement for the shadow copy of the table that is being reorganized
  • Less control over the reorg process; an offline reorg operation cannot be paused and restarted

Advantages of online reorganization

  • Full table access, except during the truncation phase of a reorg operation
  • More control over the reorg process, which runs asynchronously in the background, and which can be paused, resumed, or stopped; for example, you can pause an in-progress reorg operation if a large number of update or delete operations are running against the table
  • A recoverable process in the event of a failure
  • A reduced requirement for working storage, because a table is processed incrementally
  • Immediate benefits of reorganization, even before a reorg operation completes

Disadvantages of online reorganization

  • Imperfect data or index clustering, depending on the type of transactions that access the table during a reorg operation
  • Poorer performance than an offline reorg operation
  • Potentially high logging requirements, depending on the number of rows being moved, the number of indexes that are defined on the table, and the size of those indexes
  • A potential need for subsequent index reorganization, because indexes are maintained, not rebuilt
 
To monitor an OFFLINE REORG you can use one of the following methods:
 
1. GET SNAPSHOT FOR TABLES command
  • db2 get snapshot for tables on sample
 
2. Starting DB2 V9.5 FP5 and DB2 V9.7 FP1 you can you the db2pd -reorg command 
  • db2pd -db sample -reorg
 
3. List History Reorg:
  • db2 list history reorg all for sample
 
4. Select from the SNAPTAB_REORG administrative view or the SNAP_GET_TAB_REORG table function and check the REORG_TYPE
  • db2 " SELECT SUBSTR(TABNAME, 1, 15) AS TAB_NAME, SUBSTR(TABSCHEMA, 1, 15) AS TAB_SCHEMA, SUBSTR(REORG_TYPE, 1, 20) AS REORG_TYPE, REORG_STATUS, REORG_COMPLETETION FROM SYSIBMADM.SNAPTAB_REORG"
 
To Monitor an INPLACE REORG, you can use:
 
1. Select from the SNAPTAB_REORG administrative view or the SNAP_GET_TAB_REORG table function and check the REORG_TYPE
 
2. ADMIN_GET_TAB_INFO table function and look at the INPLACE_REORG_STATUS for an active Inplace Reorg
  • db2 "SELECT SUBSTR(TABNAME, 1, 15) AS TAB_NAME, SUBSTR(TABSCHEMA, 1, 15) AS TAB_SCHEMA,INPLACE_REORG_STATUS FROM TABLE (SYSPROC.ADMIN_GET_TAB_INFO('DB2V97','EMPLOYEE'))"
3. List History Reorg:
  • If the type is 'N' ,this indicates an inplace reorg: 
           Eg: Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID

                  --   --- ------------------                 ---- --- ------------ ------------ --------------
                  G  T  20070531154113            N S0000000.LOG
                 ----------------------------------------------------------------------------  
                 Table: "JOHN"."MYTAB1"
                 ----------------------------------------------------------------------------
                 Comment: REORG START
                 Start Time: 20070531154113
                 End Time: 20070531154113
                 Status: A
                 ----------------------------------------------------------------------------
                 EID: 1

 
 
 

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11141630