|Oracle8i Distributed Database Systems
This chapter describes the basic concepts of the Oracle Heterogeneous Services. Topics include:
For information about features new to the current Oracle8i release, please see Getting to Know Oracle8i.
Heterogeneous Services is an integrated component within the Oracle8i server, and provides the generic technology for accessing non-Oracle systems from the Oracle server. Heterogeneous Services enables you to:
To access a particular non-Oracle system, you will need a complementary Heterogeneous Services agent.
The phrase "non-Oracle system" denotes both non-Oracle datastores (or databases) that are accessed using SQL, and systems that are accessed procedurally.
While Heterogeneous Services provides the generic technology in the Oracle8i server, a Heterogeneous Services agent is required to access a particular non-Oracle system. Oracle Corporation will provide Heterogeneous Services agents in the form of Oracle Open Gateways version 8 and higher.
Oracle Open Gateways is one family of products that will use the Heterogeneous Services. Other products that are based on Heterogeneous Services are being developed. These products, developed by Oracle or third-parties, may not be part of the Oracle Open Gateways family of products. We use the phrase "Heterogeneous Services agents" to denote all products that are based on Heterogeneous Services, including Oracle Open Gateways.
Heterogeneous Services provides three services:
The transaction service allows non-Oracle systems to be integrated into Oracle transactions and sessions. Users transparently set up an authenticated (i.e. username and password) 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 will access the non-Oracle system to transparently coordinate the distributed transaction. In fact, the Oracle server will support distributed transaction with the non-Oracle system, even if the non-Oracle system itself does not support two-phase commit.
Both the SQL service and procedural service use the Transaction service. Oracle's object transaction service will use agents that only implement the transaction service.
See "Views for the Transaction Service" for more information on heterogeneous distributed transactions.
The SQL service is used to transparently access the non-Oracle system using SQL. If an application's SQL request requires data from a non-Oracle system, Heterogeneous Services translates the Oracle SQL request into an equivalent SQL request understood by the non-Oracle system, accesses the non-Oracle data, and makes the data available to the Oracle server for (post) processing.
The SQL service provides capabilities to:
Heterogeneous Services enable users to access any procedural non-Oracle system, such as messaging and queuing systems, from an Oracle8i server. The non-Oracle system is called from the Oracle server using a PL/SQL remote procedure call (RPC). Heterogeneous Services translates the PL/SQL call into a procedure or function of the non-Oracle system.
With the procedural service you can create distributed external procedures, that enable you to call third generation language (3GL) routines from PL/SQL. Like PL/SQL external procedures, the distributed external procedure maps PL/SQL procedure and function names and arguments onto 3GL routine names and their arguments. Both external procedures and distributed external procedures use the same mechanisms to call 3GL routines from PL/SQL. External procedures are designed to perform special purpose tasks that are local to the Oracle8i server, whereas distributed external procedures are designed to access non-Oracle systems. The primary differences between distributed external procedures and external procedures are:
PL/SQL external procedures, are covered in the PL/SQL User's Guide and Reference.
Heterogeneous Services makes a non-Oracle system appear to be a remote Oracle server. To access or manipulate tables or to execute procedures in the non-Oracle system, you simply create a database link. Tables and procedures at the non-Oracle system can be accessed by qualifying the tables and procedures with the database link. This is identical to accessing tables and procedures at a remote Oracle server.
If a non-Oracle system is referenced, Heterogeneous Services will translate the SQL statement or PL/SQL remote procedure call into the appropriate statement at the non-Oracle system.
Consider the following example that accesses a non-Oracle system through a database link:
SELECT * FROM EMP@non_Oracle_system;
Heterogeneous Services will translate the Oracle SQL statement into the SQL dialect and execute the SQL statement at the non-Oracle system.
An agent is required to access a particular non-Oracle system from an Oracle8i server. The Oracle server communicates with the agent. The agent communicates with a particular non-Oracle system.
As shown in Figure 5-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 it can even reside on a third machine. The agent must be accessible by the Oracle8i server through Net8, and the agent must be 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.
Distributed external procedures map PL/SQL procedures onto remote 3GL routines that reside in a dynamic linked library (DLL). Whenever a distributed external procedure is executed, the agent will load the operating system dynamic linked library that contains the 3GL routine into the agent process, map the PL/SQL procedure onto the 3GL routine, and invoke the 3GL routine. After the 3GL routine finishes processing, the arguments and return values are passed back to the calling PL/SQL program. See Figure 5-2.
To access a non-Oracle system using a distributed external procedure , you need an agent specifically designed for that non-Oracle system . The agent contains non-Oracle system-specific code which sets up a session at the non-Oracle system, and integrates the transactions performed at non-Oracle system by the distributed external procedure into an Oracle distributed transaction.
For example, you have an agent that provides access to a queuing system. To put a message into the queue, an Oracle application issues the following statement:
SQL> EXECUTE enqueue@queuing_system('We are out of stock'); SQL> COMMIT;
The enqueue procedure resides in a dynamic linked library. When you execute the statement above, the Net8 listener spawns the agent process. The agent process loads the DLL containing the enqueue procedure, and executes the enqueue procedure to put a message in the queuing system. When you COMMIT the transaction, the agent will ask the queuing system, on behalf of the Oracle server, to commit the transaction.
The agent process continues running for the duration of the Oracle user session, or until you close the database link explicitly by using the "ALTER SESSION CLOSE DATABASE LINK queing_system" command.