Can I combine data from two different DB2 databases? (Similar to SQL Server related databases)

Question

I am improving an existing Java application. There is data in two different DB2 databases. An application already retrieves data from two different databases, but it always searches from one and then the other. Is there a way to combine data from two different DB2 databases using the same 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 

Is there anything you can do with the temp table? I can run this selection without errors, but it doesn’t help me ... (for now)

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

EDIT:

A DB2 Temp table may help. I was able to create it. Now I need to (go to bed) and try to select it, and ONLY do my joining.

Answer 1

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

sort of:

 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