Cost-Based Distributed Joins

If you use cost-based optimization and have run the proper SQL to populate Oracle’s internal tables, Oracle can choose a join across a distributed network in an efficient way.

Transparent Schema Mapping

This refers of the gateway’s capability to view a foreign database’s tables and columns in the same way that Oracle tables and columns are defined. This includes mapping of dates, characters, and LONG data. This transparency can provide a seamless mapping between an Oracle character string and a DB2 character string residing on MVS. To do this, you need to account for vendor differences in data storage between DB2 and Oracle and convert to the ASCII character set from the mainframe EBCDC character set.

Transparent Views of Data Dictionaries

According to Codd’s original writings on Relational Theory, a relational data dictionary appearing as tables to the user is a vital aspect of a relational database. Oracle’s gateway provides this illusion, so a remote non-Oracle schema, at least in part, appears to the user as an Oracle data dictionary.

Procedural Gateways

Procedural Gateways can be as powerful as Transparent Gateways; the only catch is that the logic linking Oracle to a non-Oracle database has to be explicitly coded. These are ideal gateways if you have, or are willing to purchase, the in-house talent to use these “procedures” to create a gateway.

What Is This Thing? You Mean I Have to Do Work?

Procedural Gateways don’t make everything better right away like Transparent Gateways. Transparent Gateways sit both at your Oracle computer’s site and at the remote site and translate what your database is doing to the language of the remote database. A Procedural Gateway does the same thing, but you need to invoke it from your applications manually. Think of a Transparent Gateway as a brand-new bicycle that you pick off the shelf, whereas a Procedural Gateway is a bike that you buy and need to assemble at home.

A Procedural Gateway is named such because what you are buying is a gateway for procedures. This is a gateway that enables communication through procedures that you must write to invoke a remote non-Oracle database.

The Oracle Procedural Gateway utilizes the Advanced Program to Program Communication (APPC) protocol. This means that your Oracle interface programs can speak to mainframe programs that don’t use Oracle but have access to the APPC, which is a popular mainframe protocol.

With the APPC Oracle Procedural Gateway, you can communicate with the following Mainframe tools:

-  CICS/MVS

-  IMS/TM

-  CA-IDMS

-  CICS/VSE

-  CICS/400

-  REXX (for the REXX fans in the audience)

This means that within your CICS code, you can call these procedures to communicate with a remote non-MVS Oracle database. With the APPC, you can also communicate with the following databases and file systems:

-  DB2

-  IMS

-  CA-IDMS

-  ADABAS

-  VSAM

Oracle uses the X/Open Common Programming Interface for Communication. This makes the Oracle gateway very flexible because this is a common mainframe communications standard.

A Day in the Life of a Procedural Gateway

When using a Procedural Gateway, refer to the Web-based frequent-flier database (Figure 8.5). Reward your airline patron with a free flight. Assume that you cannot book flights over the Web. After you determine that your traveler has the necessary miles, call a PL/SQL procedure to book the flight on the mainframe. At this point, the procedure flies through SQL*Net to the Oracle Procedural Gateway waiting for you on the ancient mainframe. The gateway builds an APPC transaction and routes it through the SNA (mainframe) network. The response is returned via SQL*Net again to your application. This means that someone on the Web can cash in on free miles and book a flight on a mainframe. Here is how the whole “procedure” looks.

Figure 8.5.  With a Procedural Gateway, all you need to do is build the logic; the gateway takes care of all networking and request translations needed by the non-Oracle database and hardware environment.

IBM’s MQSeries

If you use IBM’s MQSeries messaging products, Oracle also has a Procedural Gateway that links a mainframe or newer IBM product using this protocol for databases on another system. Oracle’s procedure library supplies you with PL/SQL when you buy this gateway, so your development staff does not need to learn a new set of APIs.

With these procedure calls, you can employ logic affecting a remote mainframe through PL/SQL, database triggers, Oracle Developer/2000, and third-party tools. This tool even enables you to build distributed databases or, at least, distributed tables because the MQSeries messaging supports two-phase commits. You can have your frequent-flier database share a table called REWARDS that exists on the mainframe and on your Oracle instance. With the MQSeries gateway and the two-phase commit, you can ensure that these tables remain in sync.

Why Purchase a Procedural Gateway?

Because you understand that Transparent Gateways ensure that your non-Oracle databases appear as Oracle databases, thus leaving you with an integrated database environment, it might be hard to understand why you might trade that power for Procedural Gateways and only the ability to create data routines that link your Oracle database to a remote one. Well, the simple answer is price. Procedural Gateways cost much less.

It is important when considering the cost of a gateway to factor in development time. Even if a Procedural Gateway costs less, the price of developing the routines to make it work might exceed the price of the Transparent Gateway. It is also important to realize that you will need to purchase SQL*Net that links your Oracle network to the remote network; this cost can be high if you price for a large mainframe. If you need only a few simple interfaces between your mainframe and your Oracle database, maybe both the preceding gateway solutions are not correct and all you need is a Passive Gateway (discussed in the next section). This gateway has virtually no costs outside of development; yet complex functionality such as replication, high availability, and distributed data would be tedious. That is why these higher-level gateway solutions exist—they give us solutions for complex problems.