1 2 3 Previous Next 44 Replies Latest reply: Aug 8, 2012 12:51 PM by Dharma_ RSS

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

    Dharma_
      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
          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_
            Hai Mike,

            Thanks,

            I will check and let you know...

            Lingam
            • 3. Re: Create DB-Link for Oracle Database 11g to IBM DB2
              Dharma_
              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-Oracle
                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_
                  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-Oracle
                    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_
                      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-Oracle
                        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_
                          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-Oracle
                            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_
                              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_
                                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-Oracle
                                  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_
                                    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