| | |||||
| Modified 28-JUL-2008 Type BULLETIN Status ARCHIVED | |||||
PURPOSE
-----------------------------------------------------------------
PLEASE BE AWARE THAT THERE IS NO PORTING OF GENERIC CONNECTIVITY
FOR HP-UX ITANIUM.
This note describes only setting up HSODBC on HP-UX RISC platforms.
SCOPE & APPLICATION
-------------------
This note describes how to set up HSODBC (heterogeneous services) for
HP-UX 11.0 using Oracle 9.2 release. It is similar for 9.1 release, but
the directory structure changed between 8i and 9i, so while using this note
for 8i, please keep in mind to change the directories of the libraries.
How to Setup HSODBC (heterogeneous services) on HP-UX
-----------------------------------------------------
This note is divided into five different parts:
Part I describes how to set up the ODBC driver
Part II describes how to relink the HSODBC executable
Part III describes the configuration process of HSODBC
Part IV is a summary of how to set it up for Oracle 8i on HP-UX
Part V Comments
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 HP-UX platforms. More information is available at
the following URL:
http://www.datadirect-technologies.com
HSODBC is a 32 bit libray and thus it needs a 32 bit ODBC driver.
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-.
Another feature of this driver is, that it also provides 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.
[ODBC] is the general section for the odbc driver
A very simple file of the odbc.ini file looks like:
[ODBC Data Sources]
mssql=DataDirect 4.10 SQL Server Wire Protocol
[mssql]
Driver=/u05/odbc/lib/ivmsss18.sl
Database=PCS
LogonID=<UID like:sa>
Password=<password for the user>
Address=<hostname of the SQL Server>,1433
QuotedId=No
AnsiNPW=No
[ODBC]
Trace=0
TraceFile=/tmp/odbc.trc
TraceDll=/u05/odbc/lib/odbctrac.sl
InstallDir=/u05/odbc
ConversionTableLocation=/u05/odbc/tables
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) SHLIB_PATH must contain the odbc library path
export SHLIB_PATH=$ODBC_HOME/lib:$SHLIB_PATH
b) ODBCINI
export ODBCINI=$ODBC_HOME/odbc.ini
The odbc driver is a 32 bit program and thus the library must be added to the
SHLIB_PATH. 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 relink the HSODBC executable
-------------------------------------------
(Everything done in this section is done as ORACLE user)
Most ODBC drivers are linked with a C++ compiler, but Oracle is still
compiled with a C compiler.
the HP homepage conatisn an article
Mixing C++ with other Languages for information on linking HP aC++ modules
with HP C
that describes in detail why the following steps are required. Please refer to
it if you need more background information.
The procedure for Oracle 9i with aC++ runtime version below A.03.30 is
Check out, if the following file exists:
/opt/aCC/lib/cpprt0_stub.o
If not, please follow this instruction to create it:
Create a file called cpprt0_stub.s with the following contents:
--- cut here ---
.code
; stubs for static constructors in a.out
.export __StaticCtorTable_Start,data
.export __StaticCtorTable_End,data
__StaticCtorTable_Start
__StaticCtorTable_End
.data
; stubs for static constructors in a.out, compiled with +z/+Z
.export __ZStaticCtorTable_Start,data
.export __ZStaticCtorTable_End,data
__ZStaticCtorTable_Start
__ZStaticCtorTable_End
--- cut here ---
(If you created the file on a PC, don't forget to transfer
the file in ASCII mode!)
Then compile it with the following command:
as cpprt0_stub.s -o ./cpprt0_stub.o
Now back to the relinking issue:
First rename the files hsodbc and hsodbc0 in the directory
$ORACLE_HOME/bin. Then change to the directory
$ORACLE_HOME/rdbms/lib and copy the file cpprt0_stub.o to
this directory.
According to HP this procedure is not longer necessary for newer aCC
versions.
A description for Oracle 8i can be found in Note:150458.1
Relinking Heterogeneous Service on HP-UX.
Then the environment variable HSA_ODBC_SYSLIBS must be set:
export HSA_ODBC_SYSLIBS="cpprt0_stub.o -lstd -lstream -lCsup -lcl -ldld"
and then the relink of the hsodbc library is done as follows:
/usr/ccs/bin/make -f ins_rdbms.mk ihsodbc
The linking should proceed without any error and create the hsodbc file in
the $ORACLE_HOME/bin directory.
To make sure, that the linking worked, simply type hsodbc and press enter.
For Oracle 9.2 hsodbc executable a similar screen to the following will
appear:
ora920 @ hsodbc
Oracle Corporation --- TUESDAY OCT 15 2002 09:31:32.746
Heterogeneous Agent Release 9.2.0.1.0 - Production Built with
Driver for ODBC
This will indicate, that the relinking was successfully.
Please don't proceed with the next part, if this part was unsuccessful !
Part III: 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 = /u01/app/oracle/product/9.2.0)
(PROGRAM = hsodbc)
(ENVS=SHLIB_PATH=/u05/odbc/lib:/u01/app/oracle/product/9.2.0/lib32)
)
Please correct the ORACLE_HOME entry and the ENVS entry.
ORACLE_HOME must point to your ORACLE_HOME directory and the ENVS
string contains entries for the SHLIB_PATH.
The minimum of the SHLIB_PATH setting must contain the Oracle library and
the odbc library path. If the odbc driver requires the foreign data store
client libraries (like the Progress ODBC driver), the SHLIP_PATH must contain
this library path as well:
...
(ENVS=SHLIB_PATH=/u05/odbc/lib:
/u01/app/oracle/product/9.2.0/lib32:/progress/dcl/lib)
...
(Differing from 8i releases, the 32 bit Oracle libraries are now located in the
lib32 directory while for 8i the 32 bit library was located in the lib
directory. So please pay attention to this while using older configuration
files. More details can be found in Note: <109621.1>).
So a listener.ora file can look like:
SID_LIST_LISTENER920 =
(SID_LIST =
(SID_DESC =
(SID_NAME = hsodbc)
(ORACLE_HOME = /u01/app/oracle/product/9.2.0)
(PROGRAM = hsodbc)
(ENVS=SHLIB_PATH=/u05/odbc/lib:/u01/app/oracle/product/9.2.0/lib32)
)
)
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 = 1921))
)
(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=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=/u05/odbc/lib/libodbc.sl
#
# ODBC specific environment variables
#
set ODBCINI=/u05/odbc/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 is
called libodbc.sl. 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=/u05/odbc/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 SHLIB_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.
Part IV How to set it up for Oracle 8i on HP-UX
-----------------------------------------------
Because ODBC is independent from the Oracle version being used,
Part I of this note (setting up odbc driver) is the same for
8i and 9i Oracle database releases.
Part III is almost the same:
The description of the tnsnames.ora and init.ora of the HS subsystem is also
valid for 8i. Also the create database statement...
Only the listener.ora differs a little bit in the ENVS setting:
In release 9i the 32 bit libraries are stored at:
$ORACLE_HOME/lib32
in 8i the 32 bit libraries are stored in:
$ORACLE_HOME/lib
!! lib instead of lib32 !!
and the Oracle_Home is the home directory of 8i release.
So the SID section looks like:
(SID_DESC =
(SID_NAME = hsodbc)
(ORACLE_HOME = /u01/app/oracle/product/8.1.7)
(PROGRAM = hsodbc)
(ENVS=SHLIB_PATH=/u05/odbc/lib:/u01/app/oracle/product/8.1.7/lib)
)
The conclusion about the directory difference is, that in 8i the environment
SHLIB_PATH misses the 32 appendix:
(export SHLIB_PATH=$ODBC_HOME/lib:$SHLIB_PATH
echo $SHLIB_PATH
SHLIB_PATH=/u05/odbc/lib:/u01/app/oracle/product/8.1.7/lib)
Relinking as described in Part II is completely different:
Check out for the /opt/aCC/lib/cpprt0_stub.o file again.
If it does not exist, create it as described in Part II above.
Copy this file to $ORACLE_HOME/rdbms/lib directory.
Change to $ORACLE_HOME/rdbms/lib directory.
To relink the HS executable use the following command.
Copy/paste the command into a script on the HP-UX machine, make
it executable (chmod +x) and run it.
--- cut here ---
cc -Wl,+s -Wl,+n \
-o $ORACLE_HOME/rdbms/lib/hsodbc \
-L $ORACLE_HOME/rdbms/lib \
-L $ORACLE_HOME/lib \
-o $ORACLE_HOME/rdbms/lib/hsodbc \
$ORACLE_HOME/hs/lib/hsodbc.o \
$ORACLE_HOME/rdbms/lib/defopt.o \
$ORACLE_HOME/rdbms/lib/homts.o \
$ORACLE_HOME/rdbms/lib/ssdbaed.o \
$ORACLE_HOME/rdbms/lib/cpprt0_stub.o \
-L $ORACLE_HOME/hs/lib/ \
-lnavhoa \
-lnavshr \
-lnvbaseshr \
-lagtsh \
-lpls8 \
-lplp8 \
-lclntsh \
-lnls8 \
-lcore8 \
-lnls8 \
-lcore8 \
-lnls8 \
`cat $ORACLE_HOME/lib/sysliblist` \
-lm \
-lstd -lstream -lCsup -lcl -ldld
--- cut here ---
The new hsodbc executable is built in the $ORACLE_HOME/rdbms/lib directory.
Rename the old hsodbc library $ORACLE_HOME/bin/hsodbc and then move
the new hsodbc to the $ORACLE_HOME/bin location.
Test again while entering hsodbc and pressing enter key.
A message like the following should appear:
ora817 @ /u01/app/oracle/product/8.1.7/rdbms/lib hsodbc
Oracle Corporation --- TUESDAY OCT 15 2002 16:35:03.439
Heterogeneous Agent based on the following module(s):
- External Procedure Module
- Transaction Module
- SQL/PLSQL Module
A select in SQL*Plus via the db link should be possible now.
Part V: Comments
----------------
Please be aware, that after each patch set install, the RELINK process must
be executed again manually.
Repeat the steps described in Part II after a successfull patch installation.
Else the following error occures:
SQL> select * from all_catalog@hsodbc;
select * from all_catalog@hsodbc
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC]Bad file number; at FIND_IMAGE_SYMBOL
[C079] Failed to load dynamic library '/u05/odbc/lib/libodbc.sl'
ORA-02063: preceding 3 lines from HSODBC
RELATED DOCUMENTS
-----------------
NOTE:150458.1, Note:109621.1
Products
Keywords
| ||
Related
0 ความคิดเห็น:
แสดงความคิดเห็น