Accessing Files from Multiple RDBs in SQL

[jsplice]

Hello all. I'm trying to run a query that pulls data from two files. One of the files is going to be on the local system in which the program is running, and the second file will be on a remote system. The query looks like this:

          SELECT A.CUSTNUM
           FROM LocalFile A, RemoteFile B
           WHERE (B.ITETYP = 'A' OR B.ITETYP = 'I') AND
           (A.CUSTNUM >= B.ITSHPF AND A.CUSTNUM <= B.ITSHPT);

I know that if I do a "connect to", it will connect to the remote DB, but that means it will try to get the local file from the remote system also. Is there a way around this?

Thanks

 

[Mercury2]

You can't do it in one request.
First connect to remote DB, then run your SELECT to remote DB and output the result set to a new table on local machine, i.e.
CREATE TABLE MYLIB/MYTABLE AS (SELECT * FROM ....) WITH DATA.
Disconnect and do your SQL stm with MYLIB/MYTABLE and local table.

Alternatively you may use the not free DB2 Information Integrator product from IBM. With it you can actually reference tables and join together from different database all on the same SQL statement. If interested, the RedBook is Downloadable at Heterogeneous data access for iSeries applications

FYI DDM is not supported by SQL.