Cloud Photo

Azure Data Architect | DBA

Using a DBLink in Oracle to Connect to an IBM DB2 Database

I’ve been working with Oracle since version 8.0 and have wanted to be able to select * from some.table@db2 within Oracle since version 8.0. The technology has existed but it was never easy enough for me. There were heterogeneous services which sounds pleasant but I never could get it to work. So, I’ve worked through 8i, 9i, 10g and finally 11g. In 11g I’ve finally made the connection. Literally.

This is not a theoretical exercise. These are pretty much my notes that I recorded as I was going along. To be certain, this was being done on our production server after having been done on the standby server with much frustration. Hopefully, this will help you avoid such frustration. First, a bit about convention. A series of asterisks, like this *****, means I put my password there but didn’t want to show it to you. Where you see, you should use your domain name. No, I don’t have anything to do with It is not my domain name. There’s an image at the bottom of this post that might help explain how it all ties together.

We have one IBM DB2 database located at on port 446 that I would like to connect to from inside my Oracle database. The only other details I have on the DB2 system is the system name hrdb2 and I know it’s running on an iSeries. I don’t know what version of anything is on the DB2 side. Oddly enough, it didn’t seem to matter. I do know a good deal about the Oracle side. The following table provides the pertinent details of the starting point.

OS redhat release 5 92.fc14.x86_64 (64-bit)
Oracle Banner Oracle Database 11g Enterprise Edition Release – 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
Pertinent Oracle Envs ORACLE_UNQNAME=IAN2





unixODBC version 2.2.14Packages Installed (rpm -qa | grep ODBC):






One of the key components that we need is the IBM odbc driver libs. I downloaded them from here []. Yes, you have to sign up for an account. Go to the Linux Downloads page and locate the V5R4 section. You might want the latest and greatest drivers, but they didn’t work for me. The 64-bit support that works for me is in the V5R4 package. Navigate your way to the file iSeriesAccess-5.4.0-1.6.x86_64.rpm. Login as root. Be careful.

If you try to install the rpm, you’ll probably get several dependency errors. So, that’s what we want to do. Yes, we want those errors or we could just run a dependency check.
To check dependencies:

rpm -Uvh --test iSeriesAccess-5.4.0-1.6.x86_64.rpm

To get errors:

rpm -ivh iSeriesAccess-5.4.0-1.6.x86_64.rpm

Either way, you should get the same results:

[oracle@nian2 ~]$ rpm -Uvh --test iSeriesAccess-5.4.0-1.6.x86_64.rpm
error: Failed dependencies: is needed by iSeriesAccess-5.4.0-1.6.x86_64 is needed by iSeriesAccess-5.4.0-1.6.x86_64
[oracle@nian2 ~]$ rpm -ivh iSeriesAccess-5.4.0-1.6.x86_64.rpm
error: Failed dependencies: is needed by iSeriesAccess-5.4.0-1.6.x86_64 is needed by iSeriesAccess-5.4.0-1.6.x86_64

If you look in the /usr/lib and usr/lib64 directories, you’ll see that you have If you don’t, then you need to install the unixODBC packages listed above. To get around these dependecy problems I created links for the missing files to the newer versions. Since we have two missing dependencies, I ran the following commands to bring it up (or down) to the level of the package:

ln -s /usr/lib64/ /usr/lib64/
ln -s /usr/lib64/ /usr/lib64/
ln -s /usr/lib/ /usr/lib/
ln -s /usr/lib/ /usr/lib/

Notice that I created the links in the 32-bit libs directory also. I did that because the iSeries package also installs 32-bit drivers.

So, the following ls commands should return with the links set properly.

[root@nian2 oracle]# ls -alh /usr/lib64/*
lrwxrwxrwx. 1 root root 20 Mar 29 01:27 /usr/lib64/ ->
lrwxrwxrwx. 1 root root 31 Aug 23 10:34 /usr/lib64/ -> /usr/lib64/
lrwxrwxrwx. 1 root root 20 Mar 29 01:27 /usr/lib64/ ->
-rwxr-xr-x. 1 root root 70K Aug 31 2010 /usr/lib64/

[root@nian2 oracle]# ls -alh /usr/lib64/*
lrwxrwxrwx. 1 root root 16 Mar 29 01:27 /usr/lib64/ ->
lrwxrwxrwx. 1 root root 27 Aug 23 10:40 /usr/lib64/ -> /usr/lib64/
lrwxrwxrwx. 1 root root 16 Mar 29 01:27 /usr/lib64/ ->
-rwxr-xr-x. 1 root root 416K Aug 31 2010 /usr/lib64/

Next, I installed the package with the –nodeps option. I know there are dependencies, but I just fixed that, well kind of fixed it. Regardless, here’s the command and output.

[root@nian2 oracle]# rpm -ivh --nodeps iSeriesAccess-5.4.0-1.6.x86_64.rpm
Preparing... ########################################### [100%]
1:iSeriesAccess ########################################### [100%]
post install processing for iSeriesAccess 1.6...1
iSeries Access ODBC Driver has been deleted (if it existed at all) because its usage count became zero
odbcinst: Driver installed. Usage count increased to 1.
Target directory is /etc
odbcinst: Driver installed. Usage count increased to 1.
Target directory is /etc

To ensure that we succeeded, I check the /etc/odbcinst.ini for references to the new drivers.

[root@nian2 oracle]# more /etc/odbcinst.ini
[iSeries Access ODBC Driver]
Description = iSeries Access for Linux ODBC Driver
Driver = /opt/ibm/iSeriesAccess/lib64/
Setup = /opt/ibm/iSeriesAccess/lib64/
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.
Driver64 = /opt/ibm/iSeriesAccess/lib64/
Setup64 = /opt/ibm/iSeriesAccess/lib64/
Threading = 2
DontDLClose = 1
UsageCount = 1

[iSeries Access ODBC Driver 64-bit]
Description = iSeries Access for Linux 64-bit ODBC Driver
Driver = /opt/ibm/iSeriesAccess/lib64/
Setup = /opt/ibm/iSeriesAccess/lib64/
Threading = 2
DontDLClose = 1
UsageCount = 1

Also, look in the /opt/ibm/iSeriesAccess directories and you should be able to see the driver files referenced in the /etc/odbcinst.ini file. Here is what mine looks like.

[root@nian2 oracle]# ls -alh /opt/ibm/iSeriesAccess/lib64/
total 2.5M
drwxr-xr-x. 2 root root 4.0K Aug 23 10:43 .
drwxr-xr-x. 44 root root 4.0K Aug 23 10:43 ..
-r-xr-xr-x. 1 root root 1.2M Apr 5 2008
-r-xr-xr-x. 1 root root 800K Apr 5 2008
-r-xr-xr-x. 1 root root 20K Apr 5 2008
-r-xr-xr-x. 1 root root 115K Apr 5 2008
-r-xr-xr-x. 1 root root 447K Apr 5 2008

While still logged in as root, I went ahead and populated my /etc/odbc.ini file. This is the file that contains the datasource name. To be honest, I’m not sure what all the variables mean, but they are apparently important when we connect to our DB2.

[root@nian2 oracle]# more /etc/odbc.ini
[ODBC Data Sources]
armcdb2 = connection to db2

Driver = /opt/ibm/iSeriesAccess/lib64/
Description = iSeries Access for Linux ODBC Driver
Database = hrdb2
LogonID = userid
Password = *
Address =,446
location = hrdb2
noprompt = true
system = hrdb2


Logout as root and back into your oracle account. Test the datasource name and drivers using isql. In the statement below replace the ****** with your password.

[oracle@nian2 ~]$ isql armcdb2 userid ******
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
SQL> quit

If you get the “Connected!” message, pat yourself on the back. You’re about halfway there.

There are four tasks you’ll need to complete for Oracle. You’ll need to edit your tnsnames.ora and listener.ora files and create an initsid.ora file.

First, the initsid.ora file is a throw back to the heterogeneous services. Basically, this connects oracle to the datasource name (HS_FDS_CONNECT_INFO) in the /etc/odbc.ini file and tells oracle what odbc manager (HS_FDS_SHAREABLE_NAME) to use. Pay attention to that. The ODBC Manager is not the same as the ODBC Driver provided by the iSeries Access package. In the simplest terms, this is a fake oracle instance.

File: /u01/app/oracle/product/11.2.0/dbhome_1/hs/admin/initarmcdb2.ora

set ODBCINSTINI=/etc/odbcinst.ini
set ODBCINI=/etc/odbc.ini

Next, we’ll setup the listener. The heterogeneous services need a listener that will service the fake oracle instance. After you make this change, stop and start the listener using lsnrctl stop and lsnrctl start.
File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora


(ADDRESS=(PROTOCOL=tcp)( 1521))

When you stop and start the listener, your commands and results should look something like the following. Notice that the listener shows that it is listening for your new fake instance.

[oracle@nian1 ~]$ lsnrctl stop; lsnrctl start

LSNRCTL for Linux: Version - Production on 24-AUG-2011 07:15:47

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(
The command completed successfully

LSNRCTL for Linux: Version - Production on 24-AUG-2011 07:15:52

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0/db_1//bin/tnslsnr: please wait...

TNSLSNR for Linux: Version - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/nian1/listener/alert/log.xml

Connecting to (ADDRESS=(PROTOCOL=tcp)(
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version - Production
Start Date                24-AUG-2011 07:15:52
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/nian1/listener/alert/log.xml
Listening Endpoints Summary...
Services Summary...
Service "armcdb2" has 1 instance(s).
  Instance "armcdb2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Then we add a tnsnames.ora entry for our fake instance. You’ll need to ensure that the (HS=OK) is in there.
File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

(SID = armcdb2)

Finally, we can create the database link in oracle. The sql statement I used is as follows:

SQL> connect sys/******@myOracleDatabase as sysdba

Database link created.

With the link created, we can test it.
SQL> select * from;



If the select statement returns dummy information without errors, then you have been successful in connecting your Oracle database to your DB2 database.

I want to point out that as a public database link, everyone with access to the oracle system has the same level of access on the DB2 as the user in the create database link statement. In fact, if you create a public database link to the DB2 system using your DB2 userid and an oracle user deletes everything in DB2, you just deleted everything. Be smart. Use this wisely. Your job depends on it.

If you run into trouble after the isql test works, add some tracing to the hs/admin/initsid.ora using “HS_FDS_TRACE_LEVEL=99” and check the trace files in the /hs/log directory for details.

Image showing links between iSeries Access, unixODBC and Oracle.
Linking iSeries Access, unixODBC and Oracle

Leave a Reply