DB2 : V7.2
OS : AIX 4.3.3

I need to write a sql statement which will connect two database's tables. Both databases are in the same instance. How can we acheive this? Your help plse. Thank you.

Regards
Prakash

Hi,

You have to use Federated Databases to achieve this.
I have written a simple DDL for that, It might be of some use to you:

First you have to create wraper:
-- ---------------------------
-- DDL Statements for WRAPPER
-- ---------------------------
CREATE WRAPPER "DRDA"
LIBRARY 'libdb2drda.so'
OPTIONS (DB2_FENCED 'N'
);

Now create server:
-- ---------------------------
-- DDL Statements for SERVER
-- ---------------------------
CREATE SERVER "BEERSERVER"
TYPE DB2/LINUX
VERSION 8.1
WRAPPER DRDA
AUTHORIZATION ""
PASSWORD ""
OPTIONS
(DBNAME 'BEERDS'
);

where BEERDS is the database where the other table reside.
Now you have to create user mapping between the 2 users. Since you have DB in same instance then svtdbm1 and svtdbm4 will be same "instance owner user id "
-- --------------------------------
-- DDL Statements for USER MAPPING
-- --------------------------------

CREATE USER MAPPING FOR SVTDBM1
SERVER "BEERSERVER"
OPTIONS
(REMOTE_AUTHID 'svtdbm4'
,REMOTE_PASSWORD ''
);

Now create nick name for the table . Here BEER.TABLEDS is the table which reside in BEERDS DB and you have to refer this table by BEER.TABLE1 .

-- --------------------------------
-- DDL Statements for NICKNAME
-- --------------------------------

CREATE NICKNAME "BEER "."TABLE1"
FOR "BEERSERVER"."BEER"."TABLEDS";

HTH.

Are you asking about connecting to two databases in the same sesssion ..
Then you can use

db2 "set client connect 2"
db2 connect to datab1
db2 connect to datab2 (connects to datab2 but retains connection to datab1)
db2 set connection datab1 (switches the connection to datab1)

HTH

Sathyaram