03 August,2014 by Tom Collins
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
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
DB2 table reorg and table truncate
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |