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 mycompany.org, you should use your domain name. No, I don’t have anything to do with mycompany.org. 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 1.2.3.164 on port 446 that I would like to connect to from inside my Oracle 11.2.0.1 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 2.6.35.13 92.fc14.x86_64 (64-bit) |
| Oracle Banner | Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options |
| Pertinent Oracle Envs | ORACLE_UNQNAME=IAN2
ORACLE_SID=ian2_DGMGRL ORACLE_BASE=/u01/app/oracle ORACLE_HOSTNAME=nian2.mycompany.org ORACLE_TERM=xterm ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 |
| unixODBC | version 2.2.14Packages Installed (rpm -qa | grep ODBC):
unixODBC-devel-2.2.14-12.fc14.i686 unixODBC-devel-2.2.14-12.fc14.x86_64 unixODBC-2.2.14-12.fc14.i686 unixODBC-2.2.14-12.fc14.x86_64 |
| IP | 1.2.3.3 |
One of the key components that we need is the IBM odbc driver libs. I downloaded them from here [http://www-03.ibm.com/systems/i/software/access/linux/index.html]. 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:
libodbcinst.so.1()(64bit) is needed by iSeriesAccess-5.4.0-1.6.x86_64
libodbc.so.1()(64bit) 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:
libodbcinst.so.1()(64bit) is needed by iSeriesAccess-5.4.0-1.6.x86_64
libodbc.so.1()(64bit) 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 libodbcinst.so.2. 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/libodbcinst.so.2.0.0 /usr/lib64/libodbcinst.so.1
ln -s /usr/lib64/libodbc.so.2.0.0 /usr/lib64/libodbc.so.1
ln -s /usr/lib/libodbcinst.so.2.0.0 /usr/lib/libodbcinst.so.1
ln -s /usr/lib/libodbc.so.2.0.0 /usr/lib/libodbc.so.1
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/libodbcinst.so*
lrwxrwxrwx. 1 root root 20 Mar 29 01:27 /usr/lib64/libodbcinst.so -> libodbcinst.so.2.0.0
lrwxrwxrwx. 1 root root 31 Aug 23 10:34 /usr/lib64/libodbcinst.so.1 -> /usr/lib64/libodbcinst.so.2.0.0
lrwxrwxrwx. 1 root root 20 Mar 29 01:27 /usr/lib64/libodbcinst.so.2 -> libodbcinst.so.2.0.0
-rwxr-xr-x. 1 root root 70K Aug 31 2010 /usr/lib64/libodbcinst.so.2.0.0
[root@nian2 oracle]# ls -alh /usr/lib64/libodbc.so*
lrwxrwxrwx. 1 root root 16 Mar 29 01:27 /usr/lib64/libodbc.so -> libodbc.so.2.0.0
lrwxrwxrwx. 1 root root 27 Aug 23 10:40 /usr/lib64/libodbc.so.1 -> /usr/lib64/libodbc.so.2.0.0
lrwxrwxrwx. 1 root root 16 Mar 29 01:27 /usr/lib64/libodbc.so.2 -> libodbc.so.2.0.0
-rwxr-xr-x. 1 root root 416K Aug 31 2010 /usr/lib64/libodbc.so.2.0.0
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/libcwbodbc.so
Setup = /opt/ibm/iSeriesAccess/lib64/libcwbodbcs.so
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/libcwbodbc.so
Setup64 = /opt/ibm/iSeriesAccess/lib64/libcwbodbcs.so
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/libcwbodbc.so
Setup = /opt/ibm/iSeriesAccess/lib64/libcwbodbcs.so
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 libcwbcore.so
-r-xr-xr-x. 1 root root 800K Apr 5 2008 libcwbodbc.so
-r-xr-xr-x. 1 root root 20K Apr 5 2008 libcwbodbcs.so
-r-xr-xr-x. 1 root root 115K Apr 5 2008 libcwbrc.so
-r-xr-xr-x. 1 root root 447K Apr 5 2008 libcwbxda.so
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
[armcdb2]
Driver = /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so
Description = iSeries Access for Linux ODBC Driver
Database = hrdb2
LogonID = userid
Password = *
Address = 1.2.3.164,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
HS_FDS_CONNECT_INFO = armcdb2
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so.2.0.0
HS_FDS_SUPPORT_STATISTICS=FALSE
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
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=armcdb2)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM=dg4odbc)
(ENVS=LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/dbhome_1/lib:/opt/ibm/iSeriesAccess/lib)
)
)
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=nian2.mycompany.org)(PORT= 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 11.2.0.1.0 - Production on 24-AUG-2011 07:15:47
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=nian1.mycompany.org)(PORT=1521))
The command completed successfully
LSNRCTL for Linux: Version 11.2.0.1.0 - 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 11.2.0.1.0 - 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
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=nian1.mycompany.org)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=nian1.mycompany.org)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - 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...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=nian1.mycompany.org)(PORT=1521)))
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
armcdb2=
(DESCRIPTION =
(ADDRESS=(PROTOCOL=TCP)(HOST=nian2.mycompany.org)(port=1521))
(CONNECT_DATA =
(SID = armcdb2)
)
(HS=OK)
)
Finally, we can create the database link in oracle. The sql statement I used is as follows:
SQL> connect sys/******@myOracleDatabase as sysdba
Connected.
SQL> CREATE PUBLIC DATABASE LINK "ARMCDB2.MYCOMPANY.ORG" CONNECT TO "MA24878" IDENTIFIED BY iamatwork USING 'armcdb2';
Database link created.
With the link created, we can test it.
SQL> select * from dual@armcdb2.mycompany.org;
D
-
X
SQL>
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.
Leave a Reply
You must be logged in to post a comment.