Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2011
    Posts
    3

    Unanswered: COPY all objects from one schema to another schema in same DB

    Hi All,
    I am trying to accomplish following:
    ================================================== =========
    I want to copy all objects from one schema to another schema in same DB.
    ================================================== =========

    I have two questions.

    #1
    ================================================== ==
    I have succeffully ran following command:

    db2 "CALL SYSPROC.ADMIN_COPY_SCHEMA('SOURCE1', 'TARGET1', 'COPY', null, 'TS1', 'TS1 , SYS_ANY', 'ERRORSCHEMA', 'ERRORTBL')"

    It works perfectly fine and Creates a new TARGET1 schema and copies all objects.

    However, I need to copy all objects from SOURCE1 to TARGET2 Schema and there are already some tables in TARGET2. So I can not delete TARGET2 schema.

    So it gives an error that '*** Error while creating Schema TARGET2 ***".

    So what do I need??
    ================================================== =====


    #2
    ================================================== ==

    If I want to use db2move export command, can I use it?? If yes then how??

    While doing the db2move export, can I specify that owner of this backup should be some other user.

    I have successfully copied the data in SAME Schema from One Database to another Database USING SAME SCHEMA.

    1) Export Data using following command:
    db2move SOURCE_DB export -sn SOURCE1

    2) Create DDL using following command:
    db2look -d SOURCE_DB -z SOURCE1 -e -x -nofed -o SOURCE1.sql

    3) Connect to SOURCE_DB2 Database and create tables:
    Run the SOURCE1.SQL file containing DDL and create tables of SCHEMA in SOURCE2_DB.

    4) Copy all ixf files to SOURCE_DB2 Server and issue following command:
    db2move SOURCE_DB2 load -lo replace -l .

    These steps work perfectly if I use same schema name in SOURCE1_DB and SOURCE2_DB.

    What do I need if would want to change the schema name in SOURCE2_DB.

    In Step #3, I can use a different Schema Name and create tables under different shcema.

    While loading (During step #4) I get the error that tables SOURCE2.* do not exist.

    So what do I need to change in this.

    I would really appreciate any feedback or solutions.

    Thanks in Advance.

  2. #2
    Join Date
    Oct 2011
    Posts
    3

    Any updates???

    Any updates???


  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    What if you change the schema name in db2move.lst (created during db2move export) prior to step #4

  4. #4
    Join Date
    Oct 2011
    Posts
    3
    That worked like a charm.



    Since I am new so I did not know about db2move.lst file.

    I also found that if you need to have dbadm/sysadm authority to complete these steps.

    Finally I accomplished what I wanted. Thanks db2girl.

Posting Permissions

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