How do I connect Oracle to DB2?

If your Oracle database is running on Linux or UNIX, you can use Oracle's Database Gateway for ODBC (DG4ODBC) and the DB2 ODBC driver to connect Oracle to IBM DB2.

DG4ODBC is included with Oracle 11g and later and is compatible with Oracle 10g and later.

DG4ODBC interacts with Heterogeneous Services (an Oracle database component) to allow Oracle client applications to access non-Oracle databases. The non-Oracle data is transparently integrated, and so Oracle client applications are not aware that the data is stored in a remote database from another vendor.

The following instructions show you how to connect Oracle with DB2. For further information about DG4ODBC, refer to our DG4ODBC tutorial.

  1. Check whether your version of DG4ODBC is 32-bit or 64-bit: 
    cd $ORACLE_HOME/bin
file dg4odbc

    If the file commands output contains ELF 64-bit LSB executable, or something similar, DG4ODBC is 64-bit, and you need to download the 64-bit DB2 ODBC driver for your platform. Otherwise, you need to use the 32-bit DB2 ODBC driver for your platform.

  2. Install and license the DB2 ODBC driver on the machine where DG4ODBC is installed.

    For installation instructions, refer to the DB2 ODBC driver documentation. Refer to the documentation to find out which environment variables you need to set (LD_LIBRARY_PATH, LIBPATH, LD_RUN_PATH, or SHLIB_PATH depending on the platform and linker).

  3. Test that you can use the DB2 ODBC driver to access your DB2 database. To do this, create an ODBC data source in your odbc.ini file (which is usually located in /etc). For example: 
    [DB2]
Driver          = Easysoft ODBC-DB2
Server          = machine_name_or_ip_address_of_db2_instance
Database        = my_db2_database
User            = my_os_user
Password        = my_os_password
ConvWToUtf      = yes

    If your DB2 instance is not listening on the default port (50000), you also need to specify the port number. For example:

    Port = 50001

    Use isql to test your new ODBC data source. For example:

    cd /usr/local/easysoft/unixODBC/bin
./isql.sh -v DB2

    At the prompt, enter help to display a list of tables. To exit, press Return in an empty prompt line.

  4. Create a DG4ODBC init file.

    The initsid.ora file references the ODBC data source that you added to /etc/odbc.ini. For this example, we will call our init file initdb2.ora. This file needs to be written to your $ORACLE_HOME/hs/admin directory as the Oracle user. For example:

    cd $ORACLE_HOME/hs/admin
cp initdg4odbc.ora initdb2.ora
  5. Ensure these parameters and values are present in your init file: 
    HS_FDS_CONNECT_INFO = DB2
HS_FDS_SHAREABLE_NAME = /usr/local/easysoft/unixODBC/lib/libodbc.so
HS_LANGUAGE = AMERICAN_AMERICA.US7ASCII
#HS_FDS_TRACE_LEVEL = 4
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini

    The following table describes these parameters:

    Parameter Description
    HS_FDS_CONNECT_INFO The name of the ODBC data source that you added to /etc/odbc.ini.
    HS_FDS_SHAREABLE_NAME The path to the unixODBC Driver Manager library on your machine.
    HS_LANGUAGE This is required to prevent DG4ODBC using the UTF-8 character encoding.
    HS_FDS_TRACE_LEVEL Un-comment this parameter if you need to obtain an initsid log file. By default, log files are written to your $ORACLE_HOME/hs/log folder. A new log file is created for each connection. so remember to turn this off by commenting the line out when you no longer require logging.

    Note On AIX, you need to extract and then rename the unixODBC Driver Manager shared object. To do this:

    1. cd to the directory where unixODBC's libraries are installed. For example: 
      cd /usr/local/easysoft/unixODBC/lib
    2. Extract the shared object from the archive: 
      ar -X32_64 -xv libodbc.a
    3. Rename the shared object to libodbc.so: 
      mv libodbc.so.1 libodbc.so

    Then add this entry to your Oracle user's .profile file:

    LIBPATH=$LIBPATH:/usr/local/easysoft/lib:/usr/local/easysoft/unixODBC/lib
