Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2002
    Posts
    96

    Unanswered: Connect two databases.

    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

  2. #2
    Join Date
    Aug 2002
    Location
    delhi, india
    Posts
    4
    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.

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Connect two databases.

    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


    Originally posted by cgprakash
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •