Hi Friends,

Is it possible to convert SMS tablespace to a DMS tablespace with Automatic Storage ? Please, let me know the chances.

Thanks
Pandith

You can't convert from SMS to DMS using alter or restore. You will need to create a new DMS tablespace and copy the objects/data manually.

Can you please explain a bit more about this??

You can use db2look to generate the DDL for tables and dependent objects. Then, create the tables only (names have to change) in the new tablespace and copy the data. To copy the data, you have a choice:
load from a cursor
export/load
db2move

Once the new tables have been populated, you can drop the old tablespace, rename new tables to their original names and then create their dependent objects (indexes, etc...)

If you're on v9, there is an easier way to accomplish this using the admin_copy_schema procedure. Here is an example:

$ db2 "call sysproc.admin_copy_schema ('TEST','TEST2','COPY',NULL,'TEST','TEST2','COPYSC HEMA2','COPYERROR')"

Value of output parameters
--------------------------
Parameter Name : ERRORTABSCHEMA
Parameter Value : COPYSCHEMA2

Parameter Name : ERRORTABNAME
Parameter Value : COPYERROR

Return Status = 0


Where:
test - the source schema and also source tablespace name
test2 - the target schema and also target tablespace name


This will copy the object from one tablespace into another within the same database. The schema will be changed.

Take a database backup before dropping anything... just in case.

Your answer would really help me a lot. Thanks again for your very explanative
description.....


Thanks
Pandit

this was what i was searching for..

backup the table in this tablespace and then drop and recreat the tablespace and import the table.