CODEWITHSUNDEEP
sqlibm-midrangedb2-400
ehh
ehh

Reputation: 3480

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

Upvotes: 1

Views: 1497

Answers (3)

Ed Blahut
Ed Blahut

Reputation: 1

You can create a table function that hits server B and returns rows to server A in a single SQL statement. There are multiple flavors of this; with or without parms(keys).

CREATE OR REPLACE FUNCTION                    
remote_inventory()                     
RETURNS TABLE (                               
UPC CHAR(64),                          
QUANTITY INT)                             
LANGUAGE SQL                                  
no external action                            
reads sql data                                
fenced                                        
cardinality 1                                 
BEGIN                                         
return   select UPC,quantity       
   from systemB.locallib.inventory;            
end               


Select a.upc,a.quantity,b.quantity
 from locallib.inventory a, table(remote_inventory()) b
  where a.upc = b.upc

This is the very poor man's attempt (with success) at a Federated Database.

NO Federated DB License or product required - just table functions and your imagination!

You can actually have three servers A,B,C and at ServerB you can access, via two table functions, ServerA data and ServerC data without any reference to data on ServerB.

Cool Stuff!!!

Upvotes: 0

Esperento57
Esperento57

Reputation: 17492

Not possible on DB2 for i. But like says Charles, you can do it :

-- run this instructions on server B

create table qtemp.SADBATA as (
SELECT A.* FROM SA.DBA.TA A
where A.PN='BFDKS'
) with data;

SELECT * FROM qtemp.SADBATA A INNER JOIN SB.DBB.TB ON A.PN=B.PN;

Upvotes: 1

Charles
Charles

Reputation: 23823

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..

Upvotes: 2

Related Questions