DB2 TableSpace Usage Actual Size vx Allocated Size

[kelleram]

I have a couple of tablespaces set up in which the actual size appears to be half the size of the allocated size. I have the tablespaces set to a maxsize of None, and increasesize of 5 M as space is needed. Why does the table end up getting to be soo big? With combining all of the tablespaces in this situation, I have a db whose actual size is 39 GB and the allocated space is 69 GB. I've been running runstats daily followed by reorg then a alter tablespace reduce to fix it. Sometimes it fixes it and gets it back down, other times it doesn't. Any suggestions?

[cburnett]

That factor of two leads me to think that you are doing an offline table reorg and not specifying a differnet tablespace. In this case, DB2 will create a new copy of the table in the same tablespace and then delete the original one (hence needs 2x the space). Same thing happens with an online index reorg.

If you are on V9.5, you can use the ALTER TABLESPACE REDUCE command to avoid outages.

Otherwise, you need to use db2dart to suggest corrective action. You may also need the -RHWM option if there are space map pages occupying the top end of the tablespace.