|Oracle Heterogeneous Services
Part Number A88714-01
This chapter describes the basic concepts of Heterogeneous Services.
This chapter contains these topics:
Heterogeneous Services is a component within the Oracle database server that is required to access a non-Oracle database system.
The term "non-Oracle database system" refers to the following:
Heterogeneous Services makes it possible for Oracle database server users to do the following:
Heterogeneous Services is generally applied in one of two ways:
Heterogenous Services is composed of two basic components:
An agent is the Heterogeneous Services process that links the Oracle database server into the code of the non-Oracle system. Agent generic code in Heterogeneous Services in combination with a driver becomes an agent. Drivers are specific to the type of non-Oracle system you want to access and provide the systems interface between the non-Oracle system and the agent generic code of Oracle Heterogeneous Services.
An agent can reside in the following places:
Agent processes are activated when a user session accesses a non-Oracle system through a database link on an Oracle database server. These connections are made using Oracle's remote data access software, Net8, that 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.
An Oracle Transparent Gateway is a particular kind of agent that is designed by Oracle Corporation to access commercially produced brands and versions of database systems not marketed by Oracle Corporation. For example, an Oracle Transparent Gateway for Sybase on Solaris is designed to access Sybase database systems that are running on Solaris platforms.
With Oracle Transparent Gateways, you can use an Oracle database server to access data anywhere in a distributed database system without needing to know the location of the data or how it is stored. Also, when the results of your queries are returned to you by the Oracle database server, they are presented to you as if the datastores from which they were taken all resided within a remote Oracle database. This functionality is called transparency; when you are using it, you are transparently accessing a non-Oracle database system.
Generic connectivity is a feature of the Oracle database server that enables users to use ODBC and OLE DB drivers to access non-Oracle systems having an ODBC or an OLE DB interface.
A gateway using generic connectivity must have an additional ODBC or OLE DB driver to provide an interface between generic connectivity and the 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 these gateways to access their respective non-Oracle systems.
The ODBC and OLE DB drivers using generic connectivity are 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.
Heterogeneous Services provides the following components:
The transaction service allows non-Oracle systems to be integrated into Oracle database server transactions and sessions. Users transparently set up an authenticated session in the non-Oracle system when it is accessed for the first time over a database link within an Oracle user session. At the end of the Oracle user session, the authenticated session in the non-Oracle system is transparently closed 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 system to transparently coordinate the distributed transaction. Even if the non-Oracle system does not support all aspects of Oracle's two-phase commit protocol, the Oracle database server usually supports distributed transactions with the non-Oracle system.
The SQL service uses the transaction service while Oracle's object transaction service uses agents that implement only the transaction service.
"Using Transaction Service Views" for more information on heterogeneous distributed transactions.
The SQL service uses SQL to access the non-Oracle system transparently. If an application's SQL request requires data from a non-Oracle system, Heterogeneous Services does the following:
The SQL service provides the following capabilities:
With Heterogeneous Services, a non-Oracle system appears to the user as a remote Oracle database server. To access or manipulate tables or to execute procedures in the non-Oracle system, you must create a database link that specifies the connect descriptor for the non-Oracle database. Use the following syntax to create a link to a non-Oracle system (variables in italics):
If a non-Oracle system is referenced, then Heterogeneous Services translates the SQL statement or PL/SQL remote procedure call into the appropriate statement at the non-Oracle system.
You can access tables and procedures at the non-Oracle system by qualifying the tables and procedures with the database link. This operation is identical to accessing tables and procedures at a remote Oracle database server.
Consider the following example, which accesses a non-Oracle system through a database link:
Heterogeneous Services translates the Oracle SQL statement into the SQL dialect of the target system and then executes the translated SQL statement at the non-Oracle system.
You can access multiple non-Oracle systems from the same Oracle database server, as illustrated in Figure 1-2.
For Heterogeneous Services generic code to correctly generate SQL, map datatypes, and interact effectively with the code of the non-Oracle system, Heterogeneous Services needs information about that system. This information is uploaded from the agent for that system and is stored in the Heterogeneous Services data dictionary.
Oracle organizes information about the non-Oracle system by two levels of granularity in the Heterogeneous Services data dictionary. These two levels of granularity are 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 could lead the amount of stored data dictionary information to become unnecessarily large and redundant. 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, consider a case where the Oracle database server accesses three instances of Sybase and two instances of Ingres II. Sybase and Ingres II each have their own code, which requires separate class definitions for the Oracle database server to be able 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 also contains the following types of configuration data:
The Heterogeneous Services data dictionary views contain information about:
You can access information from the Oracle data dictionary by using fixed views. The views can be divided into three main types: