Db2 reduce tablespace with ALTER TABLESPACE REDUCE

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