Re: SQL: Joining on three part names.

Actually with DRDA Java client already available...
Opensource ArdGate part of
http://sourceforge.net/projects/appserver4rpg/?source=navbar

And there's is (was) a commercial option also, though I can't find it.

We wouldn't even need a MS SQL Server specific DRDA provider...

I agree that the next step should be support of 3 part names in joins, but
I'm afraid the the DB2 for i folks will be overruled by the DB2 for LUW
guys that support the Federation functionality.

Charles

On Mon, Feb 16, 2015 at 10:29 AM, Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
wrote:

Rob, I suspect there is no way around this yet - we can have only 1 active
connection at a time, and a JOIN like this would need more than 1.

It'd be nice if IBM would add this capability - especially now that we CAN
specify the remote database name in our 3-part naming.

I've no information suggesting they ARE going to do that - and could not
say so, anyhow!! It does seem an obvious next step. And if someone were to
write a DRDA provider for things like SQL Server, hey, we could do this all
directly to other than IBM i platforms.

Vern


On 2/16/2015 8:49 AM, rob@xxxxxxxxx wrote:

One of the newer functions out is three part naming, for example
select col1, col2, col3
from myLparName.MySchema.MyTable

So this statement is correct:
SELECT a.cmPNY, a.CMPNAM
FROM gdisys.erplxf.rco a

And if I have WRKRDBDIRE all set up I can change it to point to another
lpar like this:
SELECT a.cmPNY, a.CMPNAM
FROM gdi.erplxf.rco a

However, I cannot do this:
SELECT a.cmPNY, a.CMPNAM, b.cmpnam
FROM gdisys.erplxf.rco a
join gdi.erplxf.rco b using (cmpny)

Nor this:
SELECT a.cmPNY, a.CMPNAM, b.cmpnam
FROM gdisys.erplxf.rco a, gdi.erplxf.rco b
where a.cmpny=b.cmpny

These last two abort with:
SQL0512
Message . . . . : Statement references objects in multiple databases.
Cause . . . . . : The statement refers to objects that reside on
multiple
databases. The objects could be either explicitly qualified object
names or
alias names that are defined to reference a different database. This
SQL
statement can only refer to a single database.
A CREATE TABLE AS that selects from a remote database cannot
reference
a
table that has a FIELDPROC defined. It cannot define a materialized
query
table. If the remote database is not on an IBM i server, the INCLUDING

clauses are not allowed.
Recovery . . . : Ensure all objects used in the statement reside at the
same database. Try the request again.

I can do this:
Create table rob.localcopy as (
SELECT a.cmPNY, a.CMPNAM
FROM gdi.erplxf.rco a)
with data

But we're really hoping to get away from copying data all around.

Is there some magic wand I can wave around to make this work?


Rob Berendt

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.