How do I join two tables on two different instances of DB2?

Question

There are two instances of DB2 running on the same machine. One has a table aliased to the a table in the other one. Is there a way to do this in DB2 or does it have to be done via an external tool?

This is with an older version of DB2

Answer 1

The "federation" feature in DB2 will allow you to do what you need.

It's available on the Linux/Unix/Windows version of DB2 - not sure about the mainframe - and there are no licensing issues, it's part of the base product set.

To implement it you only need to create a few extra db objects (wrapper, server mappings, nickname) and you're more or less done. Follow the documentation and you should be fine.

Cheers.

Answer 2

you should be able to use the 3 part table name to access the data.

prior to version 9 is was simple, subsystem.qualifier.tablename would get you there, at least on the mainframe. starting with version 9 you have to perform a bind on each subsystem.

Answer 3

I would recommend using the DB2 federation feature. Then you would be able to setup a nickname and then use it just like any other table.

Some statements to setup federation I would look at are:

CREATE WRAPPER    
CREATE SERVER
CREATE NICKNAME

Tutorial to Federate Quickly

Publib for all general information about DB2