How to redirect a REORG to another drive

Question: I’m attempting to complete a REORG on tables and indexes but don’t have enough disk space for the REORG activities? Is it possible to redirect a REORG ?

The error message in the dbdiag.log is “ADM6017E The underlying file system is full”

Answer: It is possible to redirect the REORG to a TEMPORARY TABLESPCE on another drive , execute the REORG statement and utilise the TEMPORARY TABLESPCE

The basic steps to redirect a DB2 REORG to another drive:

a) provision another drive with sufficient space. In the example below – it’s X:\reorg_temp

b) Create TEMPORARY TABLESPACE . In the example below called REORG

c) Execute the statements

db2 "CREATE SYSTEM TEMPORARY TABLESPACE REORG PAGESIZE 16K MANAGED BY SYSTEM USING ('X:\reorg_temp') BUFFERPOOL   IBMDEFAULTBP DROPPED TABLE RECOVERY OFF"
 db2 force application all 
db2stop 
db2start 
db2 connect to MY_DB 
db2 update db cfg for MY_DB using auto_tbl_maint off 
db2 "reorg table MY_DB.MY_TABLE allow no access use reorg" 
db2 "drop tablespace reorg" 
db2 update db cfg for MY_DB using auto_tbl_maint on
db2 "RUNSTATS ON TABLE MY_DB.MY_TABLE WITH DISTRIBUTION AND SAMPLED DETAILED INDEXES ALL"

Warning: before taking these steps make sure there is a valid Backup and that enough detailed attention is taken on the amount of disk required for a REORG

Extra Reading on DB2 REORG

Reorg table DB2

DB2 table reorg and table truncate

DB2 Used Space and REORG