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


1 Answers

db2 equivalent of tsql temp table

You have to declare a temp table in DB2 before you can use it:

DECLARE GLOBAL TEMPORARY TABLE SESSION.YOUR_TEMP_TABLE_NAME AS (
    SELECT COLUMN_1, COLUMN_2, COLUMN_3
    FROM TABLE_A
) DEFINITION ONLY

Then populate it:

INSERT INTO SESSION.YOUR_TEMP_TABLE_NAME
SELECT COLUMN_1, COLUMN_2, COLUMN_3
FROM TABLE_A
WHERE COLUMN_1 = 1
  AND COLUMN_2 = 2

It's not quite as straight-forward as in SQL Server. :)

And even though it's called a "global" temporary table, it only exists for the current session. Note that all temp tables should be prefixed with the SESSION schema. If you do not provide a schema name, then SESSION will be implied.

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.




DB2 query selecting from 2 databases in same query in zend

You can federate one database in the other one, via a wrapper. You reference table via nicknames, and then you execute a query normally, as both tables were in the same database (joining, sorting, etc.)

Federation is free between DB2 databases or with Informix (because is from IBM). If you want to federate another data source (Oracle, Excel, flat files), you have to buy that separately.

With federation, you do not need to do the join at application level, but at database level.






Related



Tags