Topic
4 replies Latest Post - ‏2013-01-14T10:56:52Z by Jotac
Jotac
Jotac
82 Posts
ACCEPTED ANSWER

Pinned topic cloning databases

‏2013-01-10T10:40:11Z |
Hi all,

DB2 9.7 on Linux

I have to copy the data from the production database to test enviroment (on a a differente server).
Really isn't necessary to copy the whole db but just few user schema.
The schema name on test enviroment have different names.

I cant use backup/restore because after the restore on test enviroment I can't re-name the schema name to be compliant to test nomenclature (correct? there is no ALTER SCHEMA NAME...?!).
I cant use db2move because there are a lot of table with "identity column generated always" and documentation say I can't use DB2MOVE.

So it's seem to me that I can't use a general tool/command to clone the data but I have to "manually" copy every table.

Wich approach migth be easy and simply (less command... less error) to move/clone a lot of table/data on such scenario?!

thank you

ciao
GIovanni
Updated on 2013-01-14T10:56:52Z at 2013-01-14T10:56:52Z by Jotac
  • SystemAdmin
    SystemAdmin
    17917 Posts
    ACCEPTED ANSWER

    Re: cloning databases

    ‏2013-01-10T11:06:22Z  in response to Jotac
    May be you can try data studio copy schema feature described here http://www.ibm.com/developerworks/data/library/techarticle/dm-0902chen/

    I believe data studio also allows you to rename the schema (right click -> Refactor -> rename).

    Hope it can help you.
    • Jotac
      Jotac
      82 Posts
      ACCEPTED ANSWER

      Re: cloning databases

      ‏2013-01-10T14:17:28Z  in response to SystemAdmin
      I have IBM Data Studio Version 3.1.1.0, full client.

      Interesting your hint. It seem that "scripts" can be generated automatically.

      I do not know why but the "paste" option is not enabled as show in "Figure 35. Selecting Paste... in the Data Source Explorer
      " so I can't try it. On both database (source and target) the connected user is granted to DBADM, I can't understand why the "paste" option is disabled.

      Following the example... seem to me that I should buy "Optim High Performance Unload" isn't?

      ciao
      GIovanni
  • nivanov1
    nivanov1
    231 Posts
    ACCEPTED ANSWER

    Re: cloning databases

    ‏2013-01-10T15:05:58Z  in response to Jotac
    You could use "db2move export" to extract the tables you need, then write a script that would use the table list (db2move.lst) to generate the LOAD commands with the identityoverride option to load data, possibly also renaming schemas at the same time.
    • Jotac
      Jotac
      82 Posts
      ACCEPTED ANSWER

      Re: cloning databases

      ‏2013-01-14T10:56:52Z  in response to nivanov1
      Thank you nivanov1 I will let you know
      ciao
      GIovanni