1
Heterogeneous Services Concepts

This chapter describes the basic concepts of Heterogeneous Services.

This chapter contains these topics:

• What is Heterogeneous Services?

• Heterogeneous Services Process Architecture

• Heterogeneous Services Components

• Architecture of the Heterogenous Services Data Dictionary

  See Also: Getting to Know Oracle8i about features that are new to this release.

What is Heterogeneous Services?

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:

• Any system accessed by PL/SQL procedures written in C (that is, by external procedures)

• Any system accessed through SQL (that is, by Oracle Transparent Gateways or generic connectivity)

• Any system accessed procedurally (that is, by procedural gateways)

  Note: This manual documents Heterogeneous Services as it relates to gateways. For more information on external procedures, see Oracle8i SQL Reference and Oracle8i Application Developer's Guide - Fundamentals.

Heterogeneous Services makes it possible for Oracle database server users to do the following:

• Use Oracle SQL statements to retrieve data stored in non-Oracle systems.

• Use Oracle procedure calls to access non-Oracle systems, services, or application programming interfaces (APIs) from within an Oracle distributed environment.

Heterogeneous Services is generally applied in one of two ways:

• Users use an Oracle Transparent Gateway in conjunction with Heterogeneous Services to access a particular non-Oracle system for which the Oracle Transparent Gateway has been designed. (For example, you use the Oracle Transparent Gateway for Sybase on Solaris to access a Sybase database system operating on a Sun Solaris platform.)

• Users use Heterogeneous Services' generic connectivity to access non-Oracle databases through ODBC or OLE DB interfaces.

Heterogeneous Services Process Architecture

Heterogenous Services is composed of two basic components:

• Generic code that is part of the database server and which performs most of the processing for Heterogeneous Services

• Agent generic code that is not part of the database server but which is necessary in order that the Oracle database server be able to communicate with a non-Oracle database system

What is an Agent?

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:

• On the same machine as the non-Oracle system

• On the same machine as the Oracle server

• On a machine different from either of these two

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.

Oracle Transparent Gateways

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.

Figure 1-1 Accessing Heterogeneous Non-Oracle Systems

Text description of the illustration heter001.gif

Generic Connectivity

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.

See Also: Chapter 3, "Generic Connectivity"

Heterogeneous Services Components

Heterogeneous Services provides the following components:

• Transaction Service

• SQL Service

Transaction Service

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.

See Also: "Using Transaction Service Views" for more information on heterogeneous distributed transactions.

SQL Service

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:

1. Translates the Oracle SQL request into an equivalent SQL request for the non-Oracle system.

2. Accesses the non-Oracle data.

3. Makes the data from the non-Oracle system available to the Oracle database server for post-processing.

The SQL service provides the following capabilities:

• Translates Oracle's SQL into a SQL dialect understood by the non-Oracle system.

• Translates specific SQL requests that are made on Oracle data dictionary tables (which tell the Oracle database server how to organize the data in its datastore) into equivalent requests on the non-Oracle system's data dictionary tables.

• Maps non-Oracle system datatypes to Oracle datatypes.

Database Links to a Non-Oracle System

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):

CREATE DATABASE LINK link_name 
  CONNECT TO user IDENTIFIED BY password
  USING 'non_oracle_system';

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:

SELECT * FROM EMP@non_oracle_system;

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.

See Also: Chapter 2, "Managing Heterogeneous Services"

Architecture of the Heterogenous Services Data Dictionary

You can access multiple non-Oracle systems from the same Oracle database server, as illustrated in Figure 1-2.

Figure 1-2 Accessing Multiple Non-Oracle Instances

Text description of the illustration heter002.gif

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.

Classes and Instances

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.

Configuration Data

The Heterogeneous Services data dictionary also contains the following types of configuration data:

• Heterogeneous Services initialization parameters to provide control over various things, including language and date formats, domain names, and Heterogeneous Services tuning

• Capability definitions to identify details such as the SQL language features supported by the non-Oracle data source

• Data dictionary translations to map references to Oracle data dictionary tables and views into equivalents specific to the non-Oracle data source

Data Dictionary Views

The Heterogeneous Services data dictionary views contain information about:

• 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

• Distributed external procedures accessible from the Oracle database server

You can access information from the Oracle data dictionary by using fixed views. The views can be divided into three main types:

• General views

• Views used for the transaction service

• Views used for the SQL service

  See Also: "Using Heterogeneous Services Data Dictionary Views" to learn how to use these views Appendix B, "Heterogeneous Services Data Dictionary Views" for a list of data dictionary views that Heterogeneous Services supports