6
Oracle Heterogeneous Services Concepts

This chapter describes the basic concepts of the Oracle Heterogeneous Services. Topics include:

• What is Heterogeneous Services?

• Types of Heterogeneous Services

• Heterogeneous Services Process Architecture

• Architecture of the Heterogeneous Services Data Dictionary

  See Also: Getting to Know Oracle8i for information about features new to the current release.

What is Heterogeneous Services?

Heterogeneous Services (HS) is an integrated component within the Oracle8i database server, and provides the generic technology for accessing non-Oracle systems from the Oracle database server. Heterogeneous Services enables you to use:

• Oracle SQL statements to transparently access data stored in non-Oracle systems as if the data resided within an Oracle database server.

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

To access a non-Oracle system, you need to use a complementary Heterogeneous Services agent. You can connect to a non-Oracle system through an Oracle Transparent Gateway, which is an agent that is tailored specifically for the system that you are accessing. If you connect to the non-Oracle system using generic connectivity through the ODBC or OLE DB interfaces, however, then the agent is an executable that it automatically installed with the Oracle database server.

Note: The phrase non-Oracle system denotes both non-Oracle data stores (or databases) that are accessed using SQL, and systems that are accessed procedurally.

Database Links to a Non-Oracle System

Heterogeneous Services makes a non-Oracle system appear as a remote Oracle database server. To access or manipulate tables or to execute procedures in the non-Oracle system, 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 HS 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 that 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.

Heterogeneous Services Agents

While Heterogeneous Services provides the generic technology in the Oracle8i server, a Heterogeneous Services agent is required to access a particular non-Oracle system such as Informix or Sybase. Oracle Corporation provides Heterogeneous Services agents in the form of Oracle Transparent Gateways version 8 and higher.

Oracle Transparent Gateways is one family of products that uses the Heterogeneous Services. Generic connectivity is another family of agents based on Heterogeneous Services. The phrase Heterogeneous Services agents denotes all products that are based on Heterogeneous Services, including Oracle Transparent Gateways and the generic connectivity agents.

See Also: Chapter 7, "Managing Oracle Heterogeneous Services Using Transparent Gateways" to learn to configure Oracle Transparent Gateway agents, and Chapter 8, "Managing Heterogeneous Services Using Generic Connectivity" to learn to configure generic connectivity agents.

Types of Heterogeneous Services

Heterogeneous Services provides the following services:

• Transaction Service

• SQL Service

Transaction Service

The transaction service allows non-Oracle systems to be integrated into Oracle 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 session 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. If the non-Oracle system supports some but not all aspects of the two-phase commit protocol, then the Oracle database server typically supports distributed transactions with the non-Oracle system (with possible restrictions).

The SQL service uses the transaction service. Oracle's object transaction service uses agents that implement only the transaction service.

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

SQL Service

The SQL service transparently accesses the non-Oracle system using SQL. If an application's SQL request requires data from a non-Oracle system, HS performs the following steps:

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

2. Accesses the non-Oracle data.

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

The SQL service provides capabilities to:

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

• Transform SQL requests on Oracle's data dictionary tables to requests on the non-Oracle system's data dictionary

• Map non-Oracle system datatypes onto Oracle's datatypes

Heterogeneous Services Process Architecture

The basic architecture for HS involves a client accessing an Oracle database server, which in turn sends a request to an agent residing on a non-Oracle server. This section contains the following topics:

• Transparent Gateways

• Generic Connectivity

Transparent Gateways

A transparent gateway, also called an agent, is required to access a specific non-Oracle system from an Oracle8i server. The Oracle database server communicates with the agent, which in turn communicates with the non-Oracle system. Unless you are using the generic connectivity feature of Oracle, you need to purchase and configure a system-specific agent to connect to the non-Oracle data store. For example, a dBASE data store requires a dBASE agent, and a Sybase data store requires a Sybase agent.

With transparent gateways, you can easily access data anywhere in a distributed system without knowing the location of the data or how it is stored. The term transparent indicates that the network, location, operating system, data storage format, and access methods are hidden from the user application.

As illustrated in Figure 6-1, agents can reside on the same machine as the non-Oracle system, but are not required to. The agent can also reside on the same machine as the Oracle8i server, or even on a third machine. The agent must be:

• Accessible by the Oracle8i server through Net8.

• Able to access the non-Oracle system using a non-Oracle system-specific communication mechanism.

When a user session accesses a non-Oracle system through a database link on the Oracle8i server, a Net8 Listener starts an agent process. This agent process remains running until the user session is disconnected or until the database link is explicitly closed.

Figure 6-1 Accessing Heterogeneous Non-Oracle Systems

Generic Connectivity

If you connect to a non-Oracle data store using generic connectivity, the process architecture is essentially the same as in the non-generic case. The difference is that Oracle provides generic ODBC and OLE DB agents with the server--no transparent gateway is required. As long as the non-Oracle system supports these protocols, you can use them without purchasing a system-specific agent. To make the generic agents work, however, you must also configure a driver that can interface with the agent.

See Also: Chapter 8, "Managing Heterogeneous Services Using Generic Connectivity" for information about installation and configuration of generic connectivity.

Architecture of the Heterogeneous Services Data Dictionary

Each non-Oracle system accessed from an Oracle8i server is considered a non-Oracle system instance and class. You can access multiple non-Oracle systems from the same Oracle8i server, as illustrated in Figure 6-2.

The Oracle8i server must know the non-Oracle system capabilities (SQL translations, data dictionary translations) for each non-Oracle system that it accesses. This information is stored in the Oracle8i data dictionary.

Figure 6-2 Accessing Multiple Non-Oracle Instances

Classes and Instances

If this information were stored separately for each non-Oracle system you access, the amount of stored data dictionary information could become large and sometimes redundant. For example, when you access three non-Oracle system instances of the same type, the same capabilities, SQL translations, and data dictionary translations are stored.

To avoid unnecessary redundancy, Oracle organizes this data by classes and instances in the data dictionary. A class defines a type of non-Oracle system. An instance defines specializations of a class for a specific non-Oracle system. Note that instance information takes precedence over class information, and class information takes precedence over server-supplied defaults.

If you access multiple non-Oracle systems of the same class, then you may want to set certain information, such as initialization parameters, at the instance level. Heterogeneous Services stores both class and instance information. Multiple instances can share the same class information, but each non-Oracle system instance has its own instance information.

Consider a case where the Oracle8i server accesses three instances of type Megabase release 5 and two instances of Megabase release 6. Suppose Megabase release 5 and Megabase release 6 have different capabilities. The data dictionary contains two class definitions, one for release 5 and one for release 6, and five instance definitions.

Data Dictionary Views

The Heterogeneous Services data dictionary views contain information about:

• Names of instances and classes uploaded into the Oracle8i 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 Oracle8i server

You can access information from the Oracle data dictionary used fixed views. The views can be divided into four main types:

• General views

• Views used for the transaction service

• Views used for the SQL service

  See Also: "Using the Heterogeneous Services Data Dictionary Views" to learn how to use these views, and Appendix B, "Data Dictionary Views Available Through Heterogeneous Services" for reference information on the views.