The Gateway to Everywhere

When you enter the world of gateways, you soon realize from all the vendors selling them that your database can talk to just about anything from a mainframe to a mountain goat. Therefore, you must determine which gateway you need and which gateway will be most cost-effective given your budget and resources.

What Kind of Gateway Do You Need?

Gateways can be broken up into three major areas:

•  Transparent Gateways—These are the top-of-the-line gateways where the interface is so perfect that your Oracle systems believe that they are talking to other Oracle instances, when they are communicating with different databases on different platforms: The gateway “tricks” the Oracle instance to think that another remote foreign database is actually an Oracle database, as in Figure 8.2.

Figure 8.2.  Oracle’s Transparent Gateway appears as an Oracle database to the other Oracle databases it is linked to.

•  Procedural Gateways—At this level, the Oracle database does not believe that it is talking to another Oracle database, but instead the database can communicate to a set of libraries written in either C or PL/SQL. These libraries provide a set of calls that need to be used explicitly when linking any portion of one database’s activity to another.

•  Passive Gateways—Most of these gateways are homegrown and involve moving data through a series of steps, first moving data to another machine, and then at a point in time having the other system read in the data. The gateway is passive because there is no direct communication between database systems. Data is simply being sent from one database to the other by an independent set of processes.

Now consider how these three forms coexist with your Oracle databases and other “foreign” databases. As you can probably guess, as you move from Passive Gateways up again to Transparent Gateways, the cost of them becomes greater as functionality and ease-of-use increase. You incur a greater cost for a more sophisticated gateway, so unless that sophistication adds value, the most profitable solution might be a less expensive gateway.

Transparent Gateways

As defined in the previous section, a Transparent Gateway “tricks” the Oracle instance into thinking that an Oracle machine exits remotely when, in fact, it is a completely different database offering. Here are some of the major databases where you can purchase this type of interface to Oracle:

•  Microsoft SQL Server (offered free sometimes)

•  Informix

•  Ingres

•  Sybase

•  IBM DB2

•  EDA SQL

Levels of Transparency Exhibited by the Oracle Transparent Gateways

These Transparent Gateways are indeed transparent to the Oracle RDBMS, and they can act as just another database. Oracle’s offerings for these gateway products are called the Oracle Transparent Gateways. These software products enable a foreign database to appear as an Oracle database and make the interface transparent to the users.

Access Method Transparency

This means that just as Oracle access is SQL-based, a user can use SQL on the gateway to retrieve data from a non-Oracle database using SQL just as if it were an Oracle database. The only catch with this is performance. Regardless of the gateway’s speed, if data on the remote machine is not indexed or sorted, the same SQL runs on the remote machine, and the local Oracle machine might vary a great deal in the time that it takes to retrieve the data.

Location Transparency

This simply is an extension of Access Method Transparency in that not even the physical location of the data needs to be known by the user. Oracle’s gateway will map a request to the different physical machines and databases on which the data resides.

One note here: To realize Location Transparency, an administrator needs to set up SQL*Net on both machines and create the proper database links, grants, and synonyms.

Network Transparency

This really should be called SQL*Net Transparency. You still need Oracle’s standard network layer running over all your different networks to shelter each database from network differences and to provide a common protocol. SQL*Net is the portion of the gateway that handles network communication. When SQL*Net is working, there is network transparency between databases.

Operating System Transparency

This means that for database functions, users do not have to be concerned with which operating system the two different databases reside on.

Benefits of Oracle’s Transparent Gateways

As mentioned earlier, the Oracle Transparent Gateway is a top-line solution. It makes coexistence between an Oracle database and a non-Oracle database easier to manage. The potential buyer of these expensive systems should first ponder the benefits of this solution to determine whether the extra cost spent will add enough value to make the gateway profitable.

Full SQL Support

Advanced Oracle extensions to ANSI SQL such as the Outer-Join operator or the Decode function might be used to access a remote database that does not even use SQL.

Full PL/SQL Support and Trigger Support

This is an important feature, in that you can now code referential integrity triggers that can create streams of business logic between two remote machines, and enforce table uniformity between two databases.

For instance, as in Figure 8.3, you can have an Order/Entry system residing on Oracle. Whenever an order is shipped, a trigger can fire out to the remote table on the other database and subtract from the inventory or the warehouse from which the goods will be shipped.

Figure 8.3.  With Oracle’s Transparent Gateway, developers can use common tools such as PL/SQL to communicate to totally non-Oracle systems. The gateway handles the translation.

Distributed Transactions Use Two-Phase Commit

This refers to a change in data that must take place on two or more machines, as seen in Figure 8.4. Data is committed in the usual way on the first machine and then sent across the network and the gateway to the next machine. Not until the remote commits are all complete is the transaction considered final. This is useful for tables on two different databases that need to be replicated and/or need to always be synchronized.

Figure 8.4.  A simple request to ship an order results in a two-phase commit that will not be successful unless the mainframe database confirms that inventory exists for the shipment.

In the previous example, if you never wanted a sale to be shipped until you were sure that you subtracted it from the inventory, you would use the two-phase commit in the following way.