"People
are like stained-glass windows. They sparkle and shine when
the sun is out, but when the darkness sets in,
their true beauty is revealed only if there is light from
within."
-Elisabeth
Kübler-Ross (1926 -)
|
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.
|