Migrating from Oracle’s Traditional Generic Connectivity with ODBC to Database Gateway for ODBC

Migrating from Oracle’s Traditional Generic Connectivity with ODBC to Database Gateway for ODBC

November 10, 2009 0 Comments

We frequently encounter customers who are using SQL Server, Teradata, DB2, Sybase and other databases, who want to integrate these data sources with their Oracle environments. Generic Connectivity, also referred to as Heterogeneous Services or OHS, enables users to integrate data that may exist on several different databases.

In this post, we will focus on the challenges of moving from Generic Connectivity to Database Gateway for ODBC – and how to overcome them. Generic Connectivity (also commonly referred to as Heterogeneous Services or OHS), is the application that shipped with Oracle v9 and v10 databases. Generic Connectivity is used to connect Oracle v9 and v10 databases to non-Oracle databases.


Oracle has dropped support for Generic Connectivity, and now encourages all database users to move to Database Gateway for ODBC, which is shipped with Oracle 11g. There are advantages to moving to Database Gateway for ODBC. It is a stand-alone product – no longer required to reside on the same server as the Oracle database.


However, many Oracle users are not yet ready to move from their version 9 or version 10 databases. So, if you need connectivity to a third party database from Oracle, you want to remain in a supported environment, and you are not ready to upgrade to 11g – what can you do?

Oracle released a patch which enables Oracle 9 and 10 databases to use Database Gateway for ODBC to connect to a non-Oracle database via ODBC. If you are using Oracle v9, or Oracle v10 prior to, the patch is required to use Database Gateway for ODBC. Once you apply the patch, you can download and install Database Gateway for ODBC. Oracle Metalink article 549796.1 (MetaLink account required) lists the transparent gateways which are no longer supported. Oracle Metalink article 457808.1 (MetaLink account required) states that the patch required for using Database Gateway for ODBC with pre-11g Oracle databases is 5965763 – and provides a link to download the patch. The Database Gateway for ODBC application can be downloaded from the Oracle website: http://www.oracle.com/technology/software/products/database/index.html

Once you have applied the appropriate database patch and installed Database Gateway for ODBC, setting up Database Gateway for ODBC with DataDirect Connect for ODBC can be done in a few easy steps: http://www.datadirect.com/developer/odbc/oracle_heterogeneous/index.ssp

In order to connect to a non-Oracle database, a tnsnames.ora entry must indicate that you are using DG4ODBC, as well as identifying the Oracle instance you are going to use. You will have a corresponding listener.ora entry that also identifies the Oracle instance you are using. Once you have modified the listener.ora and tnsnames.ora, create an initialization file which is named after the Oracle instance: init<SID>.ora. The initialization file will be used to point to the ODBC driver data source and set the ODBCINI environment variable. Once you have set up the Oracle files and the DataDirect Connect for ODBC driver, you will create a database link. Once your database link is created, you can execute SQL against your non-Oracle database: select * from DB2table@DB2DatabaseLink. When issuing your SQL statement against the database link, data from the non-Oracle database is returned transparently.


Paul Griffin

View all posts from Paul Griffin on the Progress blog. Connect with us about all things application development and deployment, data integration and digital business.

Read next Extend Your SQL with User-Defined Functions in OpenEdge 11.7
Comments are disabled in preview mode.