Db2 reduce tablespace with ALTER TABLESPACE REDUCE

01 February,2013 by Jack Vamvas

There was some unexpected growth on a DB2 database server  during the business day.  Various jobs begin to fail due to disk issues . There was an immediate requirement to release  space to back to OS.

Sometimes unexpected growth occurs , regardless of scheduled reorganizations  and capacity planning.

The ALTER TABLESPACE REDUCE is useful in these circumstances.

Depending on whether the tablespace is set at automatic or non-automatic auto storage there is a different approach.

For non-automatic storage table spaces, specifies that existing containers are to be reduced in size

For automatic storage table spaces, specifies that the current high water mark is to be reduced, if possible, and that the size of the table space is to be reduced to the new high water mark

The ALTER TABLESPACE REDUCE syntax is :

 

db2 connect to mydb
db2 ALTER TABLESPACE USERSPACE1 REDUCE
db2 terminate

 

You may see the error message

 SQL1348W – The table space could not be reduced

 

In that case apply a reorg table which may allow below the watermark space release

 

Then proceed with ALTER TABLESPACE REDUCE

Other considerations

1)       Ensure the server is provisioned correctly for : disk space

2)       Regular backups are taken

3)       Sufficient window for reorganization

 Read More

DMS Container Space Full - DBA DB2

DB2 USERSPACE1 is full - DBA DB2

SQL0286N A default table space could not be found - DBA DB2

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

Subscribe to DBA-DB2.com

Latest posts delivered to you daily

Delivered by FeedBurner

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

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