• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 181
  • Last Modified:

iSeries DB2 query across local and remote site

I'm looking for a SQL example on how to create one SQL select statement to query across two iSeries with instances of DB2.

Presently, the SQL is being ran interactively from the 5250 Emulator.

The final SQL will be ran from a CL program on the iSeries.

I've done this many times in the MS SQL Server world, but I'm not sure what's involved on the IBM platform.

  • 2
2 Solutions
Dave FordSoftware Developer / Database AdministratorCommented:
First, you'll need to know the database directory-entry for your remote system.


Once you know that, you can use 3-part naming in your query:

select someColumns
  from DirectoryEntry.MySchema.MyTable

Open in new window

Gary PattersonVP Technology / Senior Consultant Commented:
Please show an SQL Server example of what you want to do.  

If you have two systems set up with appropriate RDB Entries (WRKRDBDIRE) and authority (ADDSRVAUTE), you can query between the two systems using three-part naming:

(From System1):  select * from system2/mylib/mytable

But on vanilla IBM i DB2, you can't have a SINGE QUERY that references two databases.  

select * from system1/mylib/mytable
select * from system2/mylib/mytable

Open in new window

ERROR: Statement references objects in multiple databases.

There is an optional product called DB2 Multisystem that allows you to create partitioned tables across multiple systems.  Most shops don't have it, though.

CREATE TABLE is an exception to the rule that you can't reference two different systems in the same query.  So this opens the door to running two queries, one over tables on each system, and combine the output into a single file, for example (from System1):

create table qtemp/myTempTable as 
     (select * from system2/mylib/mytable) with data;
insert into qtemp/myTempTable 
   select * from mylib/mytable;

Open in new window

Other options too.  Happy to discuss them if you'll explain a little more what you need to do.
LajuanTaylorAuthor Commented:
Thank You both. This information helps...
The syntax examples work perfectly on our remote partner systems running newer operating systems. Unfortunately, our HQ system is running an OS release that's over 15 years old...
Gary PattersonVP Technology / Senior Consultant Commented:
Lots of ways to run queries (native query and SQL) and move data between systems.  If you'll explain in more detail what you need to produce, I can show you a way.


/* Note, some configuration may be required for DDM.  Try TYPE(*IP) and *SNA  */
CPYF MYLIB/MYDDMF QTEMP/MYTEMPFIL /* and add record selection or whatever you need here */
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now