DB2 : Claim back space from dropped tables

By | February 8, 2017

Especially when creating a demo image of an enterprise system we want to have a smaller footprint than the full system that was cloned.
The same is true when the image is used for volume benchmarks. Between different benchmark sessions we want to clean the old test data and maybe in the end we want to store the image for further use or ship it to some other location.

The first step is to find which are the tables that can be cleaned. The first inputs is to use truncate but mind that in case of benchmarks we tend to have huge volumes and sometimes a truncate operation (that creates recover logs) can be very slow if we are getting read of GBs of data. The best way to do it is to drop the tables.

STEP 1: Drop tables
To drop the tables connect to the MyDB database and drop the tables using the instance name.

db2 connect to MyDB

STEP 2: Clean the disk space
After dropping a table when checking the space on disk we can see that the space is still allocated in the tablespace. It means that the data was just marked as dirty but not actually deleted.
We have to instruct db2 to free the disk. This is done by reducing the tablespace. Our tablespace is the default “userspace1” so the following alter will do the trick.

db2 alter tablespace userspace1 reduce max

Then wait for the space to be freed. Note that this will take some time, the reducing operation is quite slow.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.