Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    California,USA
    Posts
    72

    Unanswered: change the TS's retaining the data

    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

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    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.
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    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.
    --
    Jonathan Petruk
    DB2 Database Consultant

Posting Permissions

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