Solved

iSeries DB2 query across local and remote site

Posted on 2016-10-20
4
38 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:daveslash
daveslash 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 34

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 34

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

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…
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 video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now