sqlibm-midrangedb2-400

How to join 2 tables from 2 different servers in AS400?

I am working with AS400 version 7.1.

Having the following:

ServerA (SA) - DatabaseA (DBA) - TableA (TA)

ServerB (SB) - DataBaseB (DBB) - TableB (TB)

SELECT A.*, B.* 
FROM SA.DBA.TA A INNER JOIN SB.DBB.TB
ON A.PN=B.PN
WHERE A.PN='BFDKS';

What's the correct syntax to join 2 tables from two different servers in AS400?

I am getting the following error

Relational database SA not in relational database directory

Solution

  • I'm pretty sure this is not currently possible with Db2 for i...

    3-part names are new to the i, and as far as I know are limited to

    insert into mylib.mytable (select * from remotedb.somelib.sometable);

    see CREATE TABLE with remote subselect

    Or in a trigger program.. see 3-part names in triggers

    Db2 for LUW has such federation capabilities...

    One work around I've seen is the use of a user defined table function (UDTF) to return rows from a remote Db2 for i database..