Generic Connectivity

Connect Oracle to another DB

This document describe one of the most troblesome 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.

Back to Index

Contact information:
fabrizio.magni _at_ gmail.com