Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

iSeries DB2 query across local and remote site

Posted on 2016-10-20
4
Medium Priority
?
155 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
  • 2
4 Comments
 
LVL 18

Assisted Solution

by:Dave Ford
Dave Ford earned 1000 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 1000 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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 (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…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Loops Section Overview
Suggested Courses

885 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