we are running DB2 9.5 on windows. Is there anyway to Change a System Managed Tablespace to a Database Managed Tablespace?

We have one Database where whoever created the DB made the Userspace1 Tablespace System Managed. I would like to make it a DMS tablespace so that I can reclaim over 30GB in storage!!! Does anyone have any suggestions? Change be done on a restore? Is there any other way to reclaim this space??

There is no way to directly change a SMS tablespace to DMS. You will have to create the DMS tablespace, then move all the objects in the SMS tablespace to the ne one.

What about doing a REORG to reclaim the space?

Andy

SMS (unlike DMS) automatically reclaims storage (but like DMS you may want to do a reorg).

The advantage of DMS is not space utilization, but slightly better performance in certain situations (but do not use DMS for system temporary tablespaces).

Do you mean reorging all the tables in the the tablespace to reclaim the space?? The problem is that there is 32 GB allocated to this SMS tablespace and unlike a DMS, I cannot alter and pare the size down.. So is my only option to create a new DMS tablespace and move all the object to it??

An SMS tablespace does not have specific amount of space allocated to it. It resides on a specified path (or paths) and automatically grows and shrinks as needed.

However, in for a page to be freed up, and for the tablespace size to shrink, all rows in the page must be deleted. If you reorg the table, then that will take care of the problem. Just do it.

Thanks! I'll give it a shot!!!

I was wrong about this being an SMS tablespace:

List Tablespace
Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000

I reorged all the tables in the Tablespace and I did a new snapshot. I still have 32GB allocated and only 7% utilization in the tablespace.. Still trying to figure out how to reclaim this 30GB ???

You need to resize the tablespace container(s), obviously. You may need to run db2dart with the /lhwm switch - it will suggest the ways to lower the high watermark of data in the tablespace.

By the way, this is the reason why you don't want your temporary tablespaces to be DMS.

Yes, and another important reason is that when DB2 creates a table in a DMS tablespace it spend extra time looking around to find the best place to put it to have optimum performance. This works well for regular tables that are created once and used many times, but the extra time to optimize the placement is a performance issue when DB2 creates temporary tables for sorting, etc during the execution of a query.

Do a "list tablespaces show detail" to determine the high water mark of the tablespace. You can resize the tablespace to the high water mark.

Please excuse my ignorance.. but how to do resize to the high water mark?? When I try and alter the tablespace via the control center... the option to change the size is not there like it is on the other DMS tablespaces....

Check out the SQL Reference Vol 2 manual for alter tablespace. You can use InfoCenter or get the PDF manuals for free.

alter tablespace userspace1 resize (all 10000)

returns this:

Table space "USERSPACE1" of type "AUTOMATIC STORAGE" cannot be altered using the "RESIZE

Just drop it and create a new SMS tablespace.

I guess you mean backup, drop then restore the tablespace?? Can I chance DMS to SMS and vice versa on a restore?