Connect Oracle to another DB
This document describe one of the most troublesome configuration I have ever set up.
It took me almost two days to figure out how to properly set everything to make it works so I’m writing this documentation to spare a headache to other sysadmins/DBA who wish to achieve the same.
I divided the process in three steps:
- DB2 client configuration,
- odbc configuration,
- heterogeneous connectivity configuration.
First and third points have been the hardest since my knowledge of DB2 is limited.
DB2connect configuration
First of all install the DB2 client (for me it was DB2connect 7.1) and register it with the proper license (using db2licm).
Now you are ready to register your remote database.
I’ll need to provide:
hostname,
port,
database name,
authentication method.
For every DB I need three registrations: tcp/ip node, database and DCS.
Let’s start from the tcp/ip node.
Connect to your db2 user (by default db2inst1):
db2inst1@brepredbls01:~> db2
(c) Copyright IBM Corporation 1993,2001
Command Line Processor for DB2 SDK 7.2.0
db2 =>
Now from the db2 client command prompt:
catalog tcpip node <nodename> remote <hostaname> server <port>
where nodename is an alias you choose, hostname is the DB2 remote hostname and the port is the DB2 listening port.
example:
catalog tcpip node RIHEP remote rihep.rit server 5023
to unregister it:
uncatalog node RIHEP
and to list the register nodes:
db2 => list node directory
Node Directory
Number of entries in the directory = 3
Node 1 entry:
Node name = AMDSPT
Comment =
Protocol = TCPIP
Hostname = amdahlsvil.ras
Service name = 5023
Node 2 entry:
Node name = AMSVIL
Comment =
Protocol = TCPIP
Hostname = amdahlsvil.ras
Service name = 6021
Node 3 entry:
Node name = RIHEP
Comment =
Protocol = TCPIP
Hostname = rihep.rit
Service name = 5023
Now you need to catalog your remote DB2 database:
catalog database <DBname> as <DBalias> at node <nodename> authentication DCS
Where DBname is the name of the remote database, DBalias is the name you are going to use in your connection and nodename is the node alias you registered above.
The chosen authentication has been DCS for my environment.
Example:
catalog database ITFINDB2 as ITFINDB2 at node RIHEP authentication DCS
If you wish to unregister the DB:
uncatalog database ITFINDB2
for the list:
db2 => list db directory
System Database Directory
Number of entries in the directory = 3
Database 1 entry:
Database alias = ITFINDB2
Database name = ITFINDB2
Node name = RIHEP
Database release level = 9.00
Comment =
Directory entry type = Remote
Authentication = DCS
Catalog node number = -1
Database 2 entry:
Database alias = DB2PROD
Database name = DB2PROD
Node name = AMSVIL
Database release level = 9.00
Comment =
Directory entry type = Remote
Authentication = DCS
Catalog node number = -1
Database 3 entry:
Database alias = DB2DSPT
Database name = DB2DSPT
Node name = AMDSPT
Database release level = 9.00
Comment =
Directory entry type = Remote
Authentication = DCS
Catalog node number = -1
Last registration step: the DCS.
catalog dcs database <DBname> as <DBalias>
example:
catalog dcs database ITFINDB2 as ITFINDB2
to unregister:
unregister dcs ITFINDB2
For the list:
db2 => list dcs directory
Database Connection Services (DCS) Directory
Number of entries in the directory = 3
DCS 1 entry:
Local database name = DB2DSPT
Target database name = DB2DSPT
Application requestor name =
DCS parameters =
Comment =
DCS directory release level = 0x0100
DCS 2 entry:
Local database name = DB2PROD
Target database name = DB2PROD
Application requestor name =
DCS parameters =
Comment =
DCS directory release level = 0x0100
DCS 3 entry:
Local database name = ITFINDB2
Target database name = ITFINDB2
Application requestor name =
DCS parameters =
Comment =
DCS directory release level = 0x0100
Now you can check if your configuration is correct:
db2 => connect to ITFINDB2 user sisbanc
Enter current password for sisbanc:
Database Connection Information
Database server = DB2 OS/390 7.1.1
SQL authorization ID = SISBANC
Local database alias = ITFINDB2
This indicate a succesful connection.
An error or a command prompt without output indicates a failure.
ex:
db2 => connect to ITFINDB2 user sisbanc
Enter current password for sisbanc:
db2 => db2 =>
unixODBC configuration
If the DB2 client is set correctly you can step forward.
Now you need to configure your odbc.
I installed on my SLES9 the unixODBC package using yast (the operation is really simple).
Now in /etc/unixODBC I’m going to store all my configuration files (basically odbcinst.ini and odbc.ini).
This is my odbcinst.ini
db2inst1@brepredbls01:~> cat /etc/unixODBC/odbcinst.ini
[DB2]
Description = ODBC for DB2
Driver = /usr/IBMdb2/V7.1/lib/libdb2.so
FileUsage = 1
DontDLClose = 1
Here I simply configured a generic entry called DB2 which uses the driver (library) of the DB2 client installed and set above.
The important part is the driver. Make sure the location of the libdb2.so is correct on your system.
For the odbc.ini:
db2inst1@brepredbls01:~> cat /etc/unixODBC/odbc.ini
[DB2PROD]
Description = DB2PROD for Prevint
Driver = DB2
Database = DB2PROD
#DMEnvAttr = SQL_ATTR_UNIXODBC_ENVATTR={DB2INSTANCE=db2inst1}
[ITFINDB2]
Description = ITFINDB2 for Prevint
Driver = DB2
Database = ITFINDB2
[DB2DSPT]
Description = DB2DSPT for Prevint
Driver = DB2
Database = DB2DSPT
I have three entries like the three databases I configured in my systems.
All has the driver set to DB2 which indicates they have to take the entry in your odbcinst.ini and using the settings there (so the libdb2.so).
I’m going to discuss the SQL_ATTR_UNIXODBC_ENVATTR later.
To check if the configuration is working properly use the unixODBC client: isql.
brepredbls01:/etc/unixODBC # isql ITFINDB2 SISBANC SISBANC
+—————————————+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+—————————————+
SQL> select count(*) FROM TRAS.POL_ELEN_ELET
+————+
| |
+————+
| 493318 |
+————+
SQLRowCount returns -1
1 rows fetched
Oracle Generic Connectivity configuration
This is the last step. You are going to configure oracle HS in order to be able to access a DB2 via DB link.
Three are the files used for the configuration: listener.ora, tnsnames.ora and initDBNAME.ora.
Let’s start from the latter.
In $ORACLE_HOME/hs/admin you need a init file for the HS service you are creating.
The information inside this file are related to the ODBC DSN you configured in setp 2.
Here is the content of a file of mine:
oracle@brepredbls01:/u01/app/oracle/product/10.1/db_1/hs/admin> cat initITFINDB2.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.
set ODBCINI=/etc/unixODBC/odbc.ini
HS_FDS_SHAREABLE_NAME=/usr/lib/libodbc.so
HS_FDS_CONNECT_INFO = ITFINDB2
HS_FDS_TRACE_LEVEL = 0
HS_FDS_TRACE_FILE_NAME = ITFINDB2.trc
HS_DB_NAME = ITFINDB2
HS_LANGUAGE= ITALIAN_ITALY.WE8MSWIN1252
Two entries are imports:
set ODBCINI=/etc/unixODBC/odbc.ini tells which odbc.ini files needs to be used in this way you can go for a non-global one (for example one editable from your oracle user).
HS_FDS_SHAREABLE_NAME=/usr/lib/libodbc.so indicates which ODBC (library) to use.
The one I pointed is the unixODBC basic library.
Later you needs to configure your listener.ora. Here is mine:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = brepredbls01.ras)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.1/db_1)
(PROGRAM = extproc)
)
(SID_DESC=
(SID_NAME=DB2PROD)
(ORACLE_HOME= /u01/app/oracle/product/10.1/db_1)
(PROGRAM=hsodbc)
(ENVS=DB2INSTANCE=db2inst1)
)
(SID_DESC=
(SID_NAME=DB2DSPT)
(ORACLE_HOME= /u01/app/oracle/product/10.1/db_1)
(PROGRAM=hsodbc)
(ENVS=DB2INSTANCE=db2inst1)
)
(SID_DESC=
(SID_NAME=ITFINDB2)
(ORACLE_HOME= /u01/app/oracle/product/10.1/db_1)
(PROGRAM=hsodbc)
(ENVS=DB2INSTANCE=db2inst1)
)
(SID_DESC=
(SID_NAME=UNS)
(ORACLE_HOME= /u01/app/oracle/product/10.1/db_1)
)
)
The entry:
(SID_DESC=
(SID_NAME=ITFINDB2)
(ORACLE_HOME= /u01/app/oracle/product/10.1/db_1)
(PROGRAM=hsodbc)
(ENVS=DB2INSTANCE=db2inst1)
Indicates which service to look for. In this case an HOODBC calles ITFINDB2 (so looking for $ORACLE_HOME/hs/admin/initITFINDB2.ora).
The (ENVS=DB2INSTANCE=db2inst1) is discussed later.
The listener is listening for the HS services:
lsnrctl status
LSNRCTL for Linux: Version 10.1.0.4.0 – Production on 13-OCT-2005 10:25:46
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=brepredbls01.ras)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 10.1.0.4.0 – Production
Start Date 12-OCT-2005 16:27:11
Uptime 0 days 17 hr. 58 min. 34 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10.1/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10.1/db_1/network/log/listener.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=brepredbls01.ras)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=brepredbls01.ras)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary…
Service “DB2DSPT” has 1 instance(s).
Instance “DB2DSPT”, status UNKNOWN, has 1 handler(s) for this service…
Service “DB2PROD” has 1 instance(s).
Instance “DB2PROD”, status UNKNOWN, has 1 handler(s) for this service…
Service “ITFINDB2” has 1 instance(s).
Instance “ITFINDB2”, status UNKNOWN, has 1 handler(s) for this service…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Service “UNS” has 2 instance(s).
Instance “UNS”, status UNKNOWN, has 1 handler(s) for this service…
Instance “UNS”, status READY, has 1 handler(s) for this service…
Service “UNSXDB” has 1 instance(s).
Instance “UNS”, status READY, has 1 handler(s) for this service…
The command completed successfully
Last file:
DB2PROD =
(DESCRIPTION =
(ADDRESS=(PROTOCOL=tcp)
(HOST= brepredbls01.ras)
(PORT=1521))
(CONNECT_DATA=(SID=DB2PROD))
(HS=OK)
)
DB2DSPT =
(DESCRIPTION =
(ADDRESS=(PROTOCOL=tcp)
(HOST= brepredbls01.ras)
(PORT=1521))
(CONNECT_DATA=(SID=DB2DSPT))
(HS=OK)
)
ITFINDB2 =
(DESCRIPTION =
(ADDRESS=(PROTOCOL=tcp)
(HOST= brepredbls01.ras)
(PORT=1521))
(CONNECT_DATA=(SID=ITFINDB2))
(HS=OK)
)
To make the oracle client able to point toward your listener.
Create a DBlink inside your database:
CREATE PUBLIC DATABASE LINK “ITFINDB2” CONNECT TO SISBANC IDENTIFIED by PASSWORD USING ‘ITFINDB2’;
and you are done… almost…
My main problem has been with this error:
[unixODBC][Driver Manager]Driver’s SQLAllocHandle on SQL_HANDLE_HENV failed
Googling I found that the possible reason was that the env variable DB2INSTANCE was not set.
And it was so.
But how to do it?
1.
I found four different ways (and I applied two).
Insert into odbc.ini the line:
DMEnvAttr = SQL_ATTR_UNIXODBC_ENVATTR={DB2INSTANCE=db2inst1}
This didn’t worked for me and prevented even the connections with isql.
2.
Set it directly in your oracle environemt.
Since I’m using suse and the package orarun I simply added the line:
export DB2INSTANCE=db2inst1
in my /etc/profile.d/oracle.sh
3.
Place the line:
set DB2INSTANCE=db2inst1
in your $ORACLE_HOME/hs/admin/initITFINDB2.ora
4.
in your listener.ora insert:
(ENVS=DB2INSTANCE=db2inst1)
for the SID_DESC of your DB2 service.
Personally I used the solutions 2 and 4.
After this last configuration my DBlinks where fully operational and my datawarehouse where able to retrieve data directly from DB2.
(What a hard day…)
Ok, I met another error on one of my dblink.
I started debugging from the db2 client (the lower layer) and found this error:
db2 => select * from portaf.alcommin
SQL0332N There is no available conversion for the source code page “1140” to
the target code page “1208”. Reason Code “1”. SQLSTATE=57017
This was due to codepage incompatibility.
I solved it by changing my LANG (locale) env variable in the oracle user shell.
I did it by editing the file:
/etc/SuSEconfig/profile
switching from UTF8 to iso8859-15.