Re: RFE: Add cross system exception joins.

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.