1 Introduction

1.1 Overview

Heterogeneous data access is a problem that affects a lot of companies. A lot of companies run several different database systems. Each of these systems stores data and has a set of applications that run against it. Consolidation of this data in one database system is often hard - in large part because many of the applications that run against one database may not have an equivalent that runs against another. Until such time as migration to one consolidated database system is made feasible, it is necessary for the various heterogeneous database systems to interoperate.

Oracle Database Gateways provide the ability to transparently access data residing in a non-Oracle system from an Oracle environment. This transparency eliminates the need for application developers to customize their applications to access data from different non-Oracle systems, thus decreasing development efforts and increasing the mobility of the application. Applications can be developed using a consistent Oracle interface for both Oracle and non-Oracle systems.

Gateway technology is composed of two parts: a component that has the generic technology to connect to a non-Oracle system, which is common to all the non-Oracle systems, called Heterogeneous Services, and a component that is specific to the non-Oracle system that the gateway connects to. Heterogeneous Services, in conjunction with the Oracle Database Gateways, enable transparent access to non-Oracle systems from an Oracle environment.

1.2 Heterogeneous Services Technology

Heterogeneous Services provides the generic technology for connecting to non-Oracle systems. As an integrated component of the database, Heterogeneous Services can exploit features of the database, such as the powerful SQL parsing and distributed optimization capabilities.

Heterogeneous Services extend 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. Heterogeneous Services provides two types of translations: the ability to translate Oracle SQL into the proper dialect of the non-Oracle system as well as data dictionary translations that displays the metadata of the non-Oracle system in the local format. For situations where no translations are available, native SQL can be issued to the non-Oracle system using the pass-through feature of Heterogeneous Services.

Heterogeneous Services also maintains the transaction coordination between Oracle and the remote non-Oracle system.

1.3 Oracle Database Gateway for ODBC

Oracle Database Gateway for ODBC is intended for low-end data integration solutions requiring the dynamic query capability to connect from an Oracle database to non-Oracle systems. Any data source compatible with the ODBC standards described in this chapter can be accessed using Oracle Database Gateway for ODBC.

The capabilities, SQL mappings, data type conversions, and interface to the remote non-Oracle system are contained in the gateway. The gateway interacts with Heterogeneous Services to provide the transparent connectivity between Oracle and non-Oracle systems.

1.4 Oracle Database Gateway for ODBC Architecture

To access the non-Oracle data store using Oracle Database Gateway for ODBC, the gateway works with an ODBC driver. The driver that you use must be on the same machine as the gateway. The non-Oracle system can reside on the same machine as the Oracle database or on a different machine.

The gateway can be installed on the machine running the non-Oracle system, the machine running the Oracle database or on a third machine as a standalone. Each configuration has its advantages and disadvantages. The issues to consider when determining where to install the gateway are network traffic, operating system platform availability, hardware resources and storage.

1.4.1 Oracle and Non-Oracle Systems on Separate Machines

Figure 1-1 shows an example of a configuration in which an Oracle and non-Oracle database are on separate machines, communicating through Oracle Database Gateway for ODBC. The client connects to the non-Oracle system through a network.

Figure 1-1 Oracle and Non-Oracle Systems on Separate Machines

Description of "Figure 1-1 Oracle and Non-Oracle Systems on Separate Machines"

In this configuration:

1. A client connects to the Oracle database through Oracle Net.

2. The Heterogeneous Services component of the Oracle database connects through Oracle Net to the gateway.

3. The gateway communicates with the following non-Oracle components:

   • An ODBC driver manager

   • An ODBC driver

4. Each user session receives its own dedicated agent process spawned by the first use in that user session of the database link to the non-Oracle system. The agent process ends when the user session ends.

Note:

The ODBC driver may require non-Oracle client libraries even if the non-Oracle database is located on the same machine. Refer to your ODBC driver documentation for information about the requirements for the ODBC driver.

1.4.2 Oracle and Non-Oracle Systems on the Same Machine

