CODEWITHSUNDEEP
sqldatabasedb2ibm-midrange
Analytic Lunatic
Analytic Lunatic

Reputation: 3944

Simple way to query across multiple servers?

I have 2 server connections set-up in VS2012, a DEV & PROD. These 2 connections are DB2/AS400. Is there a simple way to query from DEV, then run an INSERT into PROD?

I know I could create a program to query the DEV side, store the results in say a DataGrid or ListView control, then loop through and INSERT into PROD, but I wondered if there was a simple way to perhaps do this via SQL itself?

Upvotes: 1

Views: 338

Answers (2)

user2338816
user2338816

Reputation: 2163

It depends on what you mean by "simple". If you have DB2 Connect Unlimited Edition for System i, it can be "simple". (DB2 Connect for i effectively includes the 'Federation' function.)

And if you want to code to the SQL CLI APIs and use server mode, it can be almost as "simple" as ODBC (assuming you've used it before and have built up a library of procedures; and then it takes some thought and practice to see how it can be made to work for what you want).

You can always combine native I/O with remote SQL. If you have few enough rows, you might also connect to DEV and block-FETCH them all into an array. Then disconnect and connect to PROD, and block-INSERT from the array.

Upvotes: 3

zedfoxus
zedfoxus

Reputation: 37119

There is no simple way to do that with DB2/400 and VS2012. With VS2012, you would have to store data from one server - as you mentioned - in an object and push it to the other server.

If you are running v7r1 tech refresh 4, you may be able to utilize remote database (relational database directory entry) and then execute insert into ... select from to copy data from one system to another. This feature is described here: http://www.itjungle.com/fhg/fhg072512-story02.html and details of tech refresh 4 are here: http://db2fori.blogspot.com/2012/04/db2-wiki-refresher.html.

Although untested, let me suggest - If you have SQL Server installed, you could create 2 linked servers - one to Dev and one to Prod. Then you might be able to execute an insert into ... query from SQL Server referring dev and prod.

Upvotes: 5

Related Questions