This discussion is archived
1 2 3 Previous Next 44 Replies Latest reply: Aug 8, 2012 10:51 AM by Dharma_ RSS

Create DB-Link for Oracle Database 11g to IBM DB2

Dharma_ Newbie
Currently Being Moderated
Dear all,

Please do needful help for me

thanks advance

Regards
Lingam
  • 1. Re: Create DB-Link for Oracle Database 11g to IBM DB2
    mkirtley-Oracle Expert
    Currently Being Moderated
    Lingam,
    To create a database link between Oracle and DB2 you need to install and use a Database Gateway.
    For Db2 there are 2 alternatives -

    - Database Gateway for DRDA (DG4DRDA)
    - Database Gateway for ODBC (DG4ODBC)

    The following note available in My Oracle Support has links to further details, information, documentation and setup notes -

    Master Note for Oracle Gateway Products (Doc ID 1083703.1)

    but also look at these notes for the differences in the gateways -

    Note.233876.1 Options for Connecting to Foreign Data Stores and Non-Oracle Databases (Doc ID 233876.1)
    Note.252364.1 Functional Differences Between DG4ODBC and Specific Database Gateways (Doc ID 252364.1)
    Note.232482.1 Gateway and Generic Connectivity Licensing Considerations (Doc ID 232482.1)

    Please have a look at this information and get back with any further questions.

    Let us know on which platforms Oracle and DB2 are installed and we can be more specific about which notes you need for setup help.

    Regards,
    Mike
  • 2. Re: Create DB-Link for Oracle Database 11g to IBM DB2
    Dharma_ Newbie
    Currently Being Moderated
    Hai Mike,

    Thanks,

    I will check and let you know...

    Lingam
  • 3. Re: Create DB-Link for Oracle Database 11g to IBM DB2
    Dharma_ Newbie
    Currently Being Moderated
    Dear Mike,

    Oracle Database Version = 11.2.0.7
    Operationg Version = OEL 5.8 (x64)

    DB2 Operating Version = Windows 2003 Server

    Regards

    Lingam
  • 4. Re: Create DB-Link for Oracle Database 11g to IBM DB2
    kgronau Guru
    Currently Being Moderated
    Lingam,
    you can set up 2 different gateways, DG4ODBC which is a generic gateway using 3rd party ODBC drivers or the dedicated DB 2 gateway which is called Database gateway for DRDA and which requires an additional license. Both gateways are available for Windows and Linux.

    As Mike stated already,
    please have a look at My Oracle Support notes (http://support.oracle.com):
    Note.233876.1 Options for Connecting to Foreign Data Stores and Non-Oracle Databases (Doc ID 233876.1)
    Note.252364.1 Functional Differences Between DG4ODBC and Specific Database Gateways (Doc ID 252364.1)
    Note.232482.1 Gateway and Generic Connectivity Licensing Considerations (Doc ID 232482.1)
    To get more information.

    - Klaus
  • 5. Re: Create DB-Link for Oracle Database 11g to IBM DB2
    Dharma_ Newbie
    Currently Being Moderated
    Hai Maike & Kalus,

    How to configure DSN in Linux(x64) for Oracle Database 11g, connected to DB2 in Windows 2003 Server?

    Regards
    Lingam
  • 6. Re: Create DB-Link for Oracle Database 11g to IBM DB2
    kgronau Guru
    Currently Being Moderated
    When you want to configure DG4ODBC on 64bit Linux you first need a 64bit ODBC driver which is able to connect to your DB2 UDB database. Do you already have an ODBC driver which is able to connect? If not, please check with IBM if they provide a 64bit ODBC driver for Linux which is able to connect to the DB3 database on Windows.

    Once you have a working ODBC connection, then check out the My Oracle Support Note:
    How to Configure DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX Itanium) to Connect to Non-Oracle Databases Post Install          [Document 561033.1]
  • 7. Re: Create DB-Link for Oracle Database 11g to IBM DB2
    Dharma_ Newbie
    Currently Being Moderated
    Hai kalus,

    Where we get DB2 driver for linux(x64)?

    Regards
    Lingam
  • 8. Re: Create DB-Link for Oracle Database 11g to IBM DB2
    kgronau Guru
    Currently Being Moderated
    IBM is providing them, please have a look at: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.apdv.cli.doc%2Fdoc%2Ft0023867.htm

    You can also purchase a commercial ODBC driver from 3rd party vendors like DataDirect, Easysoft or OpenLink.
  • 9. Re: Create DB-Link for Oracle Database 11g to IBM DB2
    Dharma_ Newbie
    Currently Being Moderated
    Dear mike,

    I have configure based on above document,i have test the connection,but i have error below mentioned

    [oradev@testebs bin]$ isql dblink4db2 -v
    [S1009][unixODBC][IBM][CLI Driver] CLI0124E Invalid argument value. SQLSTATE=S1009
    [ISQL]ERROR: Could not SQLConnect

    Regards
    Dharma Lingam
  • 10. Re: Create DB-Link for Oracle Database 11g to IBM DB2
    kgronau Guru
    Currently Being Moderated
    You're currently having an issue with the ODBC driver connecting to the DB2 database.
    You can post the odbc.ini file so that Mike or I can have a look at it, but it would be also good to get in touch with the ODBC vendor (IBM?) and ask the vendor how to resolve the issue.

    - Klaus
  • 11. Re: Create DB-Link for Oracle Database 11g to IBM DB2
    Dharma_ Newbie
    Currently Being Moderated
    Dear kalus,


    ****/etc/odbc.ini****
    [ODBC Data Sources]
    db2 = connection to db2

    [dblink4db2]
    Description = DB2 for ODBC Driver
    Driver = /opt/IBMDB2_Driver/clidriver/lib/libdb2.so
    Server = 192.168.2.245

    Port = 5000
    UserID = db2admin
    Password = nilgiris
    naming = 0
    DefaultLibraries = QGPL
    Database = NILGSERV
    ConnectionType = 0
    CommitMode = 2

    ExtendedDynamic = 1
    DefaultPkgLibrary = QGPL
    DefaultPackage = A/DEFAULT(IBM),2,0,1,0,512
    AllowDataCompression = 1
    MaxFieldLength = 32
    BlockFetch = 1

    BlockSizeKB = 128
    ExtendedColInfo = 0


    ***/etc/odbcinst.ini***

    [PostgreSQL]
    Description = ODBC for PostgreSQL
    Driver = /usr/lib/psqlodbc.so

    Setup = /usr/lib/libodbcpsqlS.so
    Driver64 = /usr/lib64/psqlodbc.so
    Setup64 = /usr/lib64/libodbcpsqlS.so
    FileUsage = 1

    [MySQL]
    Description = ODBC for MySQL

    Driver = /usr/lib/libmyodbc3_r.so
    Setup = /usr/lib/libodbcmyS.so
    Driver64 = /usr/lib64/libmyodbc3_r.so
    Setup64 = /usr/lib64/libodbcmyS.so
    FileUsage = 1


    [PostgreSQL64]
    Description = ODBC for PostgreSQL (64 bit)
    Driver = /usr/lib/psqlodbcw.so
    Setup = /usr/lib/libodbcpsqlS64.so
    Driver64 = /usr/lib64/psqlodbcw.so

    Setup64 = /usr/lib64/libodbcpsqlS64.so
    FileUsage = 1
    [MySQL64]
    Description = ODBC for MySQL (64 bit)
    Driver = /usr/lib/libmyodbc5.so
    Setup = /usr/lib/libodbcmyS64.so

    Driver64 = /usr/lib64/libmyodbc5.so
    Setup64 = /usr/lib64/libodbcmyS64.so
    FileUsage = 1

    [iSeries Access ODBC Driver]
    Description = iSeries Access for Linux ODBC Driver

    Driver = /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so
    Driver64 = /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so
    Setup = /opt/ibm/iSeriesAccess/lib64/libcwbodbcs.so
    Setup64 = /opt/ibm/iSeriesAccess/lib64/libcwbodbcs.so

    UsageCount = 1
    CPTimeout =
    CPReuse =
    NOTE1 = If using unixODBC 2.2.11 or later and you want the 32 and 64-bit ODBC drivers to share DSN's,
    NOTE2 = the following Driver64/Setup64 keywords will provide that support.

    Threading = 2
    DontDLClose = 1

    [iSeries Access ODBC Driver 64-bit]
    Description = iSeries Access for Linux 64-bit ODBC Driver
    Driver = /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so

    Driver64 =
    Setup = /opt/ibm/iSeriesAccess/lib64/libcwbodbcs.so
    Setup64 =
    UsageCount = 1
    CPTimeout =
    CPReuse =
    Threading = 2

    DontDLClose = 1

    [DB2 UDB ODBC Driver]
    Description = DB2 Linux ODBC Driver (64 bit)
    Driver = /opt/IBMDB2_Driver/clidriver/lib/libdb2.so
    Driver64 = /opt/IBMDB2_Driver/clidriver/lib/libdb2.so

    UsageCount = 1
    CPTimeout =
    CPReuse =
    DontDLClose = 1
    Database = NILGDB2


    [PervasiveSQL]
    Description = Pervasive Linux ODBC Driver

    Driver = /usr/lib64/libodbcci.so
    Driver64 =
    Setup =
    Setup64 =
    UsageCount =
    CPTimeout =
    CPReuse =
    Database = billctr

    Regards and Thanks
    Dharma Lingam
  • 12. Re: Create DB-Link for Oracle Database 11g to IBM DB2
    Dharma_ Newbie
    Currently Being Moderated
    Dear kalus,


    ***initNILGDB2.ora***
    HS_FDS_CONNECT_INFO = dblink4db2
    HS_FDS_TRACE_LEVEL=4
    HS_FDS_TRACE_FILE = hstrace.txt
    #HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
    HS_FDS_SHAREABLE_NAME = /Oracle/DBlink_RPM/Software/usr/local/lib/libodbc.so
    set ODBCINI=/etc/odbc.ini



    ***Lisener.ora***

    DEV =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.14)(PORT = 1526))
    )
    )

    SID_LIST_DEV =
    (SID_LIST =

    (SID_DESC =
    (ORACLE_HOME= /VIS/DEV/db/tech_st/11.1.0)
    (SID_NAME = DEV)
    )
    )

    NILGDB2=
    (DESCRIPTION_LIST=
    (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.102)(port=50000)))
    )

    SID_LIST_NILGDB2 =
    (SID_LIST=
    (SID_DESC=
    (SID_NAME=NILGDB2)
    (ORACLE_HOME=/VIS/DEV/db/tech_st/11.2.0)
    (PROGRAM=dg4odbc)
    (ENVS=LD_LIBRARY_PATH=/VIS/DEV/db/tech_st/11.2.0/lib:/Oracle/DBlink_RPM/Software/usr/local/lib/libodbc.so/lib, LIBPATH=/VIS/DEV/db/tech_st/11.2.0/lib:/Oracle/DBlink_RPM/Software/usr/local/lib/libodbc.so/lib,SHLIB_PATH=/VIS/DEV/db/tech_st/11.2.0/lib32:/Oracle/DBlink_RPM/Software/usr/local/lib/libodbc.so/lib)
    )
    )


    STARTUP_WAIT_TIME_DEV = 0
    CONNECT_TIMEOUT_DEV = 10
    TRACE_LEVEL_DEV = OFF

    LOG_DIRECTORY_DEV = /VIS/DEV/db/tech_st/11.1.0/network/admin
    LOG_FILE_DEV = DEV
    TRACE_DIRECTORY_DEV = /VIS/DEV/db/tech_st/11.1.0/network/admin
    TRACE_FILE_DEV = DEV
    ADMIN_RESTRICTIONS_DEV = ON
    SUBSCRIBE_FOR_NODE_DOWN_EVENT_DEV = OFF


    # added parameters for bug# 9286476
    LOG_STATUS_DEV = ON
    INBOUND_CONNECT_TIMEOUT_DEV = 60

    # ADR is only applicable for 11gDB
    DIAG_ADR_ENABLED_DEV = ON
    ADR_BASE_DEV = /VIS/DEV/db/tech_st/11.1.0/admin/DEV_testebs


    IFILE=/VIS/DEV/db/tech_st/12.1.0/network/admin/DEV_testebs/listener_ifile.ora


    ****tnsnames.ora****

    DEV=
    (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.14)(PORT=1526))
    (CONNECT_DATA=
    (SERVICE_NAME=DEV)
    (INSTANCE_NAME=DEV)
    )
    )

    NILGDB2=
    (DESCRIPTION =
    (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.102)(port=50000))
    (CONNECT_DATA =
    (SID = NILGDB2)
    )
    (HS=OK)
    )


    Please refer above configuration file lisener.ora,tnsname.ora,initSID.ora,odbc.ini,odbcinst.ini ,if any mistake i have done,

    Regards and Thanks
    Dharma Lingam
  • 13. Re: Create DB-Link for Oracle Database 11g to IBM DB2
    kgronau Guru
    Currently Being Moderated
    Not sure, but it looks like you mixed some configurations. The ODBC driver libdb2.so is commonly used to connect to a DB2 UDB on LUW platforms, but the parameters
    DefaultPkgLibrary = QGPL
    DefaultPackage = A/DEFAULT(IBM),2,0,1,0,512

    sound like they belong to the AS400 ODBC driver.

    Earlier you've stated that you want to connect to the UDB DB2 on Windows, so the driver is correct - but the odbc.ini entries aren't.
    The LUW odbc driver is configured using the config file db2dsdriver.cfg => see http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=%2Fcom.ibm.swg.im.dbclient.config.doc%2Fdoc%2Fc0054555.html

    Once you called the utility, please post the file db2dsdriver.cfg, your modified odbc.ini and also do not forget to test the database link using isql and this syntax: isql -v dblink4db2 db2admin nilgiris


    - Klaus
  • 14. Re: Create DB-Link for Oracle Database 11g to IBM DB2
    Dharma_ Newbie
    Currently Being Moderated
    Dear kalus,

    Thanks for ur updates,

    still i cant connect
    [oradev@testebs cfg]$ isql -v dblink4db2 db2admin nilgiris
    [IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
    [ISQL]ERROR: Could not SQLConnect
    [oradev@testebs cfg]$


    pls refer below configuration information,

    configuration>
    <DSN_Collection>
    <dsn alias="dblink4db2" name="NILGDB2" host="192.168.2.245" port="50000"/>
    <!-- Long aliases are supported -->
    <dsn alias="longaliasname2" name="name2" host="server2.net1.com" port="55551">
    <parameter name="Authentication" value="Client"/>
    </dsn>
    </DSN_Collection>
    <databases>
    <database name="NILGDB2" host="192.168.2.245" port="50000">
    <parameter name="CurrentSchema" value="OWNER1"/>
    <WLB>
    <parameter name="enableWLB" value="true"/>
    <parameter name="maxTransports" value="50"/>
    </WLB>
    <ACR>
    <parameter name="enableACR" value="true"/>
    </ACR>
    </database>
    <!-- Local IPC connection -->
    <!-- Local IPC connection -->
    <database name="DEV" host="192.168.2.14" port="1526">
    <parameter name="IPCInstance" value="DB2"/>
    <parameter name="CommProtocol" value="IPC"/>
    </database>
    </databases>
    <parameters>
    <parameter name="GlobalParam" value="Value"/>
    </parameters>
    </configuration>

    Regards
    Dharma Lingsm
1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points