we are periodically doing the TRUNCATE of some large tables in database. After that in order to release disk space we are doing the command:

alter tablespace MAXDATA reduce max

and everything works smoothly.

I have a question if for example database size iz 100 GB, and one table size is 5GB and we execute TRUNCATE command on that table, I understand that it will not release database size back to 95 GB in order to decrease it for 5 GB we must execute ALTER but will it stop the growth of database size for new data which will be populated? So does it mean that database size will be 100GB and after new 5GB data in that same table is being generated it will not trigger additional 5GB of space (105 GB) but it will keep the 100GB size.

Please provide me explanation for this.



The fact that you are using the alter tablespace ... reduce max syntax indicates that you're using automatic storage (or DMS) tablespaces.

Knowing that, truncating a table only frees up the space that the table occupies within a tablespace. (i.e., Db2 releases the extents that are assigned to the table.)

The reason Db2 can't just give back the space that was freed in the tablespace automatically is because there is no guarantee that the extents that were allocated to the table were located contiguously at the "end" of the tablespace.

Therefore, when you deallocate space in the tablespace (via alter tablespace ... reduce max), Db2 must first "fill in the holes" by moving other in-use extents into the unused extents that were freed when you truncated the table.

See the documentation for reclaimable storage for more details and some pictures of this.

| improve this answer | |
  • Hi Ian, so basically in my example it will mean I will have 105 GB and not 100GB if alter is not being executed after the Truncate, correct? – Dejan May 9 '18 at 10:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.