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