cross database query

[martynh]

Is it possible to join tables in more than one database, within the same instance, in a query (DB2 8.1, on Windows 2000)? e.g.

select * from db1.u1.tab1 inner join db4.u2.tab6 on ...

I'm sure it worked on DB2 on a mainframe (when I used to work on proper machines!)

 

[sathyarams]

This is possible ...

This is called Federated System in the DB2 LUW world ... (Don't know what it was called on the mainframe)

A brief of the steps :

1) Ensure that the data joiner package is installed ( This is on Unix .. not sure how to do it on W2K)

2) Set the dbm cfg parameter FEDERATED=YES and restart the instance

3) Create Wrapper (This step identifies the library to use to connect to a remote database). In your case the library will be drda.dll

4) Create Server. This step identifies the node and database to use.

5) Create a Nickname to a remote table ...

You may also need to create a user mapping (can't remember)

In the DB2's information centre read 'Federated Systems' under the concepts heading ...

HTH



More DB2 questions answered at

http://www.dbforums.com/f8