ow to Configure DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX Itanium) to Connect to Non-Oracle Databases Post Install

GOAL

Starting with 11g Oracle now provides a Database Gateway for ODBC for the 64bit Unix operating systems on the following platforms: Linux 64bit, AIX 64bit, Solaris 64bit and HP-UX Itanium 64bit.

It can be used to connect to any 3rd party database using a suitable 3rd party ODBC driver.
Please be aware: Installing the 32bit DG4ODBC gateway on a 64bit platform is not supported.

Differences between the older product (Generic Connectivity or sometimes also called HSODBC) and DG4ODBC (Database Gateway for ODBC):
- DG4ODBC is now a stand alone product. It is no longer required to have DG4ODBC running on the
same machine and within the same ORACLE_HOME as the Oracle database.
- DG4ODBC is now a 64bit application for 64bit platforms and requires a 64bit ODBC driver manager as
well as a 64bit ODBC driver EXCEPTION: DG4ODBC on HP-UX RSIC 11.1 is still 32bit and needs a 32bit ODBC driver and a 32bit ODBC DRIVER MANAGER - se also DG4ODBC on HP-UX RISC configuration covered in <<466228 .1="">>.
- DG4ODBC requires now a ODBC LEVEL 3 COMPLIANT ODBC driver.

The gateway is certified also for older Oracle releases like 10.2.0.4, 10.2.0.5 and for a small amount of 9.2 and 10.1 databases where the gateway compatibility patch bug <> has been ported to.

You'll find the gateway compatibility patch on "My Oracle Support" by performing a simple search under the 'Patches & Updates' tab at the top of the page. Choose 'Simple Search' and enter 5965763 in the block for the patch number and select the appropriate platform from the Platform or Language list.

If the patch is not available for your platform and/or release you have to update the Oracle database to a newer version.

SOLUTION

How to Setup DG4ODBC (Oracle Database Gateway for ODBC) on 64bit Unix platforms

The Oracle Database Gateway for ODBC for 64bit Unix platforms comes on a separate CD. It can be installed into an existing 11g database Oracle_Home (please pay attention: if the Oracle_Home contains an already patched release of the database, you MUST apply this patchset again. The reason is the gateway installation might overwrite already patched libraries with the base version as delivered on the CD. To get a proper environment again, the already applied patchset needs to be reapplied).

After the installation the following items must be configured:

0) ODBC driver
1) listener (in the ORACLE_HOME where the gateway software is installed)
2) tnsnames (in the ORACLE_HOME where the Oracle database software is installed)
3) init.ora of the hs subsystem (in the ORACLE_HOME where the gateway software is installed)
4) Oracle database

0) The ODBC driver

There are several ODBC vendors for UNIX platforms.

Below is a description using the ODBC driver from Data Direct Technologies. They also provide a 15 day trial license for Linux based platforms. More information is available at the following URL:
<http://www.datadirect.com>

The libraries of other ODBC vendors will vary; so please make sure to change the libraries to the libraries of your odbc vendor. Also the tools to test an ODBC connection differ - for example a another common ODBC test utility which is shipped with the unixODBC Driver Manager is called isql.

As mentioned, Data Direct Technologies provides a trial version. If you download this trial version, please make sure you download also the service pack for the odbc driver -if it exists-.

Another feature of this driver is that it also contains some mechanism to check the ODBC connectivity.

Install the ODBC driver into a separate home directory. Let's call this home directory ODBC_HOME (i.e. /home/odbc/dd) directory.
To install the driver, you can create a new user called odbc who owns the software.
Install the ODBC driver as mentioned in the documentation.

- Configuring the odbc.ini file:
The odbc.ini file is similar to an address book for the odbc driver. It is located by default in the ODBC_HOME directory, but can be placed anywhere you like.

A side note how odbc works: The odbc driver (nothing else than a library) gets a request to connect to a server described in the odbc.ini file. The alias for the description of the server is called:
Data Source Name (=DSN).
Then the driver reads the information from the odbc.ini file according to the specified DSN and connects to the server.

The prerequisite for the odbc driver to connect to the server is the configured odbc.ini.
It is divided into 3 different sections:

[ODBC Data Sources]
[]
[ODBC]

[ODBC Data Source] is the section that contains all the available DSNs.
[] contains the different names of the DSNs and specifies the connect details.
[ODBC] is the general section for the odbc driver

A very simple file of the odbc.ini file may look like:

[ODBC Data Sources]
mssql=MS SQL Server

[mssql]
Driver=/home/odbc/dd/lib/ivmsss22.so
Description=DataDirect 5.2 SQL Server Wire Protocol
Database=
LogonID=
Password=
Address=,
QuotedId=YES
AnsiNPW=YES

