Parts of this article were extracted from Doc ID Note:214834.1 from Oracle's Metalink website (http://metalink.oracle.com)
Setup of HSODBC on HP-UX 11.0 differs from other UNIX platforms.
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. If using this note for 8i, please keep in mind that the library directory structure has changed.
This article is divided into four different parts:
- Part I describes how to relink the HSODBC executable (if necessary)
- Part II describes the configuration process of HSODBC
- Part III is a summary of how to set HSODBC up for Oracle 8i on HP-UX
(Everything done in this section is done as the ORACLE user.)
While most ODBC drivers are linked with a C++ compiler, Oracle is linked with a C compiler. This can cause issues on HP-UX machines. The hsodbc library must be relinked with C++ compiler. This document contains relinking instructions for release 9i. A description for an Oracle 8i relink can be found in Note:150458.1 Relinking Heterogeneous Serviceon HP-UX):
/opt/aCC/lib/cpprt0_stub.o
.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
as cpprt0_stub.s -o ./cpprt0_stub.o
export HSA_ODBC_SYSLIBS="cpprt0_stub.o -lstd -lstream -lCsup -lcl -ldld"
/usr/ccs/bin/make -f ins_rdbms.mk ihsodbc
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
(Complete all steps in this section using the Oracle user account that starts the listener!)
The following elements must be configured:
(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") )
Set the ORACLE_HOME entry and the ENVS entry accordingly.
ORACLE_HOME must point to your ORACLE_HOME directory and the ENVS string contains entries for the SHLIB_PATH.
The SHLIB_PATH setting must contain the Oracle and ODBC library paths, at a minimum.
Note: 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.
Here is a sample listener.ora file:
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)) ) ) )
This listener.ora file contains a listener called LISTENER920 instead of the default name LISTENER.
To stop/start the listener from above, the current_listener must be set to listener920.
The listener must be restarted after changing the listener.ora!
hsodbc.DE.ORACLE.COM = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname of the Oracle Server>) (PORT = 1921) ) ) (CONNECT_DATA = (SID= hsodbc) ) (HS=) )
Your TNS alias domain will likely differ from the one used above (de.oracle.com), depending on the parameter in the sqlnet.ora:NAMES.DEFAULT_DOMAIN = de.oracle.com
The most important entry is the (HS=) keyword. This keyword must be added manually, and opening the Net Configuration Assistants will remove this entry from your tnsnames.ora file! The (HS=) parameter must be outside the SID section and specifies that this connector uses the Oracle Heterogeneous Service Option.
Restart the listener after adding the TNS alias.
Then, use tnsping <alias> to perform a connectivity check.
tnsping hsodbc
tnsping should come back with a successful message.
The SID is also relevant for the init.ora file.
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 = <OpenLink home directory>/lib/libiodbc.so # # ODBC specific environment variables # set ODBCINI=/dbs/openlink/32bit/v42/bin/odbc.ini set ODBCINSTINI=/dbs/openlink/32bit/v42/bin/odbcinst.ini # # *** The following variables are extra requirements of the OpenLink ODBC drivers # *** set OPENLINKINI=[OpenLink home directory]/bin/openlink.ini set PATH=$PATH:[OpenLink home directory]/bin set SHLIB_PATH=$SHLIB_PATH:[OpenLink home directory]/lib
Short explanation of the parameters:
You need to create a database link.
To proceed, 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>';
Using values found in our previous example files, the following syntax would be used to connect to the Microsoft SQL Server:
CREATE DATABASE LINK sqlserver CONNECT TO "sa" IDENTIFIED BY "sa" USING 'hsodbc';
The database link name is "sqlserver".
Username and password must be in double quotes, because they are case sensitive.
'hsodbc' points to the TNS alias in the tnsnames.ora file that calls the HS subsystem.
If everything is configured properly, a select of the EMP table (created for the demoodbc program) should be successful:
select * from "EMP"@sqlserver;
Note: The EMP table is capitalized in the Microsoft SQL Server database.
Because Microsoft SQL Server is case sensitive, the EMP table must be surrounded by double quotes in the context of the query.@sqlserver points to the name of the database link to the Microsoft SQL Server.
ODBC is independent from the Oracle version being used.
Therefore, Part I of this document is the same for 8i and 9i Oracle database releases.
Relinking as described in Part II varies substantially. These are the steps that need to be followed:
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
ora817 @ /u01/app/oracle/product/8.1.7/rdbms/lib hsodbc
The description of the tnsnames.ora and init.ora of the HS subsystem is also valid for 8i. The CREATE DATABASE LINK statement is also the same. Only the listener.ora file differs slightly in regards to 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
The Oracle_Home is the home directory of 8i release.
Therefore, 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) )