Using DataDirect Connect® for ODBC with Oracle Heterogeneous Services
Introduction
Corporate developers frequently need to integrate and consolidate data residing in diverse database systems. Oracle users make use of Oracle Heterogeneous Services to integrate non-Oracle data into their primary database. Using the Generic Connectivity agent, Oracle users can communicate to another database or data store, such as XML, via ODBC.
DataDirect Connect for ODBC provides the most reliable and highest performing data access for users of Oracle Heterogeneous Services. Connect for ODBC does not require the installation and configuration of database client libraries. This dramatically improves performance and scalability and simplifies configuration of Oracle Generic Connectivity, while providing access to a larger number of various data sources.
This paper explains how to use DataDirect Connect for ODBC with Oracle Heterogeneous Services.
DataDirect Connect for ODBC in an Oracle Heterogeneous Services Environment
Generic Connectivity Architecture
Generic Connectivity is implemented by using a Heterogeneous Services ODBC agent. An ODBC agent is included as part of your Oracle 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 ODBC driver that you use must be on the same platform as the ODBC agent. The non-Oracle data stores can reside on the same machine as the Oracle database or a different machine.
Installation Steps
This example shows the configuration of Generic Connectivity on a SUN Solaris system using DataDirect's ODBC driver to connect to Microsoft SQL Server. Please check your Oracle documentation for specific version and platform support.
1) Install the data dictionary tables and views for Heterogeneous Services.
Using the server manager or sqlplus logged on as sys, run caths.sql. For example using the server manager you can use the following example:
SQL> connect internal SQL> @<ORACLE_HOME>/rdbms/admin/caths.sql;
This script is located in $ORACLE_HOME/rdbms/admin
2) Install the DataDirect Connect for ODBC Driver.
Some non-Oracle data stores will require that particular database's client library components to be installed. If the database is DB2, Sybase, SQL Server or Informix, you should use the Connect for ODBC Wire Protocol driver for the particular database you are trying to access. These drivers do not require any additional components to be installed to connect to the database.
3) Configure your odbc data source in the .odbc.ini file. The example below is a data source to connect to Microsoft SQL Server 2000
Note: Annotations to following examples file begin with the
symbol and
should not be included in the actual file.
[MS_SQLServer2000]
4) Make sure the following entries are in the tnsnames.ora and listener.ora files.
TNSNAMES.ORA
hsalias=
(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=/db/oracle/product/ora92_64) Appropriate $ORACLE_HOME
(program= hsodbc) Agent Executable
)
)
5) Your environment might not need to set LD_LIBRARY_PATH, but you might need to add (ENVS=LD_LIBRARY_PATH=/opt/odbc/lib) to the listener.ora file. For example:
LD_LIBRARY_PATH=/db/oracle/product/8.1.6/bin:/opt/odbc/lib/
After the LD_LIBRARY_PATH has been modified, start the listener.
(SID_DESC =
(ORACLE_HOME = /db/oracle/product/ora92_64)
(SID_NAME = hsmsql)
(PROGRAM = hsodbc)
(ENVS=LD_LIBRARY_PATH==/opt/odbc/lib)
)
6) Run "lsnrctl services" to verify that you now have a service handler for the hsmsql sid.
LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXPPROC)))
Services Summary...
Service "hsmsql" has 1 instance(s).
Instance "hsmsql", status UNKNOWN, has 1 handler(s) for this service…
Handler(s):
"DEDICATED" established:1 refused:0
LOCAL SERVER
The command completed successfully
7) 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 files is "hsmsql" so the new initialization file is called inithsmsql.ora.
Note that the SID name and the initialization file name are case sensitive.
8) Make sure the following entries are in the inithsmsql.ora now located in $ORACLE_HOME/hs/admin
INITHSMSQL.ORA
# HS init parameters # HS_FDS_CONNECT_INFO = MS_SQLServer2000
9) Create a database link to access target database. Be sure to use the appropriate quotes as shown in the following example:
SQL> create database link hsmsql SQL> connect to "user" identified by "password" < = valid user/pwd on target DB SQL> using 'hsalias';
10) To test, run a simple query of a known table on the target datastore.
SQL> select * from employee@hsmsql;
| empid | firstname | lastname | department | job |
| ---------- | --------------- | --------------- | ---------- | --- |
| 10000 | Joseph | Johnston | Sales | CDW |
| 10001 | John | Ladd | Sales | WNV |
| 10002 | Ronald | Wall | Relations | NPI |
| 10003 | Julie | Reynolds | Relations | NPO |
| 10004 | Bill | Baird | Telemarket | PHN |
| 10005 | Jason | Linde | Sales | WND |
| 10006 | Edward | Lufner | Telemarket | CDG |
| 10007 | Mike | Seibt | Networking | IDW |
8 rows selected.
Common Errors and Solutions Associated with Heterogeneous Services and Generic Connectivity
The following list contains some of the most common errors associated with setting up Heterogeneous Services and Generic Connectivity.
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 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: Add (HS=OK) in the description section of the tnsnames.ora file.
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_SQLServer Wire Protocol 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=/opt/odbc/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: Set the ODBCINI variable in the HS init.ora file.
Example: set ODBCINI=/opt/odbc/odbc.ini
ORA-00942: table or view does not exist [Transparent gateway for ODBC]DRV_OpenTable:
[DATADIRECT][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 the DataDirect Connect for ODBC Reference Guide for information on setting parameters for your datasource.
ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Transparent
gateway for ODBC]DRV_InitTdp: [DATADIRECT][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: [DATADIRECT][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 proper username and password. Note that the username and password must be in double quotes.
Example:
create database link ODBC connect to "sa" identified by "pencil" using 'hsodbc'.
Summary
Heterogeneous Services and Generic Connectivity provide Oracle customers the ability to access and integrate non-Oracle data sources, providing a wide degree of flexibility in a multi-database environment. Companies who wish to use Generic Connectivity to consolidate and integrate data with Oracle require optimal connectivity to ensure the best performance. DataDirect Connect for ODBC delivers the most scalable and best performing connectivity available for Oracle Heterogeneous Services.
