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.
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.
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).
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.
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
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:
cd
to the directory where unixODBC's libraries are installed. For example:
cd /usr/local/easysoft/unixODBC/lib
ar -X32_64 -xv libodbc.a
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
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.
$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.
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.
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.
CREATE PUBLIC DATABASE LINK db2 CONNECT TO "dbuser" IDENTIFIED BY "dbpassword" using 'db2con';
Replace dbuser
and dbpassword
with a valid username and password for the target DB2 database.