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
- db2 get snapshot for tables on sample
- db2pd -db sample -reorg
- db2 list history reorg all for sample
- 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"
- 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'))"
- If the type is 'N' ,this indicates an inplace reorg:
-- --- ------------------ ---- --- ------------ ------------ --------------
G T 20070531154113 N S0000000.LOG
----------------------------------------------------------------------------
Table: "JOHN"."MYTAB1"
----------------------------------------------------------------------------
Comment: REORG START
Start Time: 20070531154113
End Time: 20070531154113
Status: A
----------------------------------------------------------------------------
EID: 1
UID
ibm11141630