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
1) Ensure the server is provisioned correctly for : disk space
2) Regular backups are taken
3) Sufficient window for reorganization