AIX
UDB 8.2 FP-10

All our databases created with the combination of DMS & SMS tablespaces with different set of containers for each TS. Now we are planning to change all the TS's to SMS and each TS spreaded all over the contaners. Can you advise me which way is best to complete this task? We have 70+ databases in development,integration and system test environments to do this.

I am not sure whether REDIRECT-RESTORE works for converting the TS's from DMS to SMS. Please clarify.

Thank you,
-Ram

Redirected restore won't allow you to change tablespace types.

Unfortunatley the only way to do it is to

EXPORT
DROP
CREATE
IMPORT/LOAD

db2move may help you do this, or you can script it yourself.

Making it more annoying, as db2 drops your foreign keys and other dependencies when you drop the table... so you have to make sure you redefine those when you create it again. db2look is very useful for that, if you don't already have the ddl somewhere.

Oh, one other option is RENAME TABLE, then CREATE, and INSERT...SELECT.

Only problem is if you're moving a lot of data, you could fill up the logs, or it could take an extraordinary amount of time.