Even linking databases that share the same architecture is difficult, especially with relational databases. The idea that relational databases are “plug-and-play” is pure fantasy. For example, the popular PowerBuilder application development tool was primarily designed for use with a Sybase relational database. To accommodate PowerBuilder, Oracle had to add an init.ora parameter to cache cursors for PowerBuilder, since the product doesn’t give the user control over specific cursors resulting in many SQL reparses. Locking between Oracle and Sybase is also totally different. If you use Oracle and want to build a high-performance OLTP application, you should first lock all records (which doesn’t exist in Sybase); set a transaction boundary to roll back everything except the locks in case of a transaction error; then perform array operations such as array inserts, deletes, updates, and selects (all of which are not supported by Sybase). Even the SQL syntax differs, and the procedural extensions to each database’s SQL are never equal.

So, how do large IS shops deal with these problems? Should companies invest in training their employees for many different relational databases? The cost of this comprehensive training is difficult to justify.

To further illustrate the stumbling blocks, consider the cost of converting an application from one relational database to another. First, do you even have the talent to make it happen when the interfaces are so diverse? Even more confounding is the lack of clarity about the “truths” of the differences. Rarely is an RDBMS chosen over another on the basis of which RDBMS is best for the type of application.

The challenge of linking multivendor databases is not one of syntax—we can solve that through standard query languages, data dictionaries, and the like. The real challenge is one of semantics: How do we extract meaning from the data contained in the many different locations? I believe that this challenge can only be met by building executable business models that pull related information out of multiple databases as a side effect of their ongoing operations.

Even the major DBMS vendors are recognizing that extensions into other products are necessary for their survival. The crux of the situation is becoming evident: Most shops are not capable of moving their data into a single database.

The Gateway Products

In response to the realities of linking multivendor databases, just about every database vendor has created a tool that claims to allow seamless communications between its engine and other vendors’ products.

For example, Oracle’s philosophy emphasizes the need for a smooth transition path for non-Oracle databases by offering a three-phase program. Oracle recognizes that customers can’t be expected to shift into Oracle overnight, so this gateway strategy allows for a smooth transition into an Oracle environment.

In phase one, Oracle applications provide gateways into non-Oracle databases, allowing Oracle applications to make calls (using Oracle SQL) to non-Oracle databases. The converter then translates the Oracle SQL into the native SQL for the foreign database. Now the developer can use the robust extensions found in Oracle SQL with a non-Oracle database, while the Oracle open gateway relies on “SQL compensation” to perform Oracle SQL functions against the non-Oracle database. This technology would allow an Oracle application to join a DB2 table with a Sybase table—all within the gateway product. Oracle believes in connectivity into all databases—not just relational databases. For this reason, Oracle bundle is packaged with Information Builders Incorporated, using its EDA-SQL product to help insulate the front-end application from the foreign data source. This takes the form of an access manager that handles the communications to and from the Oracle database, allowing Oracle to access nonrelational databases. (See Figure 9.1.)

Figure 9.1  Oracle’s transparent gateway—phase one.

While this approach seems noble, Oracle has experienced some problems with a few of its SQL extensions that do not have equivalents in other relational databases. For example, Oracle’s DECODE function cannot be implemented in Informix, since the DECODE verb has no direct equivalent in Informix SQL. (See Figure 9.2.)

Figure 9.2  Oracle’s open gateway.

Phase two of Oracle’s strategy allows a foreign application to access Oracle. For example, a CICS COBOL customer may require access to Oracle data. With phase two of the Oracle gateway, the non-Oracle application on the mainframe can access Oracle as if it was on a local host. However, some middleware vendors do not feel that the gateway approach is the best long-term solution for database connectivity. Unlike the gateway products that have a single interface, some products have separately tuned drivers for each target database. This movement away from general interfaces such as ODBC is primarily for performance reasons, and it is not uncommon for each product to have its own custom interface for each database that is supported.

Data Replication Products

Phase three of Oracle’s strategy is heterogeneous replication. We now have a solution that allows us to replicate data from a variety of sources and import the table data into Oracle. The Oracle gateway also provides a mechanism to constantly update the replicated non-Oracle data. The gateway approach is commonly used with data warehouse applications, but it also allows updates to non-Oracle data to be quickly transported into a replicated Oracle database, as shown in Figure 9.3.

Figure 9.3  Foreign tables imported into Oracle.

The replication can work in the other direction as well—taking Oracle tables and propagating them into the foreign database where they behave as native tables within the foreign database, as shown in Figure 9.4.

Figure 9.4  Oracle tables exported into foreign databases.

The ability to replicate in both directions gives the database design an enormous flexibility in choosing the best approach to replication based on the needs of the client/server application. However, we need to be aware that these replication interfaces are complicated and often require manual intervention, especially when data is being transferred from one hardware architecture to another. For example, populating EBCDIC data from DB2 tables into the ASCII world of Oracle involves translating characters that are unknown to ASCII, such as the cent sign.