Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2013

    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 ?

  2. #2
    Join Date
    Jul 2013
    Moscow, Russia
    Provided Answers: 55
    You have to recreate and reload the database to convert your SMS SYSCATSPACE to use automatic storage.

  3. #3
    Join Date
    Jan 2009
    Zoetermeer, Holland
    A redirected restore will not convert your tablespaces from SMS to DMS. You already found out the hard way . I would leave the TEMPSPACES SMS and for the catalog:

    Perhaps try to move the tables over to a new tablespace 1 by 1 using ADMIN_MOVE_TABLE procedure?

    or, play safe, leave that on SMS, just make sure that the associated buffer pool is large enough to eliminate the 'pain' of being stuck with SMS.
    SMS is not that bad.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  4. #4
    Join Date
    Apr 2013
    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 09:51. Reason: INCOMPLETE POSTING

  5. #5
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    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.
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Apr 2012
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts