Solved

iSeries DB2 query across local and remote site

Posted on 2016-10-20
4
103 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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Coding for returned data using DCL-PR 3 124
DB2 error. 37 101
Certificat to iSeries KeyStore 1 30
Shell Script on AIX 7 177
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 (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Suggested Courses

738 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