If your Oracle database is running on Linux or UNIX, you can use Oracles 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, see the DB2 ODBC driver documentation. Refer to the documentation to see which environment variables you need to set (LD_LIBRARY_PATH, LIBPATH, LD_RUN_PATH or SHLIB_PATH depending on the platform and linker).
/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, type "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 please remember to turn this off by commenting the line out when you no longer require logging. |
AIX 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.
HP-UX 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 will 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 have 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. Do not 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 will 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.
© 1993 - 2024 Easysoft Limited. All rights reserved. Oracle® is a registered trademark of Oracle® Corporation and/or its affiliates. Other trademarks and registered trademarks appearing on easysoft.com are the property of their respective owners.