export LIBPATH

    The library path needs to be set before the Oracle listener is started.

    Note On HP-UX machines, shared objects have the extension .sl. Your initsid entry needs include:

    HS_FDS_SHAREABLE_NAME = /usr/local/easysoft/unixODBC/lib/libodbc.sl
  6. For the Oracle Listener to listen for DG4ODBC, information about DG4ODBC must be added to the Oracle Listener configuration file, listener.ora. By default listener.ora is located in $ORACLE_HOME/network/admin.

    Add an entry to listener.ora that creates a SID_NAME for DG4ODBC. For example:

    SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC=
     (SID_NAME=db2)
     (ORACLE_HOME=oracle_home_directory)
     (PROGRAM=dg4odbc)
     (ENVS=LD_LIBRARY_PATH = /usr/local/easysoft/unixODBC/lib:
            /usr/local/easysoft/lib)
   )
 )

    Replace oracle_home_directory with the value of $ORACLE_HOME. For example, /u01/app/oracle/product/11.2.0/dbhome_1/.

    The SID_NAME parameter value is the SID_NAME for your init file. In our test, we called the init file initdb2.ora, so our SID_NAME is db2.

    The ENVS parameter value is the environment variables that you need to set for the DB2 ODBC driver. On AIX, you need to replace LD_LIBRARY_PATH with LIBPATH.

    On HP-UX you need to replace LD_LIBRARY_PATH with SHLIB_PATH.

    Some versions of DG4ODBC need to locate additional Oracle libraries when run. If this is the case, you need to include the $ORACLE_HOME/lib folder in the ENVS parameter value.

    Save the file when you have entered this information.

  7. Add a DG4ODBC entry to $ORACLE_HOME/network/admin/tnsnames.ora that specifies the SID_NAME created in the previous step. For example: 
    db2con=
 (DESCRIPTION=
   (ADDRESS=(PROTOCOL=TCP)(HOST=oracle_host)(PORT=1521))
   (CONNECT_DATA= (SID=db2))
   (HS=OK)
 )

    The tnsnames.ora entry can be given the same name as SID_NAME value (and so in our example, db2con could be db2.) The SID parameter value has to be the same as the SID_NAME value.

    Save the file when you've entered this information.

  8. Start (or restart) the Oracle Listener: 
    cd $ORACLE_HOME/bin
./lsnrctl stop
./lsnrctl start

    When you start the listener, you may get information messages similar to:

    Service "db2con" has 1 instance(s).
Instance "db2con", status UNKNOWN, has 1 handler(s) for this  service...
The command completed successfully

    The status UNKNOWN is returned by DG4ODBC, as the service is not created until it is used.

    You can also run lsnrctl and enter SERVICES to display service status information. Don't use the reload option within the lnsrctl program as this does not always reread your configuration files.

  9. After adding the tnsnames.ora alias and restarting the listener, use tnsping alias to check that you can connect to the new service. If tnsping succeeds, you get a message similar to: 
    $ tnsping db2con

    TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 06-MAY-2008
17:19:20
    Copyright (c) 1997, 2007, Oracle.  All rights reserved.

    Used parameter files:
    /home/oracle/2/product/11.2.0/db_1/network/admin/sqlnet.ora

    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=1521))
     (CONNECT_DATA= (SID=db2)) (HS=OK))
    OK (0 msec)

    If the tnsping fails, please check the three Oracle configuration files for errors.

  10. Connect to your Oracle database in SQL*Plus.
  11. In SQL*Plus, create a database link for the target DB2 database. For example: 
    CREATE PUBLIC DATABASE LINK db2 CONNECT TO "dbuser" IDENTIFIED BY "dbpassword" using 'db2con';

    Replace dbuser and dbpassword with a valid user name and password for the target DB2 database.

Further information