Monday, April 14, 2008

Oracle Transparent Gateways - General Description - Part I

A lot of companies have several applications based on more than one database system (e.g DB2, SQL Server, Sybase, etc).
Each database system store its own data and naturally there's a need to share data among the various heterogeneous database systems.

Oracle, starting with Oracle Database 9i, offers the "Oracle Transparent Gateways" (Oracle Database Gateways) to allow integration of Oracle database with non-Oracle databases.
Unlike "Oracle Generic Connectivity" that provide a generic solution to connect any ODBC/OLEDB compliant non-Oracle system using ODBC and OLEDB standards, the "Oracle Transparent Gateways" are solutions specifically tailored for each target non-Oracle database system.
The "Oracle Transparent Gateways" communicates using the target database native interface, it's make it possible to access to non-Oracle systems as if they were Oracle databases.

The Transparent Gateway solution composed of two parts:

  • Heterogeneous Services (HS) - this is a general integrated component that make it possible to connect to non-Oracle systems from Oracle database
  • Oracle Database Gateways (agent) - these are specific tailored agents for non-Oracle systems that make it possible to interacts with the target non-Oracle system.

Heterogeneous Services (HS)

This is a generic component for connecting to non-Oracle systems.
It's an integrated component of the database that "extends the Oracle SQL engine to recognize the SQL and procedural capabilities of the remote non-Oracle system and the mappings required to obtain necessary data dictionary information" (Oracle Doc').

The following services are provided by the Heterogeneous Services (HS):

  • Transaction service
    Responsible for establishing authenticated connection when the non-Oracle system is accessed and close the connection when session end.
    Also responsible for global data integrity using two phase commit protocol, even for non-Oracle systems that do not support two phase commit natively.
  • SQL Service
    Provide the translation capabilities: SQL & data dictionary translations.
    The SQL services uses an information arrived from the Gateway to translate Oracle SQL to the appropriate SQL dialect of the non-Oracle system. Also, references to data dictionary tables in a query will be rewrite by the SQL Service and result with a result set as from Oracle database.
    ** Data type translation performed by the Gateway.
  • Procedural Service
    An interface for executing stored procedures on non-Oracle system.
  • Pass through SQL
    A mechanism for issuing a SQL statement against the non-Oracle system. It is useful when the statement/function/procedure are not supported by the Gateway.

Oracle Database Gateways (agents)

This component responsible for the interface to the remote non-Oracle system.
It's also responsible for SQL mappings and data type conversions.
The Gateway interacts with Heterogeneous Services to make it possible to transparently connect from an Oracle Database to a non-Oracle System.
In contrast to the HS (Heterogeneous Services) which is a generic component, the Gateways are tailored specifically for each target non-Oracle system.
There are Gateways for many systems such as: DB2, Sybase, Informix, SQL Server, IMS, VSAM, Adabas, Ingres, Teradata, to name a few.
The Gateway can be installed on the same server like the non-Oracle system or on the same server like the Oracle system or on a separate server.

Next post I'll show an example of connecting and retrieving data from a SQL Server database to an Oracle database using Oracle Transparent Gateway for Microsoft SQL Server including all configuration required for Transparent Gateway and the source Oracle System.

Related Documents for more information:

- Oracle® Transparent Gateway for Microsoft SQL Server Administrator's Guide 10g Release 2 (10.2) for Microsoft Windows (32-bit)

- Database Gateways Technical Whitepaper

You are more than welcome to leave a comment.



Anonymous said...

Can I interface with two SQL Servers? One server is SQL Server 2000 the is SQL Server 2005. Will I need to purchase two licenses of the Oracle Tranparent Gateway or will one be sufficient.

Aviad said...

Yes, you can interface with more than one SQL servers with one deployment of Oracle Transparent Gateway.
But you should carefully choose the Transparent Gateway version to install.
Microsoft SQL*Server 2005 is certified with Oracle version 10.1 and 10.2 of the Transparent Gateway for SQL*Server (TG4MSQL).
If you're using a version lower than the above it might be a problem.


Anonymous said...

Is the gateway functionality included as part of 10g? Are there any additional license costs associated with implementing this solution wity say Sybase.

Denny M.

Jishar said...

Oracle Transparent Gateway for Ingres can be installed on windows system.

Whther gateway for Ingres supporting Windows??



Anonymous said...

Hi Avaid,

I just want to say thank you for being so detailed in your instructions.

This was very helpful

Aviad said...

Hi Denny,

1) The Gateway itself comes as a separate installation on top of an Oracle Home.

2) Yes it does associated with additional license costs.

Anonymous said...


We have the ff right now..

Ingres OpenRoad (Apps)
Ingres Gateway (Middleware)
Informix Database

We want to migrate to Oracle database WITHOUT changing the apps layer(Inges OpenRoad). Changes will be done on :

Ingres Gateway to Oracle Gateway
Informix to Oracle DB 10g.

I want to know if this is possible with 0 change from the apps layer.

Anonymous said...

I have these right now:

OpenRoad (4gl) -- (Ingres Corp)
Ingres Gateway -- (Ingres Corp).
Informix -- IBM Corp.

That we want to achieve is having Oracle 10G as Backend without changing the application layer. Is this doable? If we change the Gateway from Ingres Gateway to Oracle Gateway, will the application work?

Anonymous said...

Is it possible to do the reverse. connect and get data from oracle database and put it on teradata.