[ODBC]
IANAAppCodePage=4
InstallDir=/home/odbc/dd
Trace=0
TraceDll=/home/odbc/dd/lib/odbctrac.so
TraceFile=odbctrace.out
UseCursorLib=0
UseCursorLib=0

The section [ODBC Data Sources] contains one datasource called mssql. The configuration behind the data source mssql is found in the section [mssql].
It contains the address (and port) of the database server to contact, the driver library being used and the user id and password of the remote database server.
The [ODBC] section contains general paremeters like tracing (Trace=1 enables tracing, Trace=0 disables it).

After configuring the odbc.ini file, the first step is to check if the ODBC configuration works. Data Direct Technologies provides a demo program to test the connectivity and fetches some data from the remote server.
The directory $ODBC_HOME/demo contains some sql scripts to create a demo table called EMP on the foreign database.
For the Microsoft SQL Server for example the script is called empsqlsrv.sql. Run this script on the SQL Server to create the EMP table. (Please make sure that you don't overwrite/delete any tables you need).

Before calling the program demoodbc, you need to set two environment variables:
a) LD_LIBRARY_PATH/LIBPATH must contain the odbc library path
AIX: export LIBPATH=$ODBC_HOME/lib:$LIBPATH
OTHER: export LD_LIBRARY_PATH=$ODBC_HOME/lib:$LD_LIBRARY_PATH
b) ODBCINI
export ODBCINI=$ODBC_HOME/odbc.ini
The ODBCINI parameter will guarantee the newly configured odbc.ini file from above is used.

Now calling the odbc demo program to query the remote MS SQL Server:
demoodbc -uid -pwd
like
demoodbc -uid sa -pwd sa mssql
should connect to the server and query the EMP table.

Please make sure you can successfully query the table AND don't proceed if this configuration fails!
If you have problems configuring the odbc driver, please contact the vendor of the driver.

Normally the ODBC driver is installed as a different user then the Oracle user. To test the odbc connectivity for the Oracle user the following should be performed:
Set the ODBCINI and ODBC_HOME environment variable and add the $ODBC_HOME/lib directory to the $LD_LIBRARY_PATH/LIBPATH. (The details how to do it are described in Part I.)

Now execute as the ORACLE User (who starts the listener) the demoodbc program:
$ODBC_HOME/demo/demoodbc -uid sa -pwd sa mssql
A similar output should be generated:
DataDirect Technologies, Inc. ODBC Sample Application.
will connect to data source 'mssql' as user 'sa/sa'.

First Name Last Name Hire Date Salary Dept
---------- --------- --------- ------ ----
Tyler Bennett 1977-01-06 00:00:00.000 32000.0 D101
George Woltman 1982-07-08 00:00:00.000 53500.0 D101
Rich Holcomb 1983-01-06 00:00:00.000 49500.0 D202
Richard Potter 1986-12-04 00:00:00.000 15900.0 D101
David Motsinger 1985-05-05 00:00:00.000 19250.0 D202
Tim Sampair 1987-02-12 00:00:00.000 27000.0 D101
SQLFetch returns: SQL_NO_DATA_FOUND

SIDE NOTE: Please make sure QuotedId is set to yes; else DG4ODBC might fail with error:

hgopoer, line 159: got native error 170 and sqlstate 42000; message follows...
[DataDirect][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 's
ubq1'.
Exiting hgopoer, rc=0 at 2008/06/19-14:10:17
hgopars, line 347: calling SQLNumResultCols got sqlstate 42000
Exiting hgopars, rc=28500 at 2008/06/19-14:10:17 with error ptr FILE:hgopars.c L
INE:377 FUNCTION:hgopars() ID:Preprocess number of columns

1) The listener needs a new SID entry like:

For AIX:

(SID_NAME=DG4ODBC)
(ORACLE_HOME=/home/oracle/oracle/product/11.1)
(ENV="LIBPATH=//lib:/home/oracle/product/11.1/lib")
(PROGRAM=dg4odbc)

For all other 64bit Unix systems:

(SID_NAME=DG4ODBC)
(ORACLE_HOME=/home/oracle/oracle/product/11.1)
(ENV="LD_LIBRARY_PATH=//lib:/home/oracle/product/11.1/lib")
(PROGRAM=dg4odbc)

Please correct the ORACLE_HOME entry and the ENV entry according to your installation.
We strongly recommend to add the LD_LIBARARY_PATH/LIBPATH to the listener.ora file to avoid any conflicts with already existing ODBC driver managers.
The LD_LIBRARY_PATH/LIBPATH must contain the fully qualified path to the $ORACLE_HOME/lib and also the library paths of the ODBC driver manager and the ODBC driver itself.
Please do NOT use $ORACLE_HOME variable in the ENV path.

