Oracle Heterogeneous Services

Oracle offers two solutions to access any number of non-Oracle systems from an Oracle environment in a heterogeneously distributed environment. They are

1. Oracle Generic Connectivity
2. Oracle Transparent Gateways.

Heterogeneous Connectivity Process - The Oracle Database server accesses the non-Oracle Database using Oracle Heterogeneous Services in conjunction with an agent. Heterogeneous Services (HS) is an integrated component within the Oracle Database server and the enabling technology for the current suite of Oracle Transparent Gateway products.

Oracle Generic Connectivity is a generic solution for any ODBC or OLEDB compliant non-Oracle system. It enables connectivity using industry standards such as ODBC and OLEDB driver. Using Generic connectivity it possible to access low-end data stores such as Access, dBase, FoxPro and non-relational targets like Excel. Generic connectivity is provided free of charge with the database server, but it is and limited in functionality and slower compared to Oracle's Gateways.

If Oracle database running on windows, setup the HS ODBC/HS OLEDB agent on the same server to access non-oracle systems. If Oracle database running on UNIX, you must install Oracle server on the windows where non-oracle database is running and setup the HS ODBC/HS OLEDB agent on the same server. If you don’t want to install Oracle server on non-oracle system then you can Install Oracle server on middle tier windows system or system that supports the ODBC or OLE DB protocols and setup the HS agent.

See the below links to setup Oracle Generic connectivity.
Oracle Generic connectivity setup in Oracle 9i
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96544/gencon.htm#1656
Oracle Generic connectivity setup in Oracle 10g
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14232/gencon.htm#g1012648

Oracle Transparent Gateways is another solution to access non-oracle database and the HS agent is a system-specific application. For example, if you include a MSSQL Sybase database in an Oracle Database distributed system then you need to obtain a Sybase-specific transparent gateway so that the Oracle Database in the system can communicate with it.

Oracle transparent Gateways are licensed separately and agents can be installed on any machine. They can be on the same machine as the Oracle database system or on the same machine as the non-Oracle system or on a third machine as a stand alone.

Following are dedicated Gateways for non-Oracle data sources:-
- Microsoft SQL*Server - Database Gateway for SQL*Server (DG4Msql)
- Sybase - Database Gateway for Sybase (DG4Sybase)
- Informix - Database Gateway for Informix (DG4Ifmx)
- IBM DB2 - Database Gateway for DRDA (DG4DRDA)
- Teradata - Database Gateway for Teradata (DG4Teradata)
- Websphere MQ - Database Gateway for Websphere MQ (DG4MQ)
- Remote online transaction processors (OLTPs) - Database Gateway for APPC (DG4APPC)
- IMS - Database Gateway for IMS (DG4IMS)
- VSAM - Database Gateway for VSAM (Dg4VSAM)
- Adabas - Database Gateway for Adabas (DG4Adabas)

See the below links to setup Transparent Gateways.
Oracle Transparent Gateways setup in Oracle 9i
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96544/admin.htm#170863
Oracle Transparent Gateways setup in Oracle 10g
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14232/admin.htm

Non-Oracle data sources for which a dedicated transparent gateway is not available can be accessed by using Database Gateway for ODBC (DG4ODBC), which uses third party ODBC drivers to make the connection to the non-Oracle data source

Database Gateway for ODBC (DG4ODBC) is the 11g version of the generic connectivity and certified for older versions (9i, 10g). However, this does require that the Oracle 10g database accessing non-Oracle database through it must be patched to at least 10.2.0.4.

See the below link for Heterogeneous connectivity setup for Oracle Database Gateways
http://www.oracle.com/pls/db111/portal.portal_db?selected=15

There are differences in licensing and functionality of the Database Gateway for ODBC and the other gateways. For more information see the Metalink notes.
Note.232482.1 Gateway and Generic Connectivity Licensing Considerations
Note.252364.1 Functional Differences between Generic Connectivity and Database Gateways

The earlier gateway versions are de-supported. For more information see the Metalink notes.
Note.549796.1 Desupport of Oracle Transparent Gateways

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com