| | |||||
| Modified 23-JUN-2008 Type FAQ Status PUBLISHED | |||||
Checked for relevance on 17-Oct-2007
PURPOSE
-------
Starting with database release 10g Oracle provides generic connectivity for
Linux.
PLEASE PAY ATTENTION: CURRENTLY (UP TO 10gR2) HSODBC IS ONLY PORTED TO
LINUX X86; NOT TO Linux X86-64bit.
SCOPE & APPLICATION
-------------------
Below is a description of setting up HSODBC (generic connectivity) for
Linux using Oracle 10g release.
How to Setup HSODBC (generic connectivity) on LINUX
---------------------------------------------------
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.
Below a description with 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.
As mentioned, Data Direct Technologies provides 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-.
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 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 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 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=<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=/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 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 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 = /home/oracle/server/10.2.0/)
(PROGRAM = hsodbc)
(ENVS=LD_LIBRARY_PATH=/home/oracle/server/10.2.0/lib:/home/odbc/dd/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=/home/oracle/server/10.2.0/lib:/home/odbc/dd/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.
A correct setting of the path can be verified by typing
hsodbc
then pressing <ENTER> at the 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_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = hsodbc)
(ORACLE_HOME = /home/oracle/server/10.2.0/)
(PROGRAM = hsodbc)
(ENVS=LD_LIBRARY_PATH=/home/oracle/server/10.2.0/lib:/home/odbc/dd/lib)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname of the Oracle Server>)
(PORT = 1921))
)
)
)
The listener must be restarted (use stop and start) after changing the
listener.ora file!
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 = /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 <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 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; 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.
(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 <library name> |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 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'.
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
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.
RELATED DOCUMENTS
-----------------
The relevant manuals are :-
Heterogeneous Connectivity Administrator’s Guide 10g Release 2 (10.2)
Note:261726.1 - Generic Connectivity Available on Linux with 10g
Products
Keywords
| ||
Related
0 ความคิดเห็น:
แสดงความคิดเห็น