Figure 1-2 shows an example of a configuration in which an Oracle and non-Oracle database are on the same machine, again communicating through Oracle Database Gateway for ODBC.

Figure 1-2 Oracle and Non-Oracle Systems on the Same Machine

Description of "Figure 1-2 Oracle and Non-Oracle Systems on the Same Machine"

In this configuration:

1. A client connects to the Oracle database through Oracle Net.

2. The Heterogeneous Services component of the Oracle database connects through Oracle Net to the gateway

3. The agent communicates with the following non-Oracle components:

   • An ODBC driver manager

   • An ODBC driver

   The driver then allows access to the non-Oracle data store.

4. Each user session receives its own dedicated agent process spawned by the first use in that user session of the database link to the non-Oracle system. The agent process ends when the user session ends.

Note:

The ODBC driver may require non-Oracle client libraries even if the non-Oracle database is located on the same machine. Refer to your ODBC driver documentation for information about the requirements for the ODBC driver.

1.5 ODBC Connectivity Requirements

To use Oracle Database Gateway for ODBC, you must have an ODBC driver installed on the same machine as the gateway. The ODBC driver manager and driver must meet the following requirements:

• The following ODBC catalog functions must work inside a transaction:

  • SQLColumns

  • SQLForeignKeys

  • SQLGetFunctions

  • SQLGetInfo

  • SQLGetTypeInfo

  • SQLPrimaryKeys

  • SQLProcedureColumns

  • SQLProcedures

  • SQLStatistics

  • SQLTables

• On Windows:

• • The ODBC driver must have compliance level to ODBC standard 3.0. For multi-byte support, the driver needs to meet ODBC standard 3.5.

  • The ODBC driver and driver manager must conform to ODBC application program interface (API) conformance Level 1 or higher. If the ODBC driver or driver manager does not support multiple active ODBC cursors, the complexity of SQL statements that you can execute using Oracle Database Gateway for ODBC is restricted.

• On UNIX:

• • The ODBC driver manager must be installed on the same machine.

  • The ODBC driver must have compliance level to ODBC Standard 3.0 and have a conformance level 1 or higher. If the ODBC driver works with an ODBC driver manager, the ODBC driver manager must be compliant with ODBC Standard 3.0 or higher. The ODBC driver must have compliance level to ODBC standard 3.0. For multi-byte support, the driver needs to meet ODBC standard 3.5.

• See Also:

  Your ODBC driver documentation for dependencies on an ODBC driver manager, and Oracle Database Concepts for more information on transaction isolation levels.

• The ODBC driver you use must support all of the core SQL ODBC data types and must support SQL grammar level SQL_92. The ODBC driver should also expose the following ODBC APIs:

  • SQLAllocHandle

  • SQLBindCol

  • SQLBindParameter

  • SQLCancel

  • SQLColAttribute

  • SQLColumns

  • SQLConnect

  • SQLDescribeCol

  • SQLDisconnect

  • SQLDriverConnect

  • SQLEndTran

  • SQLExecDirect

  • SQLExecute

  • SQLFetch

  • SQLForeignKeys

  • SQLFreeHandle

  • SQLFreeStmt

  • SQLGetConnectAttr

  • SQLGetData

  • SQLGetDiagField

  • SQLGetDiagRec

  • SQLGetEnvAttr

  • SQLGetFunctions

  • SQLGetInfo

  • SQLGetStmtAttr

  • SQLGetTypeInfo

  • SQLMoreResults

  • SQLNumResultCols

  • SQLParamData

  • SQLPrepare

  • SQLPrimaryKeys

  • SQLProcedureColumns

  • SQLProcedures

  • SQLPutData

  • SQLRowCount

  • SQLSetConnectAttr

  • SQLSetEnvAttr

  • SQLSetDescField

  • SQLSetDescRec

  • SQLSetStmtAttr

  • SQLStatistics - If statistics are to be supported

  • SQLTables