Can I join data from 2 different DB2 databases? (Like SQL Server linked databases)

Question

I'm enhancing an existing java application. There is data in 2 different DB2 databases. The app already gets data from 2 different databases, but it always does a lookup from one and then the other. Is there a way to join data from 2 different DB2 databases using one SQL SELECT?

This is what I tried:

CREATE ALIAS remote_orders FOR remote_db.schema.orders;

select *
from myid.remote_orders a
inner join local_schema.parts b on (a.key = b.key)
with ur FETCH FIRST 200 ROWS ONLY

I get this error:

STATEMENT REFERENCE TO REMOTE OBJECT IS INVALID. SQLCODE=-512, SQLSTATE=56023, DRIVER=4.14.113

Can I do something with a temp table? I can run this select with no errors, but it does not help me... (yet)

select *
from myid.remote_orders
with ur FETCH FIRST 200 ROWS ONLY

EDIT:

A DB2 Temp Table might help. I was able to create one. Now I need to (go to bed) and try selecting into it and THEN doing my join.

Answer 1

Use fully qualified name <database>.<user/schema>.<tablename>

something like:

select *
from DB1.myid.remote_orders a
inner join DB2.local_schema.parts b on (a.key = b.key)
with ur FETCH FIRST 200 ROWS ONLY