QUICK START GUIDE: 
Setting up Connect for ODBC with Oracle's Heterogeneous Services

 

	More Resources by Google:

Gathered By: John Kazerooni  

Setting up Connect for ODBC with Oracle's Heterogeneous Services

This document describes the steps to be performed when configuring Generic Connectivity on a Unix system.

Generic Connectivity Architecture 
Generic connectivity is implemented by utilizing a Heterogeneous Services ODBC agent. An ODBC agent is included as part of your Oracle8i system. Be sure to use the agent shipped with your particular Oracle system and installed in the same $ORACLE_HOME. To access the non-Oracle data store using generic connectivity, the agent works with an ODBC driver. The driver that you use must be on the same platform as the agent. The non-Oracle data stores can reside on the same machine as Oracle8i or a different machine. 

Installation Steps 
Note: This example shows the configuration of Generic Connectivity on a SUN Solaris system utilizing Merant's ODBC driver to connect to Microsoft SQL Server. 
Whereas the Oracle configuration steps will remain the same, the configuration of ODBC is dictated by the 3rd party driver supplier. 

1) Verify Compatibility. 
Generic Connectivity Product Availability for Unix systems: 
Sun Solaris 8.1.6 or later 
IBM AIX 8.1.7 or later 
HP-UX 8.1.7 or later 

2) Install the 3rd Party ODBC Driver. eg. Intersolve/Merant 
Some ODBC connections will require that a 3rd party datastore client application is loaded and configured in addition to the ODBC rivers. In this example, the Merant ODBC driver is capable of making a connection without an additional client application. In all cases, configure ODBC using the instructions provided by the driver supplier. 

The following basic information enables you to connect with your DataDirect Connect for ODBC drivers immediately after installation. For installation instructions, please see the DataDirect Connect for ODBC Installation Guide. 

Connecting to a Database
Once the DataDirect Connect for ODBC drivers are installed, you can connect from your application to your database. 

Environment Setup
Check your permissions: You should log in as a user with full r/w/x permissions recursively on the entire DataDirect Connect for ODBC installation directory.

Determine which shell you are running: Execute the env command.
Run the DataDirect Technologies setup script to set variables: Two scripts, odbc.csh and odbc.sh, are installed in the installation directory. For Korn, Borne and equivalent shells, execute odbc.sh. For a C shell, execute odbc.csh. After running the setup script, execute the env command to verify that the installation_directory/lib directory has been added to your shared library path.

Set ODBCINI variable: You must set the ODBCINI environment variable. The variable should point to the path from the root directory to the odbc.ini file where your data source will reside. DataDirect Connect for ODBC is installed with a template odbc.ini in the installation directory. For example: $ODBCINI= /opt/odbc/odbc.ini; export ODBCINI

Test Loading the Driver 
The ivtestlib tool is a utility to verify that the driver can be loaded into memory and is located in the installation_directory/bin directory. For example, to load the SQL Server Wire Protocol driver on Solaris (where xx represents the driver level number), you would enter:
$ivtestlib /opt/odbc/lib/ivmsssxx.so

Setting up a Data Source in the odbc.ini 
The default odbc.ini installed in installation directory is a template into which you enter your site-specific database connection information using a text editor. Each database has its own section in the template. On Solaris, for example, you might enter: 
[mssql]
Driver=/u01/odbc32v50/lib/ivmsss20.so
Description=DataDirect 5.0 SQL Server Wire Protocol
Address=x.x.x.x,1433
AnsiNPW=No
Database=ss2dev
LogonID=jk1157
password=jk1157pass
QuotedId=No 

Testing the Connection 
The DataDirect Connect for ODBC installation includes a program called "example" that can be used to connect to a data source and execute SQL. The application is located in the installation_directory/example directory. To run the program, type example and follow the prompts to enter your data source name, user name, and password. If successful, a SQL> prompt appears and you can type in SQL Statements such as select * from <table_name>. If example is unable to connect, the appropriate error message appears.

3) Make sure the following noted entries are in the odbc.ini 
(again, this example is utilizing Merant drivers) 
[mssql] 
Driver=/uo3/odbc/lib/ivmsssXX.so <= Configured during ODBC driver installation 
Description=SQL Server 
Database=dbname <= Name of target database. 
Address=120.2.200.176,1433 <= IP address and port of target database. 
Quoteld=No 
AnsiNPW=No 

