Copy data from one server to another

[khsaunderson]

Is there any way to copy data from one DB2 server to another using SQL?

Basically, I want to have an insert statement in this vein...

INSERT INTO Server1.Database1.TableA
(Column1,
Column2,
Column3)
SELECT Data1,Data2,Data3
FROM Server2.Database2.TableA

Any ideas?

 

[fredericofonseca]

yes there is. but it depends on your operating system and on your database version on both machines.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[MarcLodge]

khsaunderson,
I've not tried this, and I think you may have to look into what Frederico says above, but I've read that you can create a nickname for a remote table. The command takes the format:
CREATE NICKNAME remotename FOR unixserver.database.table

If you are able to issue such a command, then you would theoretically be able to insert straight into whatever you used as your 'remotename'.

Let us know how you get on.

Marc

 

[khsaunderson]

Thanks for your responses. I've tried the CREATE NICKNAME remotename FOR unixserver.database.table statement and I got an error

SQL0104 - Token NICKNAME was not valid. Valid tokens: VIEW ALIAS TABLE SCHEMA UNIQUE ENCODED.

I've tried googling this and nothing comes up for it, which is strange.

Ta
Kate

 

[khsaunderson]

Oh, and I forgot to say - yes, the OS and version are the same on both systems. It's just a move from test to prod.

Thanks

 

[MarcLodge]

What is the OS and version?

 

[khsaunderson]

V5R3M0...

 

[MarcLodge]

And what platform are you? Mainframe, Unix, Windows etc.

 

[khsaunderson]

I'm going to sound thick now, but how would I know? Sorry, am not sat with the dev team...

I have tried running it in SqlDbx from my PC and then I have also tried it directly on the AS400 SQL screens.

 

[MarcLodge]

My fault I think as I didn't really understand the format of your previous answer.

So, I think that what you are saying is that it is a AS400 DB2 system that you access from an app on your windows PC.

Have you tried accessing it with the apps that you've got? We've got DB2 subsystems here called DB2T and DB2P and from either of those systems I can access data on the other by adding the subsystem to the table qualifier e.g. when in DB2T I issues the select:
SELECT * FROM DB2P.SYSIBM.SYSDUMMY1

Not sure if I can help further as I have no knowledge of AS400 systems.

Marc

 

[khsaunderson]

So, I think that what you are saying is that it is a AS400 DB2 system that you access from an app on your windows PC."

Yep, that's right.

I've tried accessing it through the app, which is basically directly through the AS400 system...

Thanks for trying