On 12-Sep-2016 08:09 -0600, Rob Berendt wrote:
I added an RFE to allow cross system exception joins.
Please vote.
[http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=94447]
Somebody might get the false impression that cross-system
non-exception joins are allowed ;-) Why not just ask for *all* join
[and UNION, INTERSECT, and EXCEPT] query capability across systems?
FWiW: I seem to recall suggesting that a User Defined Table Function
(UDTF) could be created to assist with solving such relatively
simplistic scenarios. Even with the additional objects that would be
created per remote system for implementation, and the poor performance
of the implementation, the value of the making such a simple reference
might be worthwhile; see:
Subject: SQL: Joining on three part names.
[
http://archive.midrange.com/midrange-l/201502/msg00481.html]
So for example:
Setup of permanent objects:
create function PTF_INFO_at_GDIxx ()
returns table
( PTF_PRODUCT_ID varchar( 07)
, PTF_IDENTIFIER varchar( 07)
) language sql
return
select
PTF_PRODUCT_ID
, PTF_IDENTIFIER
from GDIxx.QSYS2.PTF_INFO
;
create view ptf_info_at_gdixx as
select p.*
from table ( ptf_info_at_gdixx() ) as p
;
Querying:
select
home.PTF_PRODUCT_ID
, home.PTF_IDENTIFIER
, away.PTF_PRODUCT_ID
, away.PTF_IDENTIFIER
from qsys2.PTF_INFO home
exception join
PTF_INFO_at_GDIxx away
on (home.PTF_PRODUCT_ID=away.PTF_PRODUCT_ID)
and (home.PTF_IDENTIFIER=away.PTF_IDENTIFIER)
ORDER BY
home.PTF_PRODUCT_ID
, away.PTF_PRODUCT_ID
, home.PTF_IDENTIFIER
, away.PTF_IDENTIFIER
;
A likely better [and better performing] approach would be to
encapsulate the work shown used to circumvent the limitation, wherein
there is a deletion and then generation of the [temporary table and]
data from the target system; done in CALL to a Stored Procedure that
uses a dynamically created statement with the RDB name [because CONNECT
TO VariableName is not supported AFaIK, and definitely no ability to use
VariableName.SchemaName.TableName for three-part-naming]. The CREATE
PROCEDURE for that SP could be defined to return the result set directly
[if the intention is running from Run SQL Scripts for which the results
would easily display] or effect nothing more than the data from the
other LPAR being stored locally again which a previously composed query
can be repeatedly requested after each new CALL to reset the local copy
of data retrieved from the remote partition. An even better option
might be a CLP as stored procedure that uses TCP/IP DDM, and the
permanent objects created are [one or more] DDMF to get a local copy of
the data from the remote system.
As an Amazon Associate we earn from qualifying purchases.