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 are:
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