Solved

iSeries DB2 query across local and remote site

Posted on 2016-10-20
4
116 Views
Last Modified: 2016-10-21
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.

Thanks
0
Comment
Question by:LajuanTaylor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 18

Assisted Solution

by:Dave Ford
Dave Ford earned 250 total points
ID: 41853048
First, you'll need to know the database directory-entry for your remote system.

WRKRDBDIRE

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

select someColumns
  from DirectoryEntry.MySchema.MyTable

Open in new window


HTH,
DaveSlash
0
 
LVL 35

Accepted Solution

by:
Gary Patterson earned 250 total points
ID: 41853104
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
union                                              
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.
0
 
LVL 8

Author Closing Comment

by:LajuanTaylor
ID: 41854064
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...
0
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 41854153
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.

From SYSTEM1

/* Note, some configuration may be required for DDM.  Try TYPE(*IP) and *SNA  */
CRTDDMF FILE(MYLIB/MYDDMF) RMTFIL(RMTLIB/RMTFILE) RMTLOCNAME(SYSTEM2) TYPE(*IP)
CPYF MYLIB/MYDDMF QTEMP/MYTEMPFIL /* and add record selection or whatever you need here */
0

Featured Post

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question