Oracle9i Heterogeneous Connectivity Administrator's Guide Release 1 (9.0.1) Part Number A88789_01 |
|
Heterogeneous Services, an integrated module within the Oracle9i database server, has been designed to access data in non-Oracle systems by means of either Oracle Transparent Gateways or generic connectivity. This chapter introduces you to Heterogeneous Services by describing the kinds of situations in which Heterogeneous Services is needed and by explaining how Heterogeneous Services fulfills this need.
This chapter contains these topics:
Heterogeneous access is a challenge that affects many organizations. Many run several different database systems. Each of these systems stores data and has a set of applications that runs against it. Consolidation of this data into one database system is often difficult. This is 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 work together.
There are several problems to overcome before such inter operability becomes possible. The database systems can have different access interfaces, different data types, different capabilities, and different ways of handling error conditions. Even when one relational database is trying to access another relational database the differences are significant. In such a situation, the common features of the databases include data access through SQL, two phase commit, and similar data types. However, there are significant differences as well. SQL dialects can be different as can transaction semantics. There can be some data types in one database that do not exist in the other. The most significant area of difference is in the data dictionaries of the two databases. Most data dictionaries contain similar information but the information is structured for each in a completely different way.
There are several possible ways of overcoming this problem. In this book, we describe the approach that Oracle has taken.
If a client program wishes to access or modify data at several Oracle databases, it can open up connections to each of them. This approach, however, has several drawbacks. If data from the databases has to be joined, then the client will have to contain logic that does that. If data integrity has to be guaranteed, then the client will have to contain transaction coordination logic. An alternative approach is for the client to connect to one Oracle database and shift the burden of joining data and transaction coordination to that database. We call the database that the client program connects to the local database. We call any database other than this one a remote database. The client program can access objects at any of the remote databases using database links. The Oracle query processor will take care of the joins and its transaction engine will take care of the transaction coordination.
The approach that Oracle has taken to solving the heterogeneous connectivity problem is to allow a non-Oracle system to be one of the remote nodes in the above scenario. From the client's point of view the remote non-Oracle system will function like a remote Oracle system would. It appears to understand the same SQL dialect and to have the same data dictionary structure as an Oracle system. Access to a non-Oracle system in this manner is done through a module in the Oracle server called Heterogeneous Services. Using Heterogeneous Services with the client program can do the following
Heterogeneous Services can also be used to call external routines written in C using PL/SQL calls. This aspect of Heterogeneous Services is not covered in this book. For more information on external procedures please see Oracle9i SQL Reference and Oracle9i Application Developer's Guide - Fundamentals.
Note:
The work done by the Heterogeneous Services module is, for the most part, completely transparent to the end user. With only a few exceptions, you do not need to do anything different to access a non-Oracle system than you would for accessing an Oracle system. The Heterogeneous Services Module is used as enabling technology for many of heterogeneous access products that Oracle Corporation designs and for features including Oracle Transparent Gateways and Generic Connectivity (both of which are discussed in detail in this book).
You generally implement Heterogeneous Services in one of the following ways:
Much of the processing power of Oracle Transparent Gateways for Oracle7 and earlier versions of the sever has been integrated into Oracle8i and later versions of the Oracle database server as a module called Heterogeneous Services.
In the all versions of the Oracle server and Oracle Transparent Gateways up to Oracle7, much of the transaction processing code for the gateway was contained in the gateway itself. However, much of the same code also existed within the Oracle database server. Because of this redundancy, using a gateway placed an unnecessary demand on system resources.
An additional redundancy existed whenever you tried to use more than one gateway from the same database server. This was because each gateway contained large segments of code that were common to all the gateways. This meant that using more than one gateway at a time also placed an unnecessary demand on system resources.
The approach that Oracle has taken for Oracle8i and later versions of the Oracle database server has been to integrate all code that is redundant in either of these two ways into the Heterogeneous Services module of the Oracle database server. The advantage to this is that using gateways now requires less memory storage space and processing power than it did in Oracle7 and earlier releases. The result is a "thin" transparent gateway which functions based on the Heterogeneous Services module of the database with the following benefits.
This section describes the following additional features provided by the Heterogeneous Services module:
Remote data access provides distributed database system administrators with several benefits.
Applications can take advantage of Oracle client-server capability to connect to a remote server using Oracle Net. The remote server can then connect to the gateway using a database link. So, because the Oracle architecture enables network connections between each of the components, you have more options for locating your data.
Remote access also gives you access to data outside your local environment. With remote access, you can move application development onto cost-efficient workstations or microcomputers. Also, with remote access, your data sources are virtually unlimited. Remote access also enables you to choose the best environment for your users. For example, data might be located on a platform that supports only character-mode interfaces, but, with remote access, users can access the data from desktop platforms that support graphical user interfaces.
An Oracle Transparent Gateway gives applications direct access to non-Oracle database system data. This consequently eliminates the need to upload and download large amounts of data to different locations. Reducing the need to upload and download large amounts of data has the further consequence of reducing the risk for unsynchronized or inconsistent data. And, by reducing the need for data duplication, an Oracle transparent gateway reduces the disk storage needs across all of your systems.
The Oracle database server can accept a SQL statement that queries data stored in several different databases. The Oracle database server with the Heterogeneous Services module processes the SQL statement and passes the appropriate SQL directly to other Oracle databases and through gateways to non-Oracle databases. The Oracle database server then combines the results and returns them to the client. This enables a query to be processed so that it spans the non-Oracle database system, other databases, and local and remote Oracle data.
An Oracle Transparent Gateway extends the range of user tools and application development that you can use to access the databases. These user tools increase application development and user productivity by reducing prototype, development, and maintenance time. This means that current Oracle users do not have to learn a new set of tools to access data stored in non-Oracle database system databases. Instead, they can access Oracle and non-Oracle database system data with a single set of tools. These tools can run on remote machines connected through Oracle Net to the Oracle database server.
In a distributed database system, the network might fail during a distributed transaction, raising the risk of data inconsistencies. The Oracle transaction model uses a two-phase commit protocol to protect the databases as the data is being committed at sites participating in a distributed transaction. This feature ensures that all database servers participating in the transaction must commit or roll back the transaction statements. The two-phase commit protocol is also supported (with some limitations) for non-Oracle systems when the user is accessing them through an Oracle Transparent Gateway.
Whenever possible, the Oracle database server passes the entire query to the non-Oracle system to utilize the indexes and statistics of the non-Oracle system tables.
When a query that involves multiple databases is processed, the Oracle database server passes optimized statements to the remote servers and gateways involved in the query to minimize the amount of data returned across the network.
The gateway provides error mapping and logging. It does this by mapping the non-Oracle database system error to an Oracle database server error message and adding all of the relevant error messages generated by non-Oracle database system. You can route messages to the client application, an operator console, an error log, or any combination of these destinations as needed. Error mapping provides database transparency for applications.
As mentioned in the previous sections, Heterogeneous Services technology can allow clients to transparently access non-Oracle systems using Oracle SQL. In some cases, however, it becomes necessary to use non-Oracle system SQL to access the non-Oracle system. For such cases, Heterogeneous Services has a pass-through feature which allows the user to bypass Oracle's query processor and to issue non-Oracle system SQL to the non-Oracle system through the gateway.
|
![]() Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|