Oracle
offers two connectivity solutions to address the needs of disparate data
access. They are: Oracle Generic Connectivity and Oracle Transparent Gateways.
These two solutions make it possible to access any number of non-Oracle
systems from an Oracle environment in a heterogeneously distributed environment.
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. Oracle Transparent Gateways are solutions specifically
tailored for the target non-Oracle system and communicates using their
native interface. These solutions are available with Oracle9i Database.
PRODUCT SUMMARY
Heterogeneous data
access is a problem that affects a lot of companies. Many of them 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 due to the fact that
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. The challenge is
to quickly, efficiently, and economically deploy data that may exist on
many disparate systems through a single application, providing a comprehensive
view of the data, regardless of the database or operating system. Oracle
offers two connectivity solutions for the heterogeneous environment. They
are: Oracle Generic Connectivity and Oracle Transparent Gateways.
Both Oracle
Generic Connectivity and Oracle Transparent Gateways provide the ability
to transparently access data in non-Oracle systems 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.
For smooth interoperability
between disparate systems, SQL translations, data dictionary translations
and data type translations are required, even if the non-Oracle systems
are based on SQL standards. Both Oracle Generic Connectivity and Oracle
Transparent Gateways have the ability to translate one system's dialect
to another.
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 (HS) and a component that is target specific,
called an Agent. Heterogeneous Services in conjunction with the Agent enables
transparent access to non-Oracle systems from an Oracle environment.
Heterogeneous Services
Technology
Heterogeneous Services
provides the generic technology for connecting to non-Oracle systems and
is the processing power of both solutions. Oracle Generic Connectivity
and Oracle Transparent Gateways are based on Heterogeneous Services. 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 which 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, such as providing the two-phase commit protocol
to ensure distributed transaction integrity, even for non-Oracle systems
that do not natively support two-phase commit.
Agent
The capabilities,
SQL mappings, datatype conversions, and interface to the remote non-Oracle
system are contained in the Agent. The agent interacts with Heterogeneous
Services to provide the transparent connectivity between Oracle and non-Oracle
systems. There are two types of agents: HS agents and Transparent Gateway
agents.
There are two
types of HS agents: HS ODBC which uses an OBDC driver to talk with the
non-Oracle system and HS OLEDB which uses an OLEDB driver. HS agents are
part of the Oracle9i Database, so they are installed by default
with these products. Oracle Generic Connectivity use these agents to connect
to the non-Oracle systems.
Oracle Transparent
Gateway agents are part of Oracle9i Database however they are licensed
separately. Unlike the HS agents which has to be on the same machine as
the Oracle database, the Transparent Gateway agents can be installed on
any machine. They can be on the same machine as the Oracle database or
on the same machine as the non-Oracle system or on a third machine as a
stand alone. Each configuration has its advantages and disadvantages. The
issues to consider when determining where to install these agents are network
traffic, operating system platform availability, hardware resources and
storage. Oracle Transparent Gateways use these agents to connect to the
non-Oracle systems.
Oracle Generic
Connectivity
Oracle Generic
Connectivity is a feature of Oracle9i Database. It is a generic
solution that uses an ODBC or OLE DB driver to access any ODBC or OLEDB
compliant non-Oracle system. It addresses the needs of data access to many
data stores for which Oracle does not have a gateway solution. This feature
enables transparent connectivity using industry standards such as ODBC
and OLEDB. Oracle Generic connectivity makes it possible to access low-end
data stores such as Foxpro, Access, dBase and non-relational targets like
Excel.
Oracle Transparent
Gateways
In contrast to
Oracle Generic Connectivity which is a generic solution, Oracle Transparent
Gateways are tailored solutions, specifically coded for the non-Oracle
system. They provide an optimized solution, with more functionality and
better performance than Oracle Generic Connectivity. Oracle Generic Connectivity
relies on industry standards, whereas Oracle Transparent Gateways accesses
the non-Oracle systems using their native interface. Oracle Transparent
Gateways are also end-to-end certified. Oracle has transparent gateways
to many sources, Sybase, Informix, Microsoft SQL Server, Ingres, Teradata
to name a few.
NEW 9i FEATURES
-
Support for
LOBs - Full support for LOB datatype
-
Result Set -
Supports the capability of returning results sets from stored procedures
-
Date Time datatypes
- Supports Oracle datetime datatypes in SQL and stored procedures
-
SQL*Plus DESCRIBE
command - The SQL*Plus DESCRIBE command can be used to describe non-Oracle
system objects
-
Piecewise LONG
- Full support of LONG data type by piecewise handling of the data
-
Multithreaded
agents - Multiple user sessions can share the same agent process
|
| KEY
FEATURES |
-
Improved SQL
Generation - Fine grained capability handling
-
Generated SQL
queries cache - Non-Oracle language queries are generated and cached
in Oracle's Shared Pool, making it more efficient for reuse when such queries
are frequently used by multiple client sessions
-
Better performance
- Improved remote connectivity techniques of HS, provides better performance
-
Local Cache
- Information about capabilities, SQL translations, and data dictionary
translations are stored locally, reducing network traffic
-
Distributed
SQL Optimizations - Improves performance of multi-site JOIN and SET
operations
-
Pass-Through
SQL - Allows native SQL (including DDL) of target non-Oracle system
to be directly transmitted
-
Read/Write Access
- Both query and update capabilities are supported for most non-Oracle
systems.
-
Data Type Translations
- Performs automatic data type translations between the Oracle Sever and
the non-Oracle system
-
Data Dictionary
Translations - Metadata of the non-Oracle system is translated and
displayed in Oracle format
-
Gateway Mobility
- The Transparent Gateway, the non-Oracle system and the Oracle server
can all reside on different machines
|
-
Remote Stored
Procedures - Supports execution of stored procedures defined in non-Oracle
system
-
Two Phase Commit
- Oracle supports two-phase commit with non-Oracle stores. When a public
two-phase commit interface is not available, it can be supported when distributed
transactions involve Oracle and a single non-Oracle store
-
Logon Security
- Enforces access authorizations for remote login.
-
Data Encryption
- Available with Oracle's Advanced Security Option
-
Location Transparency
- Users do not need to be aware of the physical location of data, just
the names of the tables to be accessed
-
Commit Transparency
- Distributed and non-distributed transactions are committed using the
ANSI-standard SQL COMMIT. Oracle automatically detects when a transaction
is distributed and uses a two-phase commit protocol to ensure transaction
integrity and consistency
-
Network and
Operating System Transparency - No special coding is required, even
if the data resides on different systems
-
NLS and NCHAR
Support - Enables connectivity to non-Oracle systems using multi-byte
character sets
|
|
|
|
|
| Technical Specifications |
| Gateway v9i |
Platform
Availability |
Oracle Server |
Non-Oracle
System |
Supported
Datatypes |
Restrictions |
| Oracle Generic
Connectivity |
Solaris, NT,
HP, IBM AIX |
9i |
Any ODBC or
OLE DB compliant source |
- |
Stored procedure
and distributed transactions not supported
Installed on
the same machine as the database |
| Sybase |
Solaris, HP/UX,
NT, Tru64, IBM AIX |
9i |
11.9.2
12.0 |
BINARY, BIT,
CHAR, DATETIME, DECIMAL, DOUBLE PRECISION, FLOAT, IMAGE, INT, MONEY, NUMERIC,
REAL, SMALLDATETIME, SMALLINT, SMALL MONEY, TEXT, TINYINT, VARBINARY, VARCHAR |
- |
| MS SQL Server |
NT/2000 |
9i |
7.0 |
BINARY, BIT,
CHAR, DATETIME, DECIMAL, FLOAT, IMAGE, INTEGER, MONEY,
NUMERIC, REAL, SMALLDATETIME, SMALL MONEY, SMALLINT,TEXT, TIMESTAMP,
TINYINT VARBINARY, VARCHAR |
- |
| Informix |
Solaris, HP/UX |
9i |
7.2
7.3
7.4 |
BYTE, CHAR,
DATE, DATETIME, DECIMAL, DOUBLE PRECISION, FLOAT, INTEGER,
MONEY, NUMERIC, REAL, SERIAL, SMALLFLOAT, SMALLINT, TEXT, LONGVARCHAR |
Stored Procedure
not supported |
| Ingres |
Solaris, HP/UX |
9i |
Ingres II 2.0 |
BYTE, C, CHAR,
DATE DECIMAL, FLOAT, FLOAT4, FLOAT8, INTEGER, INTEGER1, INTEGER2, INTEGER4,
LONG BYTE, LONG VARCHAR, MONEY, OBJECT_KEY, SMALLINT, TEXT,
VARCHAR |
Stored Procedure
not supported |
| Teradata |
Solaris, HP/UX,
NT |
9i |
V2R3
V2R4 |
BYTE, BYTEINT,
CHAR, CHARACTER, DATE, DEC, DECIMAL, DOUBLE PRECISION, FLOAT, INT,
INTEGER, INTERVAL, LONG VARCHAR, NUMERIC, REAL, SMALLINT, TIME, TIMESTAMP,
VARBYTE, VARCHAR |
Stored Procedure
not supported |
| Rdb |
Alpha OpenVMS |
9i |
7.2 |
BIGINT, BIGINT(s),
CHAR, DATE VMS, DOUBLE PRECISION, DECIMAL, FLOAT, INTEGER, INTEGER(s),
LONG VARCHAR, NUMERIC, REAL, SMALLINT, SMALLINT(s), TIMESTAMP, TINYINT,
TINYINT(s), VARCHAR |
Stored Procedure
not supported |
| RMS |
Alpha OpenVMS |
9i |
7.2 |
- |
Stored Procedure
not supported |
|
|
|
| RELATED
PRODUCTS
Both connectivity
solutions can be used in conjunction with other Oracle products such as:
-
Oracle Warehouse
Builder
-
Oracle9iAS Report
Services
-
Oracle9iAS Discoverer
|
GETTING
STARTED
Oracle Generic
Connectivity is a feature of Oracle9i Database and is available
for free on Solaris, NT, HP and IBM AIX.
The transparent
gateways are licensed separately from the database although they are available
on the database CD as a custom install. |
|
|
| BUYING
TRANSPARENT GATEWAYS
For licensing
purposes Transparent Gateways are grouped together into 4 four bundles:
-
Open System
Gateways Bundle includes: Transparent Gateway for MS SQL Server, Transparent
Gateway for Informix, Transparent Gateway for Sybase, Transparent gateway
for Ingres, Transparent Gateway for Teradata, Transparent Gateway for RDB
and Transparent Gateway for RMS
-
Mainframe Integration
Gateways Bundle includes: Oracle Transparent Gateway for DB2 for OS/390,
Oracle Pure Extract
-
Enterprise Integration
Gateways Bundle includes: Access Manager for AS/400, Procedural Gateway
for MQSeries, Procedural Gateway for APPC, Transparent Gateway for DB2/400,
Transparent Gateway for IBM DRDA
-
EDA/SQL Gateways
Bundle includes: EDA/SQL Gateway
|
Top
of Page
|
Bookmark
