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.

This article explains how to use DataDirect for ODBC and the DataDirect OpenAccess™ ODBC driver with Oracle Heterogeneous Services.

Back to top

Generic ODBC 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 Oracle 10g and hsodbc; DataDirect's 5.3 ODBC driver to connect to Microsoft SQL Server; and DataDirect's OpenAccess 6.0 ODBC driver to connect to a test database. Please check your Oracle documentation for specific version and platform support.

Note: Starting with Oracle 11g, the HS executable name is now called DG4ODBC. If you’re using a 64-bit version of Oracle you must use a 64-bit ODBC driver. If you’re using a 32-bit version of Oracle, you must use a 32-bit ODBC driver. Please refer to DataDirect KB doc#2466288PG for the supportability matrix.

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.

6) Run "lsnrctl services" to verify that you now have a service handler for the hsMydb sid.

LSNRCTL> services

Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXPPROC)))

Services Summary...

Service "hsMydb"has 1 instance(s).

Instance "hsMydb", status UNKNOWN, has 1 handler(s) forthis service…

Handler(s):

"DEDICATED"established:1 refused:0

LOCALSERVER

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 inithsMydb.ora now located in $ORACLE_HOME/hs/admin

9) Create a database link to access target database. Be sure to use the appropriate quotes as shown in the following example:

SQL> createdatabaselink hsdb Link namecan be anything you want

SQL> connectto"user"identified by"password" Must be a valid user/pwd

ontarget DB

SQL> using 'hsalias'; NamefromTnsnames.ora

10) To test, run a simple query of a known table on the target datastore.

SQL> select* fromemployee@hsdb;

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.

Back to top

Common ODBC Errors

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 HSCause: 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 ORACLECause: 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 HSCause: 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 HSCause: 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 HSCause: 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-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 HSCause: 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 HSTESTCause: 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:

Progress, Telerik, and certain product names used herein are trademarks or registered trademarks of Progress Software Corporation and/or one of its subsidiaries or affiliates in the U.S. and/or other countries. See Trademarks for appropriate markings.