|Oracle9i Heterogeneous Connectivity Administrator's Guide
Release 1 (9.0.1)
Part Number A88789_01
You can access a non-Oracle database system either by Transparent Gateways or with Generic Connectivity. This chapter describes the architecture of Heterogeneous Services insofar as it relates to each of these means of accessing a non-Oracle system.
This chapter contains these topics:
At a high level, Oracle heterogeneous connectivity process architecture is structured as shown in Figure 2-1.
The Heterogeneous Services module in the kernel talks to a Heterogeneous Services agent process which, in turn, talks to the non-Oracle system. We can conceptually divide the code into three parts:
An agent is the process through which an Oracle server connects to a 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 the Oracle database server from third-party code. In order for a process to access the non-Oracle system, the non-Oracle system client libraries have to 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 the Oracle server to go down. Having an agent process isolates the Oracle server from any problems in third-party code so that even if a fatal error takes place, only the agent process will end.
An agent can reside in the following places:
Agent processes are usually started when a user session makes its first non-Oracle system access 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 behave slightly differently. They have to be explicitly started and shut down by a database administrator instead of automatically being spawned by Oracle Net Services.
For more information on multithreaded agents, please see Chapter 5, "Using Multithreaded Agents"
An agent process that accesses a non-Oracle system is called a gateway. (Note that agents can also be used to execute external procedures.) Access to all gateways goes through the Heterogeneous Services module in the Oracle server and all gateways contain the same agent-generic code. Each gateway has a different driver linked in which maps the Heterogeneous Services application programming interface (API) to the client API of the non-Oracle system.
An Oracle Transparent gateway is a gateway that is designed for accessing a specific non-Oracle system. Oracle Corporation provides gateways to access several commercially produced non-Oracle systems; many of these gateways have been ported to several platforms. For example, an Oracle Transparent Gateway for Sybase on Solaris is the Solaris port of a gateway designed to access Sybase database systems.
With Oracle Transparent Gateways, you can use an Oracle database server to access data anywhere in a distributed database system without needing to know either the location of the data or how it is stored. When the results of your queries are returned to you by the Oracle database server, 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 transparent gateways to various non-Oracle database systems, there is a set of agents that comprise the Oracle generic connectivity feature.These agents contain only generic code and the customer is responsible for providing the necessary drivers. Oracle has generic connectivity agents for ODBC and OLE DB that enable you to use ODBE and OLEDB drivers to access non-Oracle systems that have an ODBC or an OLE DB interface.
To build a gateway to a specific non-Oracle system using generic connectivity, you must connect an ODBC or OLE DB driver to the gateway for that non-Oracle system. These drivers are not provided by Oracle corporation. However, as long as Oracle Corporation supports the ODBC and OLE DB protocols, you can use the generic connectivity feature to access any non-Oracle system that can be accessed using an ODBC or OLE DB driver.
Generic connectivity has some limitations. The ODBC and OLEDB gateways have to be installed in the same Oracle Home directory as the Oracle database server. Connecting to one of these gateways from another Oracle database server is not supported. Functionality of these gateways, especially when compared to Oracle Transparent Gateways, is limited.
For more information, see Chapter 7, "Generic Connectivity"
The transaction service component of the Heterogeneous Services module makes it possible for non-Oracle systems to be integrated into Oracle database server transactions and sessions. When you access a non-Oracle system for the first time over 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 transparently closes at the non-Oracle system.
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 coordinate transparently 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.
The standard query language (SQL) service handles the processing of all SQL-related operations. The work done by the SQL service includes:
In the previous section, we described what the different heterogeneous components do. These components consist entirely of generic code and, in order to work with so many different non-Oracle systems, their behavior has to be configured. Each gateway has configuration information stored in the driver module and this information is uploaded to the server immediately after the connection to the gateway has been established. We can divide this configuration information into three parts:
Data dictionary translations are views on non-Oracle system data dictionary tables that help Heterogeneous Services translate references to Oracle data dictionary tables into queries needed to retrieve the equivalent information from the non-Oracle system data dictionary.
For a more detailed explanation of data dictionary translations, please see Appendix D, "Data Dictionary Translation Support".
Initialization parameters serve two functions.
You can examine the 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 and are not 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.
As mentioned in the previous section, configuration information is uploaded from an agent to the Heterogeneous Services module immediately after the connection to the agent has been established. Since this information can be very large in size, it is inefficient to do uploads on each connection. Therefore, the first time an Oracle database talks to an agent, the configuration information is uploaded and stored in Heterogeneous Services data dictionary tables and thereafter no upload takes place until something at the agent changes (for example, if a patch is applied or if the agent is upgraded to a new version).
Using Heterogeneous Services, a user can access several non-Oracle systems from a single Oracle database. This is illustrated in Figure 2-2
Both the agents upload configuration information that is stored as part of the Oracle data. This information is organized in the Heterogeneous Services data dictionary as follows.
In the Heterogeneous Services data dictionary, Oracle organizes data by two levels of granularity called class and instance. A class pertains to a specific type of non-Oracle system. For example, you might want to access the class of Sybase database systems with your Oracle database server. An instance defines specializations within a class. For example, you might want to access several separate instances within a Sybase database system. Instance information takes precedence over class information, and class information takes precedence over server-supplied defaults.
Although it is possible to store data dictionary information at one level of granularity by having completely separate definitions in the data dictionary for each individual instance, this might lead to an unnecessarily large amount of redundant data dictionary information. To avoid this, Oracle organizes the data dictionary by two levels of granularity, in which each class definition (one level of granularity) is shared by all the particular instances (a second level of granularity) under that class.
For example, suppose that the Oracle database server 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 the Oracle database server 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 the Oracle database server.
The Heterogeneous Services data dictionary views contain the following kinds of information:
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 occurs when a client application queries the non-Oracle database system database through the gateway.
Oracle client applications can access non-Oracle database system data with Oracle SQL just as if the data residing in the non-Oracle database system were stored in a remote Oracle database. Combined data residing in both Oracle and non-Oracle database system databases can be accessed by a single SQL statement performing heterogeneous joins and subselects. This means you can develop a single set of portable applications to use against both Oracle and non-Oracle database system databases. In this way, you can continue to develop new information systems without losing your investment in existing data and applications.
Also, transaction integrity for transactions involving updates to both Oracle and non-Oracle database system databases from a single Oracle database server is automatically protected by the Oracle two-phase commit feature.
Finally, synonyms in the Oracle database server can be used for transparent access to the non-Oracle system. Synonyms within the Oracle database server that point to database links to non-Oracle database system tables makes the physical location of the data transparent to the client application. This allows the future migration of data from the non-Oracle database system to Oracle to be transparent to client applications.
Only the Oracle database server and Oracle Net are needed to set up a gateway to a non-Oracle system. All other Oracle products are not necessary. However, using other Oracle products with the gateway can greatly extend the capabilities of a gateway.