asked on Does db2 move utility work for different environments if they are on different servers
Hi All
Does db2 move utility work for different environments if they are on different servers (with different IPs)?
db2move sample COPY -sn BAR -co target_db target schema_map
"((BAR,FOO))" -u userid -p password
I need to do copy a schema.
need DB cataloging?
Connecting to database SOURCEDB... successful! Server : DB2 Common Server V10.5.7
**ERROR - Failed to connect to target database : TARGETDB
db2move failed with -1 (debuginfo:19)
can you pls help
Does db2 move utility work for different environments if they are on different servers (with different IPs)?
db2move sample COPY -sn BAR -co target_db target schema_map
"((BAR,FOO))" -u userid -p password
I need to do copy a schema.
need DB cataloging?
Connecting to database SOURCEDB... successful! Server : DB2 Common Server V10.5.7
**ERROR - Failed to connect to target database : TARGETDB
db2move failed with -1 (debuginfo:19)
can you pls help
Databases* dbaDB2
Last Comment
Prardhan N
ASKER
I just need to copy the schema with data , not the DB.
but between different DBs which are in different machines.
but between different DBs which are in different machines.
BACKUP DATABASE myDB TO path/filename
The BACKUP DATABASE statement will back up any schema/database on the server. In this case the terms "schema" and "database" are nearly synonymous. That can be confusing as the terms "Database" and "instance" are often interchanged, too. Making matters worse is that all vendors don't subscribe to the same definitions. :(
The BACKUP DATABASE statement will back up any schema/database on the server. In this case the terms "schema" and "database" are nearly synonymous. That can be confusing as the terms "Database" and "instance" are often interchanged, too. Making matters worse is that all vendors don't subscribe to the same definitions. :(
ASKER
For some reason, i want to use db2move utility, any inputs on this.
Unless the functionality was recently added (and I see no evidence that it was) db2move doesn't support it. The utility uses direct I/O (skipping some of the DBMS overhead) to accomplish things at a lower level than the database.
Hi!
To move a schema with tables and data using db2move then you could use it like this (always works for me :) )
Export
Or similar to your command.
Note that this command requires : The TARGET_DB cannot be the same as the source database and must be a local database.
So if the the target database is a remote database to the source then use the export/import method copying the exported data between hosts.
https://www.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0002079.html
Regards,
Tomas Helgi
To move a schema with tables and data using db2move then you could use it like this (always works for me :) )
Export
db2move DBSOURCE export -aw -l lobs -sn schema1
db2move DBTARGET import -io replace_create -l lobs
Or similar to your command.
db2move dbsrc COPY -sn schema1 -co TARGET_DB dbtgt USER myuser1 USING mypass1
SCHEMA_MAP ((schema1,newschema1)) TABLESPACE_MAP ((ts1,ts2), SYS_ANY))
Note that this command requires : The TARGET_DB cannot be the same as the source database and must be a local database.
So if the the target database is a remote database to the source then use the export/import method copying the exported data between hosts.
https://www.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0002079.html
Regards,
Tomas Helgi
ASKER
Yes, target Db is on Remote Host. Does Db2move does not work for remote machine directly ?
Shall i use db2move export
then ftp the files
then shall i Import or load the data ?
is that you are suggesting?
Shall i use db2move export
then ftp the files
then shall i Import or load the data ?
is that you are suggesting?
Hi!
Yes, use the export/import method in your case.
Regards,
Tomas Helgi
Yes, use the export/import method in your case.
Regards,
Tomas Helgi
ASKER
have many tables in the schema, do i need to export and ftp hundred of files and load or import it.
Any simple way?
Any simple way?
ASKER
Hi ,
do we have any Db2 utility does the operation(Schema copy with DDL and Data) over network for a remote target DB?
do we have any Db2 utility does the operation(Schema copy with DDL and Data) over network for a remote target DB?
Hi!
No, I'm afraid not.
The export command I gave you exports all tables in the provided schema1 in one go.
And the import of the entire schema1 is also done in one single command.
All you need to do is to be in a directory ( on a disk) big enough to hold the exported data/files.
If you are on *nix then you can use scp commands to copy the entire directory to the remote host in one go.
So actually, it's only 3 commands you need to execute.
Regards,
Tomas Helgi
No, I'm afraid not.
The export command I gave you exports all tables in the provided schema1 in one go.
And the import of the entire schema1 is also done in one single command.
All you need to do is to be in a directory ( on a disk) big enough to hold the exported data/files.
If you are on *nix then you can use scp commands to copy the entire directory to the remote host in one go.
So actually, it's only 3 commands you need to execute.
Regards,
Tomas Helgi
ASKER
Hi
what is the use of "lobs" paramter? Is it to include lob data type data ?
I have lob data as well.
what is the use of "lobs" paramter? Is it to include lob data type data ?
I have lob data as well.
ASKER
if i want a new schema name in my remote DB
How to do that ?
I think 3rd command will only work for local Data Base.
How to do that ?
I think 3rd command will only work for local Data Base.
Hi!
You can follow this in the guide to do the schema change inside the same database.
Basically open a connetion to the database as the instance owner or SYSDBA user
and issue the ADMIN_COPY_SCHEMA command
with the required schemanames
Example (see the above link for more info especially the copymode - COPY/COPYNO option)
Regards,
Tomas Helgi
You can follow this in the guide to do the schema change inside the same database.
Basically open a connetion to the database as the instance owner or SYSDBA user
and issue the ADMIN_COPY_SCHEMA command
with the required schemanames
Example (see the above link for more info especially the copymode - COPY/COPYNO option)
CALL SYSPROC.ADMIN_COPY_SCHEMA('SOURCE_SCHEMA', 'TARGET_SCHEMA',
'COPY', NULL, 'SOURCETS1 , SOURCETS2', 'TARGETTS1, TARGETTS2,
SYS_ANY', 'ERRORSCHEMA', 'ERRORNAME') ;
Regards,
Tomas Helgi
ASKER
Thanks for info. As per your inputs and my reading schema name change is not possible directly.
The only way is to create a new schema(with required name) through ADMIN_COPY_SCHEMA utility and drop the old one.
The only way is to create a new schema(with required name) through ADMIN_COPY_SCHEMA utility and drop the old one.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks for your valuable inputs.
Appreciating your knowledge sharing.
Appreciating your knowledge sharing.
The DB2 move utility can't be used to move a database to another server.
The traditional way to accomplish that move is with a full backup on the source server and a restore on the target server.
Kent