Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003

    Unanswered: How to reclaim unused disk space (compact) back to operating system?

    using DB2 v9.5 on Linux. We are using SMS tablespaces for data. Our database is 100 GB in size (measured by Linux du command on directory database is stored on). I have noticed that if I create a backup and restore it to my second-test hardware the database directory size measured with du command after restore is 35 GB. So all 65 GB (=100 GB - 35 GB) usage are used by unused pages under DB2 database.

    Is there any other way except backup/restore to reclaim disk space back to operating system?

    P.S. I have noticed when table is deleted and reorg is done, disk space is not reclaimed to operating system. The only way to reclaim is to drop table - this is logical, because in SMS tablespace each table is a single operating system file and when table is dropped file is deleted and so reclaimed back to operating system. The only problem is, I don't want to delete tables - also export/drop/import is too time consuming - in this case it is better to do backup/restore of whole database.

  2. #2
    Join Date
    Jan 2003
    Provided Answers: 5
    REORG should reclaim the space.


  3. #3
    Join Date
    Jan 2003
    Provided Answers: 5
    Just to confirm that REORG will reclaim space, I did the following:

    1) create table in a SMS tablespace (du -h: 84GB)
    2) load 54,297,870 rows into the table (du -h: 112GB)
    3) create index on 2 rows of table (du -h: 113GB)
    4) delete 5,496,160 rows (just over 10%) (du -h: 113GB)
    5) reorg table (du -h: 110 GB)
    6) drop table (du -h: 84 GB)

    The table was 28GB fully loaded. Deleting about 10% of the rows, and then reorging yielded about 3GB (just over 10%) disk reduction.


  4. #4
    Join Date
    Aug 2008
    Toronto, Canada
    This was written in 2001 (old, but I think most still apply):

    What happens to space after a mass delete ? drop table ?

    1. For a DROP TABLE
    - SMS Tablespace - the files are truncated and the space is returned to the filesystem on COMMIT
    - DMS Tablespace - the extents are freed and are reusable (but perhaps on immediately) after COMMIT

    2. For a mass delete of rows
    - The space is freed and left owned by the table/indexes. The space is reusable for future inserts into the same table. Only a REORG TABLE can reclaim the deleted space and give it back to the tablespace like the DROP TABLE case does.

    Deleting rows from a table frees up space in the table/indexes, but does not make this space available to the tablespace. SMS files stay the same size, DMS tablespace has no additional free extents. The space is reused on future inserts.

    The only way to physically free this space up is to reorg the table (alternatively is to import/load replace an empty file). If there is no clustering index and the reorg is issued without an INDEX option - then a relational scan will be done - scanning all the empty pages. If a clustering index does exist or the INDEX option is specified, then the table will be reorg'd using the index. pre-V8 the default is to use the index, but if registry variable DB2REORGSCANSORT is set then it will perform a table scan (again, looking at all the empty pages). Ensure this registry variable is not set and a reorg of the table using an index will use the index to reorg the table. The index (although taking up a lot of space) would have no or few rows in it after a mass delete, so should perform very fast.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts