var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: COPY all objects from one schema to another schema in same DB
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.
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??
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.
What if you change the schema name in db2move.lst (created during db2move export) prior to step #4
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.