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