So a listener.ora file with a listener listening on port 1511 might look like:

SID_LIST_LISTENER =
   (SID_LIST =
      (SID_DESC =
       (SID_NAME=DG4ODBC)
      (ORACLE_HOME=/home/oracle/oracle/product/11.1)
      (ENV="LD_LIBRARY_PATH=/home/odbc/dd/lib:/home/oracle/product/11.1/lib")
      (PROGRAM=DG4ODBC)
      )
   )

LISTENER =
   (DESCRIPTION_LIST =
      (DESCRIPTION =
         (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = ) (PORT = 1511))
         )
      )
   )

The listener must be stopped and started after changing the listener.ora file!

2) The tnsnames.ora needs an entry for the DG4ODBC alias:

DG4ODBC.de.oracle.com =
   (DESCRIPTION=
      (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1511))
      (CONNECT_DATA=(SID=DG4ODBC))
      (HS=OK)
   )

The domain of the tns alias can differ from the one used above (de.oracle.com), depending on the parameter in the sqlnet.ora:
NAMES.DEFAULT_DOMAIN = de.oracle.com

The important entry is the (HS=OK) key word. (HS=) is also a valid entry, but DBCA and NetCA will only recognize (HS=OK) entries and remove any (HS=) entries.

After adding the tnsnames alias and restarting the listener, a connectivity check is to use tnsping .
tnsping DG4ODBC
It should come back with a success message.

3) init.ora of the gateway:

There are some restrictions on how to name the SID (described in the Net Administrators Guide in detail). At this place only a short note: don't use dots in the SID and keep it short!

The SID is also relevant for the initialisation file of the gateway. The name of the file is init.ora. In this example it is called initDG4ODBC.ora.
The file is located at $ORACLE_HOME/hs/admin.
It should contain at least the connect details:

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = mssql
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /home/odbc/dd/lib/libodbc.so
#
# ODBC specific environment variables
#
set ODBCINI=/home/odbc/dd/odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set =

Short explanation of the parameters:
HS_FDS_CONNECT_INFO points to the ODBC DSN configured in step "0" of this note.
HS_FDS_SHAREABLE_NAME points to the ODBC Driver Manager library at
$ODBC_HOME/lib/.
For the Data Direct Technologies ODBC drivers the generic odbc library on Linux is called libodbc.so. This library checks the ODBC DSN configuration and loads the driver to the foreign database server. The name of this library may differ from ODBC vendor to vendor. Please check out the driver documentation to figure out the generic odbc library. Also some ODBC driver vendors do not require an ODBC Driver Manager; so the ODBC driver library itself can be specified here. To determine if an ODBC Driver Manager is required, please contact the ODBC driver vendor.
(As not each ODBC Driver vendor documents its ODBC Driver Manager library and the library name might differ from Driver Manager to Driver Manager. A possible way to figure out the Driver Manager library name could be to check for the existence of SQLAllocConnect ODBC function within this library:
strings |grep -i sqlalloc
)

The set ODBCINI=/home/odbc/dd/odbc.ini points to the location of an odbc.ini file you want to use with this DG4ODBC configuration.

4) Configuring the Oracle database

The only thing that must be done here is to create a database link: 
 connect with the username/password that has sufficient rights to create a database link (i.e. system). 
 The syntax is: 
 create [public] database link  
 connect to  identified by  using ''; 
 
 In other words, to connect to the MS SQL Server configured in the last steps, the syntax must be: 
 CREATE DATABASE LINK sqlserver 
 CONNECT TO "sa" IDENTIFIED BY "sa" USING 'DG4ODBC'; 
 
 The db link name is sqlserver. Username and password must be in double quotes, because the username and password are case sensitive in SQL Server. 'DG4ODBC' points to the alias in the tnsnames.ora file that calls the HS subsystem. 
 
 If everything is configured well, a select of a SQL Server table should be successful: 
 
 select * from "systables"@sqlserver; 
 ... 
 
 Side note: The systables table name at the MS SQL Server is in small letters. As the MS SQL Server is case sensitive this table name must be surrounded by double quotes. "@sqlserver" points to the name of the database link to the MS SQL Server.

Ask Questions, Get Help, And Share Your Experiences With This Article

Would you like to explore this topic further with other Oracle Customers, Oracle Employees, and Industry Experts?

Click here to join the discussion where you can ask questions, get help from others, and share your experiences with this specific article.
Discover discussions about other articles and helpful subjects by clicking here to access the main My Oracle Support Community page for Database Gateways.

======================================================================

Oracle DBA Information at one place

Search This Blog