4) Make sure the following entries are in the tnsnames.ora and listener.ora. 
TNSNAMES.ORA 
hsodbc= 
(description= 
(address=(protocol=tcp)(host=hostname)(port=1521)) 
(connect_data=(sid=hsmsql) <= Needs to match the sid in listener.ora.
) 
(hs=ok) <= hs clause goes in the description. 
) 

LISTENER.ORA 
listener = 
(description_list = 
(description = 
(address_list = 
(address = (protocol = tcp)(host = unixhost)(port = 1521)) 
) 
) 
sid_list_listener= 
(sid_list= 
(sid_desc= 
(sid_name=hsmsql) <= Match the sid in tnsnames.ora. 
(oracle_home=/u03/oracle/product/8.1.6) <= Appropriate $ORACLE_HOME 
(program= hsodbc) <= Agent Executable 
) 
) 

5) Before starting the listener make sure the ODBC lib directory is specified in the LD_LIBRARY_PATH environment variable. 
LD_LIBRARY_PATH=/u03/oracle/product/8.1.6/lib:/uo3/odbc/lib 

After the LD_LIBRARY_PATH has been modified, start the listener. 
Run "lsnrctl services" to verify that you now have a service handler for 
the hsmsql sid. 

=============================================================== 
LSNRCTL> services 
Connecting to (address=(protocol=tcp)(host=unixhost)(port=1521)) 
Services Summary... 
hsmsql has 1 service handler(s) 
DEDICATED SERVER established:0 refused:0 
LOCAL SERVER 
The command completed successfully 
=============================================================== 

6) Create the Initialization file. 
You must create and customize an initialization file for your generic connectivity agent. Oracle supplies a sample initialization file named "inithsodbc.ora" which is stored in the $ORACLE_HOME/hs/admin directory. 

To create an initialization file, copy the appropriate sample file and rename the file to initHS_SID.ora. In this example the sid noted in the listener and tnsnames is hsmsql so our new initialization file is called inithsmsql.ora. 

7) Make sure the following noted entries are in the inithsmsql.ora now located in $ORACLE_HOME/hs/admin 

INITMSQL.ORA 
# HS init parameters 
# 
HS_FDS_CONNECT_INFO = mssql <= odbc data_source_name 
HS_FDS_TRACE_LEVEL = 0 <= trace levels 0 - 4 (4 is verbose) 
HS_FDS_TRACE_FILE_NAME = hsmsql.trc <= trace file name 
HS_FDS_SHAREABLE_NAME = /uo3/odbc/lib/libodbc.so <= full path to odbc driver 
# 
# ODBC specific environment variables 
# 
set ODBCINI=/uo3/odbc/odbc.ini <= location of odbc.ini 
# 
# Environment variables required for the non-Oracle system 
# 
# set <envvar>=<value> 

8) Create a database link to access target database. Be sure to use the appropriate quotes as noted below. 
=============================================== 
SQL> create database link hsmsql 
1 connect to "user" identified by "password"
2 using 'hsodbc'; 
=============================================== 

9) To test, run a simple query of a known table on the target datastore. 
SQL> select * from employee@hsmsql; 

=========================================================================== 
Errors and Solutions Associated with the HS Gateway (UNIX) 
=========================================================================== 
/********************************************************************/ 
ORA-28509: unable to establish a connection to non-Oracle system 
ORA-02063: preceding line from HS 
/********************************************************************/ 
Cause: 
This indicates a problem with the Oracle configuration files. 
Action: 
Make sure the HOST parameter in the tnsnames.ora file is correct. 
Make sure the PORT number is correct. 
Make sure the SID name is correct in both the TNSNAMES.ORA and LISTENER.ORA 

/********************************************************************/ 
ORA-02068: following severe error from HS 
ORA-03114: not connected to ORACLE 
/********************************************************************/ 
Cause: 
This indicates the required syntax for the TNSNAMES.ORA file is not present. 
Action: (HS=OK) has to be added to the tnsnames.ora file in the DESCRIPTION section. 

/********************************************************************/ 
ORA-02068: following severe error from HS 
ORA-28511: lost RPC connection to heterogeneous remote agent using %tns_address% 
/********************************************************************/ 
Cause: 
The listener is unable to spawn the HS agent or the agent cannot find the ODBC lib directory. 
Action: 
The PROGRAM line in the listener.ora file is incorrect or not specified. Make sure LD_LIBRARY_PATH includes the $ODBC_HOME/lib directory. If not, set LD_LIBRARY_PATH and restart the listener. 

/********************************************************************/ 
ORA-28500: connection from ORACLE to a non-Oracle system returned this message: 
[Transparent gateway for ODBC][H001] The environment variable <HS_FDS_CONNECT_INFO> is not set. 
ORA-02063: preceding 2 lines from HS 
/********************************************************************/ 
Cause: 
Incorrect parameter settings in the HS init.ora file. 
Action: 
Set HS_FDS_CONNECT_INFO in the HS init.ora file to the data source name located in the odbc.ini file. 
Example: HS_FDS_CONNECT_INFO = MS_SQLServer7 

Make sure the HS init.ora file exists in the $ORACLE_HOME/hs/admin directory and has the same name as the SID in the LISTENER.ORA. Example: If SID=hsodbc in the listener.ora file, then the HS init.ora file would be named $ORACLE_HOME/hs/admin/inithsodbc.ora 

/********************************************************************/ 
ORA-28500: connection from ORACLE to a non-Oracle system returned this message: 
[Transparent gateway for ODBC][H001] The environment variable <HS_FDS_SHAREABLE_NAME> is not set. ORA-02063: preceding 2 lines from HS 
/********************************************************************/ 
Cause: 
Incorrect parameter settings in the HS init.ora file. 
Action: 
Set HS_FDS_SHAREABLE_NAME to the full path plus filename to the libodbc.so file. Example: HS_FDS_SHAREABLE_NAME=/u01/intersolv/odbc/ msql/lib/libodbc.so 

/********************************************************************/ 
ORA-28500: connection from ORACLE to a non-Oracle system returned this message: 
[Transparent gateway for ODBC]DRV_InitTdp: (SQL State: 01000; SQL Code: 0) 
ORA-02063: preceding 2 lines from HS 
/********************************************************************/ 
Cause: 
The HS agent cannot find the odbc.ini file. 
Action: 
The ODBCINI variable is not set in the HS init.ora file and needs to be set. Example: set ODBCINI=/u01/intersolv/odbc/msql/odbc.ini 

/********************************************************************/ 
ORA-00942: table or view does not exist 
[Transparent gateway for ODBC]DRV_OpenTable: [MERANT][ODBC SQL Server Driver][SQL Server]Invalid object name '%table%'. (SQL State: S0002; SQL Code: 208) 
ORA-02063: preceding 2 lines from HS 
/********************************************************************/ 
Cause: 
The data source in the odbc.ini file has incorrect database information. 
Action: 
Consult your odbc user guide on how to set the parameters for your datasource. 

/********************************************************************/ 
ORA-28500: connection from ORACLE to a non-Oracle system returned this message: 
[Transparent gateway for ODBC]DRV_InitTdp: [MERANT][ODBC SQL Server Driver][libssclient15]General network error. Check your network documentation. (SQL State: 08001; SQL Code: 11) 
ORA-02063: preceding 2 lines from HS 
/********************************************************************/ 
Cause: 
There is a problem at the network layer communicating with the foreign data 
source. 
Action: 
Make sure the destination host or ip address and port number are correct for the data source in the odbc.ini file. 

/********************************************************************/ 
ORA-28500: connection from ORACLE to a non-Oracle system returned this message: 
[Transparent gateway for ODBC]DRV_InitTdp: [MERANT][ODBC SQL Server Driver][SQL Server] Login failed (SQL State: 28000; SQL Code: 4002) 
ORA-02063: preceding 3 lines from HSTEST 
/********************************************************************/ 
Cause: 
The Oracle database link created for the foreign datasource has either no credentials or incorrect credentials. 
Action: 
Recreate the Oracle database link with the propper username and password. Also, username and password must be in double quotes. Example: create database link ODBC connect to "sa" identified by "pencil" 
using 'hsodbc'; 

REFERENCES 
---------- 
Oracle8i Distributed Database Systems Guide 

PURPOSE 
----------------------------------------------------------------- 
This is a quick step instruction how to setup generic connectivity on SUN Solaris. 

SCOPE & APPLICATION 
------------------- 
This note describes how to set up HSODBC (generic connectivity) for SUN using Oracle 9.2 release. It is similar for 9.0.1 and 8i releases. 

How to Setup HSODBC (generic connectivity) on SUN 
------------------------------------------------- 
This note is divided into two different parts: Part I describes how to set up the ODBC driver Part II describes the configuration process of HSODBC 

Part I: Setting up the ODBC driver 
---------------------------------- 
There are several ODBC vendors for UNIX platforms. This note describes the settings of the ODBC driver from Data Direct Technologies. They also provide a 15 day trial license for SUN platforms. More information is available at the following URL: http://www.datadirect-technologies.com 

The libraries of other ODBC vendors will vary; so please make sure to change the libraries to the libraries of your odbc vendor. 

As mentioned, Data Direct Technologies provide a trial version. If you download this trial version, please make sure that you download also the service pack for the odbc driver -if it exists-. (Side Note: Data Direct ODBC driver V4.1 MUST HAVE the latest service pack; the base release reports ORA-28511 and the trace file ends 
with hoalgon while used with Oracle 9.2.0.4.0; V817 reports a missing symbol:__1cG__CrunSregister_exit_code6FpG_v_v_ .) 

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. /u05/odbc) 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 it. 

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 the server is the configured odbc.ini. 
It is divided into 3 different sections: 
[ODBC Data Sources] 
[<DSN>] 
[ODBC] 

[ODBC Data Source] is the section that contains all the available DSNs. 
[<DSN>] 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 looks like: 
[ODBC Data Sources] 
mssql=MS SQL Server 

[mssql] 
Driver=/odbc.42/lib/ivmsss19.so 
Description=DataDirect 4.20 SQL Server Wire Protocol 
Database=<SQL Server Database> 
LogonID=<UID like:sa> 
Password=<password for the user> 
Address=<hostname of the SQL Server>,<port; default is 1433> 
QuotedId=No 
AnsiNPW=No 

[ODBC] 
IANAAppCodePage=4 
InstallDir=/odbc.42 
Trace=0 
TraceDll=/odbc.42/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 server to contact, the driver that should be used while connecting to the remote server and the user id and password of the remote 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 out 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 on the foreign database a demo table called EMP. 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 must contain the odbc library path 
export LD_LIBRARY_PATH=$ODBC_HOME/lib:$LD_LIBRARY_PATH 
b) ODBCINI 
export ODBCINI=$ODBC_HOME/odbc.ini 
The ODBCINI parameter will guarantee, that 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 <user of the MS SQL Server> -pwd <appropriated password> <DSN> 
like demoodbc -uid sa -pwd sa mssql should connect to the server and query the EMP table. 

Please make sure that 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. 

Part II: How to configure HSODBC 
--------------------------------- 
(This section assumes that everything is done in the Oracle user account that starts the listener!) 

In general the following things must be configured: 
1) listener 
2) tnsnames 
3) init<SID>.ora of the hs subsystem 
4) environment 
5) Oracle database 

1) The listener needs a new SID entry like the following: 
(SID_DESC = 
(SID_NAME = hsodbc) 
(ORACLE_HOME = /oracle/product/64bit/9.2.0.1) 
(PROGRAM = hsodbc) 
(ENVS=LD_LIBRARY_PATH=/oracle/product/64bit/9.2.0.1/lib:/odbc.42/lib) 
) 

Please correct the ORACLE_HOME entry and the ENVS entry according to your 
installation. If the odbc driver requires the foreign data store client libraries (like the Progress ODBC driver), the LD_LIBRARY_PATH must contain this library path as well: 
... 
(ENVS=LD_LIBRARY_PATH=/oracle/product/64bit/9.2.0.1/lib:/odbc.42/lib: /progress/dcl/lib) 
... 
ORACLE_HOME must point to your ORACLE_HOME directory and the ENVS string contains entries for the LD_LIBRARY_PATH. The minimum of the LD_LIBRARY_PATH setting must contain the Oracle library 
and the odbc library path; both 32 bit. HSODBC is a 32 bit libray and thus it needs a 32 bit ODBC driver. 
ATTENTION: Due to a staging problem with Oracle 9iR2 this hsodbc configuration must have the ORACLE_HOME/lib directory instead of the lib32 directory in the ENVS path setting; the lib32 directory does not contain ALL required libraries! 

