Why doesn't DELETE + REORG free diskspace (DB2)?

Question

In DB2 I have a table containing large binary data. Now i purged the whole table and ran runstats, reorg, runstats, but the amount of disk space taken does not change. What could be wrong here?

The table resides in its own tablespace which I created as follows:

CREATE BUFFERPOOL "MY_BP" SIZE 250 AUTOMATIC PAGESIZE 4096;
CREATE LARGE TABLESPACE MY_TBS IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 4096 MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 64 PREFETCHSIZE 64 BUFFERPOOL MY_BP OVERHEAD 10.500000 TRANSFERRATE 0.140000 FILE SYSTEM CACHING;

I deleted/reorged as follows:

DELETE FROM MY_TBL
RUNSTATS ON TABLE MY_TBL WITH DISTRIBUTION AND DETAILED INDEXES ALL
REORG TABLE MY_TBL
RUNSTATS ON TABLE MY_TABLE WITH DISTRIBUTION AND DETAILED INDEXES ALL
ALTER TABLESPACE MY_TBS REDUCE

The table MY_TBL took up 2.5GB before all that and after deleting/reorging it uses only 3 MB less.

FWIW: I'm running DB2/NT v9.5.2.

Answer 1

I'm going to take a guess that you are using automatic storage. (Not that this could happen otherwise...it is just easy to have this happen with automatic storage.)

The problem is most likely that your database reclaimed the space for itself but did not release the disk back to the operating system. This can be shown very easily by checking the High Water Mark for the tablespace.

Do a the following

db2 list tablespaces show detail

This will show you each tablespace and what it is using on disk. Used pages is how many pages of disk the database is using. Comparing that against total pages (the total claimed on disk) and the High water mark (pages) will show you if you are "claiming" more than you actually need. (ie, low used pages, very high total pages and a High Water Mark close to the total pages).

To get rid of this unused space and return it to the operating system you would issue the following (under automatic storage): db2 alter tablespace <tablespace name> reduce max. example

db2 alter tablespace ts1 reduce max;

That will cause DB2 to lower the high water mark and release the unused disk back to the operating system. (Note you can only do this for regular and large tablespaces, not for system temporary, or user temporary tablespaces).

If you are using DMS without automatic storage you need to use a slightly different set of commands:

db2 alter tablespace <tablespace name> lower high water mark;
db2 alter tablespace reduce (<containter name> or [all containers] integer K|M|G or integer PERCENT);

example

db2 alter tablespace ts1 lower high water mark;
db2 alter tablespace reduce (all containers 500 M);

Where we work, we put this into some of our maintenance scripts so that we automatically run this after we do reorgs to make sure we reclaim disk space. In our case we use DB2 LUW 9.7 FP 4, so it doesn't hurt to double check Information Center for 9.5 to make sure you have access to the right information for your version.

EDIT: If your tablespaces came from a database upgraded to DB2 9.7, you probably will not have the reclaimable storage attribute set. This is true even if you upgrade from DMS to automatic storage. Either way bites as you cannot actually lower the high water mark. You have to dump the table and data out, drop the tablespaces. Then re-create the tablespace using automatic storage and import the data for your tables.

Answer 2

The table MY_TBL contains large binary data in a BLOB column. The documentation of the REORG command says that DB2 avoids reorganizing such objects because it is time consuming and does not improve clustering. However, DB2 can be forced to reorganize LOB data if the LONGLOBDATA option is specified. The unused space can be reused by DB2, so inserting new data will first fill the existing, unused pages before allocating new.

Running

REORG TABLE MY_TBL LONGLOBDATA

successfully reclaimed the 2.5GB of disk space that the empty table was using.

I did not know about this option and oversaw it the first time I read the documentation.