Does db2 move utility work for different environments if they are on different servers

Prardhan N
Prardhan N used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kent OlsenData Warehouse / Database Architect

Commented:
Hi Sridhar,

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

Author

Commented:
I just need to copy the schema with data , not the DB.

but between different DBs which are in different machines.
Kent OlsenData Warehouse / Database Architect

Commented:
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.  :(
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Author

Commented:
For some reason, i want to use db2move utility, any inputs on this.
Kent OlsenData Warehouse / Database Architect

Commented:
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.
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
Hi!

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

Open in new window

Import
db2move DBTARGET import -io replace_create -l lobs

Open in new window


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))

Open in new window


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

Author

Commented:
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?
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
Hi!

Yes, use the export/import method in your case.

Regards,
    Tomas Helgi

Author

Commented:
have many tables in the schema, do i need to export and ftp hundred of  files and load or import it.

Any simple way?

Author

Commented:
Hi ,
do we have any Db2 utility does the operation(Schema copy with DDL and Data) over network for a remote target DB?
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
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

Author

Commented:
Hi

what is the use of  "lobs" paramter? Is it to include lob data type data ?

I have lob data as well.

Author

Commented:
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.
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
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)
CALL SYSPROC.ADMIN_COPY_SCHEMA('SOURCE_SCHEMA', 'TARGET_SCHEMA', 
   'COPY', NULL, 'SOURCETS1 , SOURCETS2', 'TARGETTS1, TARGETTS2, 
   SYS_ANY', 'ERRORSCHEMA', 'ERRORNAME') ;

Open in new window


Regards,
     Tomas Helgi

Author

Commented:
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.
Database Administrator / Software Engineer
Commented:
Hi

And to drop the entire schema (after copying it  and the data to the new one) use the ADMIN_DROP_SCHEMA utility. :)

Regards,
    Tomas Helgi

Author

Commented:
Thanks for your valuable inputs.

Appreciating your knowledge sharing.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial