If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Db2 10.1 - SMS to Automatic storage db conversion

We are converting all our SMS and DMS tablespaces into Automatic storage using Redirect restore..

We have no issues converting DMS to Automatic storage..

For two ( USERSPACE and TEMPSPACE ) of the three SMS tablespaces, we were able to convert into automatic
storage using db2move export, drop tablespaces , create tablespaces as automatic storage and db2move import..

The issue is with the SYSCATSPACE SMS tablespace... If we follow the above approach, the second step -dropping SYSCATSPACE tablespace - would leave us dead in the water once the catalog is dropped..

Did any one experience the similar issue and figure out the way to resolve this issue ?

We would want to move all SMS/DMS tablespaces to automatic storage so that we don't have to worry about the next (DB2 10.5 )upgrade where non-automatic storage tablespaces are discontinued.

If we take the "move tables to a different tablespace" approach, here is my concern.
After we move all the SYSCATSPACE tables into a new tablespace followed by DROP tablespace SYSCATSPACE, my guess is that I wouldn't be able to access database as DB2 would be looking for all the catalog information in that specific SYSCATSPACE tables

I have a feeling that I am not doing something right.. Why wouldn't IBM not document in their online manuals on how to convert SMS catalog tablespaces into automatic storage tablespaces..

Please point me to a document where IBM specifically talks about converting SMS catalog tablespace into automatic storage one, if you see one....

Last edited by Manidba49; 04-17-14 at 10:51.
Reason: INCOMPLETE POSTING

You can only convert DMS tablespaces to automatic storage. And I don't think you can actually drop SYSCATSPACE. As mark.b said, create a new database using automatic storage them move all user data to it.

Non-automatic tablespaces is depricated except for the catalog and temporary tablespaces:

"Important: The SMS table space type has been deprecated in Version 10.1 for user-defined permanent table spaces and might be removed in a future release. The SMS table space type is not deprecated for catalog and temporary table spaces"

I took this from the create tablespace documentation for db2 10.5. When it comes to the catalog tablespace I would not move it unless it is required. I am also leaving my temporary tablespaces as SMS. I would imagine IBM will document moving the catalog when it is required in the future.