Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Posts
    9

    Question Unanswered: REORG or ALTER Tablespace?

    Hi All

    Having about 3 hours of DB2 experience so far is proving to be a major hurdle.

    My Question:

    To Reduce the size of a tablespace in 8.1 on Windows and release the space to the operating system, should "Alter Tablespace reduce/resize" or should REORG be used? If Alter tablespace is the right choice, should a reorg be done beforehand? What I'm actually looking for is a script of some kind that can do something similar to DBCC ShrinkFile combined with sp_msForEachDB in SQL Server.

    I understand that REORG might increase the size of a tablespace based on "pctfree value" but will it reduce the size to something lower than it's orignal create size.

    Appreciate the help.


    Graham

  2. #2
    Join Date
    Jul 2002
    Posts
    21
    If you are using SMS tablespace then you can not alter the tablespace.. But if you are using DMS tablespace there are extend or reduce clauses or you can resize your containers

  3. #3
    Join Date
    Sep 2004
    Posts
    9
    Thanks.

    Does this mean that there's no way to shrink/resize an sms tablespace? Drop and recreate then? Reimport all the tables and make it a DMS next time? Do SMS Tablespace shrink automatically if you whack all the data in them?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think you can only reduce the size of a DMS container up to the high water mark; you can't go past it. In that sense DMS containers are similar to SMS ones - once space has been allocated it doesn't go back by itself.

    db2dart has an option, "LHWM" that "suggests ways of lowering the high water mark". I've never actually run it but I guess these suggestions might involve REORGing and/or dropping objects. A redirected restore might also help.

  5. #5
    Join Date
    Sep 2004
    Posts
    9
    Thanks - I'll try.

Posting Permissions

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