With Oracle 10g all libraries are placed in the lib32 directory again. A correct setting of the path can be verified by typing hsodbc then pressing <ENTER> at the SUN console. If the LD_LIBRARY_PATH contains the correct libraries, the version number of HSODBC should be displayed. 

So a listener.ora file can look like: 
SID_LIST_LISTENER920 = 
(SID_LIST = 
(SID_DESC = 
(SID_NAME = hsodbc) 
(ORACLE_HOME = /oracle/product/64bit/9.2.0.1) 
(PROGRAM = hsodbc) 
(ENVS=LD_LIBRARY_PATH=/oracle/product/64bit/9.2.0.1/lib:/odbc.42/lib) 
) 
) 

LISTENER920 = 
(DESCRIPTION_LIST = 
(DESCRIPTION = 
(ADDRESS_LIST = 
(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname of the Oracle Server>) 
(PORT = 1921)) 
) 
(ADDRESS_LIST = 
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) 
) 
) 
) 

The listener.ora file contains a listener called LISTENER920 instead of the default name LISTENER. 
To stop/start the listener from above, don't forget to set the current_listener to listener920. 

The listener must be restarted after changing the listener.ora! 

2) The tnsnames.ora needs an entry for the HSODBC alias: 
HSODBC.DE.ORACLE.COM = 
(DESCRIPTION = 
(ADDRESS_LIST = 
(ADDRESS = (PROTOCOL = tcp)(HOST = <hostname of the Oracle Server)(PORT = 1521)) 
) 
(CONNECT_DATA = 
(SID = hsodbc) 
) 
(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 

But the important entry is the (HS=)or (HS=OK) key word. This key word must be added manually and opening the Net Configuration Assistants will remove this entries from your tnsnames.ora file! The (HS=OK) parameter must be outside the SID section and specifies that this connector uses the Oracle Heterogeneous Service Option. After adding the tnsnames alias and restarting the listener, a connectivity check is to use tnsping <alias>. tnsping hsodbc should come back with a successfull message. 

3) init.ora of the gateway: 
The SID to use HS functionality is called in this example hsodbc. There are some restrictions 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 init.ora file of the gateway. The name of the file is init<SID>.ora. In this example it is called inithsodbc.ora. The file is located at $ORACLE_HOME/hs/admin. It should contain the following entries: 
# 
# HS init parameters 
# 
HS_FDS_CONNECT_INFO = mssql 
HS_FDS_TRACE_LEVEL = off 
HS_FDS_SHAREABLE_NAME = /odbc.42/lib/libodbc.so 
# 
# ODBC specific environment variables 
# 
set ODBCINI=/odbc.42/odbc.ini 
# 
# Environment variables required for the non-Oracle system 
# 
#set <envvar>=<value> 


Short explanation of the parameters: HS_FDS_CONNECT_INFO points to the ODBC DSN configured in PART I of this note. HS_FDS_SHAREABLE_NAME points to the ODBC Driver Manager library at $ODBC_HOME/lib/<ODBC_Driver_MANAGER_LIB>. For the Data Direct Technologies odbc driver the generic odbc library on SUN 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; then the ODBC driver library itself can be specified here. To determine if an ODBC Driver Manager is required, please contact the ODBC driver vendor. 

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

4) Configuring the environment: Normally there is nothing to configure anymore. But 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. (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'. 

5)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 <name> 
connect to <UID> identified by <pwd> using '<tnsalias>'; 

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 'hsodbc'; 

The db link name is sqlserver. Username and password must be in double quotes, because the username and password are case sensitive. 'hsodbc' points to the alias in the tnsnames.ora file that calls the HS subsystem. 

If everything is configured well, a select of the EMP table -created for the demoodbc program- should be successful: select * from "EMP"@sqlserver; 
... 

(Side note: The EMP table at the MS SQL Server is in capital letters. Because the MS SQL Server is case sensitive the EMP table must be surrounded by double quotes). @sqlserver points to the name of the database link to the MS SQL Server.