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

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

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?

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.

Thanks - I'll try.