Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2008
    Posts
    76

    Thumbs up Unanswered: How can we convert SMS tablespace to DMS Tablespace (Automatic Storage)

    Hi Friends,

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

    Thanks
    Pandith

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    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.
    Last edited by db2girl; 02-06-09 at 02:50.

  3. #3
    Join Date
    Aug 2008
    Posts
    76

    Thanks a lot db2girl.

    Can you please explain a bit more about this??

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    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.

  5. #5
    Join Date
    Aug 2008
    Posts
    76

    Cool Thank you very very much

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


    Thanks
    Pandit

  6. #6
    Join Date
    Jan 2006
    Posts
    23
    this was what i was searching for..

  7. #7
    Join Date
    Aug 2004
    Posts
    24
    backup the table in this tablespace and then drop and recreat the tablespace and import the table.
    I am a java and database developer.

Posting Permissions

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