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

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

    Re: cloning databases

    ‏2013-01-10T11:06:22Z  
    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

    Re: cloning databases

    ‏2013-01-10T14:17:28Z  
    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.
    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

    Re: cloning databases

    ‏2013-01-10T15:05:58Z  
    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

    Re: cloning databases

    ‏2013-01-14T10:56:52Z  
    • nivanov1
    • ‏2013-01-10T15:05:58Z
    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.
    Thank you nivanov1 I will let you know
    ciao
    GIovanni