7

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.

Community
  • 1
  • 1
Jess
  • 23,901
  • 21
  • 124
  • 145
  • 1
    What if you put the part you can run into a subquery and join to it? – Dan Bracuk Feb 28 '13 at 02:56
  • Nice idea, but I tried using a `where exists` sub-select also with the same error result. – Jess Feb 28 '13 at 03:24
  • I don't think the temp table idea is going to work. The problem is that the session is connection specific. If you want to write code to select into the temp table, then change connection to a different schema, the temp table is no longer available, because you get a new session. – Jess Jul 01 '13 at 19:52

1 Answers1

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
Mayur Manani
  • 795
  • 4
  • 12
  • Did you try the above `select`? I got the same error, `STATEMENT REFERENCE TO REMOTE OBJECT IS INVALID. SQLCODE=-512, SQLSTATE=56023, DRIVER=4.14.113` – Jess Feb 28 '13 at 04:43
  • 3
    Is your server configured to allow federated service? 'Federated' should be enabled for this query to work. – Mayur Manani Feb 28 '13 at 04:50
  • 1
    @MayurManani You didn't mention anything about federated services in your answer. As such, it's not a complete answer. – Zoran Pavlovic Jun 26 '14 at 17:41