Oracle
offers two connectivity solutions to address the needs of disparate data
access. They are: 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.
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 both Oracle9i and
Oracle9iAS .
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: Generic Connectivity and Oracle Transparent Gateways.
Both 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 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. 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. As of Oracle9iAS, Heterogeneous Services is also part
of Oracle9iAS Database Cache thereby enabling connection to the
non-Oracle system from the middle tier using an agent.
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 and Oracle9iAS, so they are installed
by default with these products. Generic Connectivity use these agents to
connect to the non-Oracle systems.
Transparent
Gateway agents are part of Oracle9i and Oracle9iAS 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.
Generic Connectivity
Generic Connectivity
is a feature of Oracle9i and Oracle 9iAS. 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. 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
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 Generic Connectivity. Generic Connectivity relies on industry standards,
whereas Oracle Transparent Gateways accesses the non-Oracle systems using
their native interface. The 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 ORACLE9i
FEATURES
-
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
Server 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
|
| RELATED
PRODUCTS AND SERVICES
Both connectivity
solutions can be used in conjunction with other Oracle products such as:
-
Oracle Warehouse
Builder
-
Oracle9iAS Report
Services
-
Oracle9iAS Discoverer
-
Oracle9iAS
Forms Services
-
Oracle9iAS
Oracle9iAS Portal
-
Oracle9iAS
Database Cache
|
GETTING
STARTED
Generic Connectivity
is a feature of Oracle9i and Oracle 9iAS and is available
for free on Solaris, NT, HP and IBM AIX
The open system
transparent gateways listed below are available in the Open System Gateways
bundle and licensed separately from the database |
| |
| Technical Specifications |
| Gateway v9i |
Platform
Availability |
Oracle Server |
Non-Oracle
System |
Supported
Datatypes |
Restrictions |
| Generic Connectivity |
Solaris, NT,
HP, IBM AIX |
9i |
Any ODBC or OLE DB
compliant source |
- |
Stored Procedure and Distributed Transactions are 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 |
Top
of Page
|
Bookmark
