|Oracle® Database Heterogeneous Connectivity User's Guide
11g Release 2 (11.2)
Part Number E11050-01
Oracle's synchronous solutions for operating in a heterogeneous environment are Oracle Database gateways. The common component of Oracle Database for supporting these solutions is Heterogeneous Services. This chapter describes the architecture and functionality of the Heterogeneous Services component and its interaction with Oracle Database gateways.
This chapter contains the following topics:
At a high level, Oracle heterogeneous connectivity process architecture is structured as shown in Figure 2-1.
Figure 2-1 Oracle Heterogeneous Connectivity Process Architecture
The Heterogeneous Services component in Oracle Database communicates with a Heterogeneous Services agent process which, in turn, communicates with the non-Oracle system. The code can be conceptually divided into three parts:
The Heterogeneous Services component in Oracle Database
This module performs most of the processing related to heterogeneous connectivity.
Agent generic code
This is code in the agent that is generic to all Heterogeneous Services products. This code communicates with the database and provides multithreading support.
This module communicates with the non-Oracle system. It is used to map calls from the Heterogeneous Services to the native API of the non-Oracle system, and it is not specific to Oracle systems.
A Heterogeneous Services agent is the process through which Oracle Database connects to a non-Oracle system. The agent process that accesses a non-Oracle system is called a gateway. Access to all gateways goes through the Heterogeneous Services component in Oracle Database and all gateways contain the same agent-generic code. Each gateway has a different driver linked in that maps the Heterogeneous Services to the client API of the non-Oracle system.
The agent process consists of two components. These are agent generic code and a non-Oracle system-specific driver. An agent exists primarily to isolate Oracle Database from third-party code. For a process to access the non-Oracle system, the non-Oracle system client libraries must be linked into it. In the absence of the agent process, these libraries would have to be directly linked into the Oracle database and problems in this code could cause Oracle Database to fail. An agent process isolates Oracle Database from any problems in third-party code. Even if a fatal error occurs in the thirde-party code, only the agent process will end.
An agent can be in the following places:
On the same computer as the non-Oracle system
On the same computer as Oracle Database
On a computer different from either of these two
Agent processes are started when a user session accesses a non-Oracle system through a database link. These connections are made using Oracle's remote data access software, Oracle Net Services, which enables both client/server and server/server communication. The agent process continues to run until the user session is disconnected or the database link is explicitly closed.
Multithreaded agents act differently. They must be explicitly started and shut down by a database administrator, instead of automatically being spawned by Oracle Net Services
There are two types of Heterogeneous Services agents:
An Oracle Database gateway is a gateway that is designed for accessing a specific non-Oracle system. Oracle provides gateways to access several commercially available non-Oracle systems. For example, an Oracle Database Gateway for Sybase is designed to access Sybase databases.
With Oracle Database gateways, you can access data anywhere in a distributed database system without being required to know either the location of the data or how it is stored. When the results of your queries are returned to you by Oracle Database, they are presented to you as if the data stores from which they were taken all resided within a remote instance of an Oracle distributed database.
In addition to Oracle Database gateways for various non-Oracle database systems, there is the Oracle Database Gateway for ODBC agent. This agent contains only generic code, and the customer is responsible for providing the necessary drivers. Oracle Database Gateway for ODBC enables you to use ODBC drivers to access non-Oracle systems that have an ODBC interface.
To access a specific non-Oracle system using Oracle Database Gateway for ODBC, you must configure an ODBC driver to the non-Oracle system. These drivers are not provided by Oracle. However, if the non-Oracle system supports the ODBC protocols, you can use Oracle Database Gateway for ODBC to access any non-Oracle system that can be accessed using an ODBC driver.
Oracle Database Gateway for ODBC has some limitations. Especially, when compared to a paticular target, the functionality and performance are limited.
This section discusses the components of Heterogeneous Services in Oracle Database. These components are:
The transaction service component of the Heterogeneous Services component enables non-Oracle systems to be integrated into Oracle Database transactions and sessions. When you access a non-Oracle system for the first time using a database link within your Oracle user session, you transparently set up an authenticated session in the non-Oracle system. At the end of your Oracle user session, the authenticated session in the non-Oracle database system is closed.
Additionally, one or more non-Oracle systems can participate in an Oracle distributed transaction. When an application commits a transaction, Oracle's two-phase commit protocol accesses the non-Oracle database system to transparently coordinate the distributed transaction. Even in those cases where the non-Oracle system does not support all aspects of Oracle two-phase commit protocol, Oracle can (with some limitations) support distributed transactions with the non-Oracle system.
Mapping Oracle internal SQL-related calls to the Heterogeneous Services driver application programing interface (API). This API is then mapped by the driver to the client API of the non-Oracle system.
Translating SQL statements from Oracle's SQL dialect to the SQL dialect of the non-Oracle system.
Translating queries that reference Oracle data dictionary tables to queries that extract the necessary information from the non-Oracle system's data dictionary.
Converting data from non-Oracle system data types to Oracle data types and back.
Compensating for missing functionality of the non-Oracle system by issuing multiple queries to get the necessary data and doing postprocessing to get the desired results.
Heterogeneous Services components consist of generic code and must be configured to work with many different non-Oracle systems. Each gateway has configuration information stored in the driver module. The information is uploaded to the server immediately after the connection to the gateway is established. The configuration information includes:
Data dictionary translations are views on non-Oracle data dictionary tables. They help Heterogeneous Services translate references to Oracle data dictionary tables into queries that can retrieve the equivalent information from the non-Oracle data dictionary.
Heterogeneous Services initialization parameters serve two functions:
They give you a means of fine-tuning the gateway to optimize performance and memory utilization for the gateway and the Heterogeneous Services component.
They enable you to inform the gateway (and, thereby, Heterogeneous Services) how the non-Oracle system was configured (for example, in what language the non-Oracle system is running). They give information to Heterogeneous Services about the configurable properties of the non-Oracle system.
You can examine the Heterogeneous Services initialization parameters for a session by querying the view
V$HS_PARAMETER. Users can set initialization parameters in gateway initialization files.
Capabilities tell Heterogeneous Services about the limitations of the non-Oracle system (such as what types of SQL statements are supported) and how to map Oracle data types and SQL expressions to their non-Oracle system equivalents. In other words, they tell Heterogeneous Services about the non-configurable properties of the non-Oracle system. Capabilities cannot be changed by the user.
Configuration information is uploaded from an agent to the Heterogeneous Services component immediately after the connection to the agent is established. The configuration information is stored in Heterogeneous Services data dictionary tables. No further uploading occurs until something at the agent changes (for example, if a patch is applied or if the agent is upgraded to a new version).
The following sections describe:
Classes and Instances
Data Dictionary Views
Using Heterogeneous Services, you can access several non-Oracle systems from a single Oracle database. This is illustrated in Figure 2-2, which shows two non-Oracle systems being accessed.
Figure 2-2 Accessing Multiple Non-Oracle Instances
Both agents upload configuration information, which is stored as part of the Heterogeneous Services data dictionary information on Oracle Database.
Although it is possible to store data dictionary information at one level of granularity by having completely separate definitions in the Heterogeneous Services data dictionary for each individual instance, this can lead to an unnecessarily large amount of redundant data dictionary information. To avoid this, Oracle organizes the Heterogeneous Services data dictionary by two levels of granularity, called class and instance.
A class pertains to a specific type of non-Oracle system. For example, you may want to access the class of Sybase database systems with Oracle Database. An instance defines specializations within a class. For example, you may want to access several separate instances within a Sybase database system. Each class definition (one level of granularity) is shared by all the particular instances (a second level of granularity) under that class. Further, instance information takes precedence over class information, and class information takes precedence over server-supplied defaults.
For example, suppose that Oracle Database accesses three instances of Sybase and two instances of Ingres II. Sybase and Ingres II each have their own code, requiring separate class definitions for Oracle Database to access them. The Heterogeneous Services data dictionary therefore would contain two class definitions, one for Sybase and one for Ingres II, with five instance definitions, one for each instance being accessed by Oracle Database.
Note that instance-level capability and data dictionary information are session-specific and are not stored in the Heterogeneous Services data dictionary of Oracle Database. However, instance-level initialization parameters can be stored in the database.
The Heterogeneous Services data dictionary views contain the following kinds of information:
Names of instances and classes uploaded into the Oracle data dictionary
Capabilities, including SQL translations, defined for each class or instance
Data Dictionary translations defined for each class or instance
Initialization parameters defined for each class or instance
You can access information from the Oracle data dictionary by using fixed views. The views are categorized into three main types:
Figure 2-3 shows a typical gateway process flow. The steps explain the sequence of events that occur when a client application queries the non-Oracle database system database through the gateway.
Figure 2-3 Gateway Process Flow
The client application sends a query using Oracle Net to Oracle Database.
Heterogeneous Services and the gateway converts the SQL statement into a SQL statement understood by the non-Oracle database system.
Oracle Database sends the query to the gateway using Oracle Net.
For the first transaction in a session, the gateway logs in to non-Oracle database system using a user name and password that is valid in the non-Oracle system.
The gateway retrieves data using non-Oracle database system SQL statements.
The gateway converts retrieved data into a format compatible with Oracle Database.
The gateway returns query results to Oracle Database, again using Oracle Net Services.
Oracle Database passes the query results to the client application using Oracle Net. The database link remains open until the gateway session is finished, or the database link is explicitly closed.