How to redirect a REORG to another drive

03 August,2014 by Jack Vamvas

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

 

 

Author: Jack Vamvas(http://www.dba-db2.com)

Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

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.

Working...

Post a comment on How to redirect a REORG to another drive

Comments are moderated, and will not appear until the author has approved them.


dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright