Chapter 8
Coexisting with Non-Oracle Databases

by Advanced Information Systems, Inc.

In This Chapter

•  Sleeping with the Enemy

•  The Gateway to Everywhere

•  Coexistence and Replication

Sleeping with the Enemy

Although any database vendor will tell you that his database should be running 100 percent of your systems, this isn’t usually the case. In the first place, converting all a company’s systems to one database at once will put too much strain on the existing resources. Secondly, it might not be logical to move all the systems to one database. Different applications demand different types of database software. This is why in most large or medium corporate operations, you need to connect disparate database software.

Because of this phenomenon, you might be in a situation where you purchased an Oracle RDBMS, and you have a number of separate systems that need to be interfaced to Oracle, systems that have major differences in their environment from yours. Here are some of the differences:

•  Different operating systems

•  Different hardware vendor

•  Different RDBMS vendor

•  Different paradigms

A project team and an RDBMS software application might need to contend with these four areas when dealing with outside systems.

Different Operating Systems

This is a more difficult obstacle if these two systems do not talk to each other for any other reason. In this case, linking a system with a different OS than your Oracle database means that your project needs to establish the links from the lowest networking level to the level of screen interfaces. Different operating systems can have many subtle differences.

Picture a fictitious airline that purchased Oracle as its Web server. Soon customers are ordering flights over the Web and querying flight information. This information processing has been traditionally performed on a huge mainframe, but now this new Oracle database needs to connect to a different mainframe. Say that the database is running UNIX. This is a classic example of two very different operating systems that many times need to talk to each other.

The situation can be compounded with three operating systems that need to communicate database application logic. If this fictitious airline purchased a huge Windows NT cluster for a data warehouse, you would need to send data directly to Windows NT from both the UNIX and MVS operating systems as shown in Figure 8.1.

Figure 8.1.  A corporate environment usually has many generations of different hardware and operating systems.

Connecting three databases over different operating systems is not inherently more difficult, but managing the underlying connection between the OSs and any data translation is.

Different Hardware Vendors

The complexity of a database gateway over different hardware systems isn’t drastic if you run the same operating system. If you run the same operating system and the same database, the differences are minor. If you are running different RDBMS products by different vendors, the complexities increase.

Looking at the Microsoft NT operating system, differences in hardware evaporate when the Windows NT services are in place because Windows NT is the product of one vendor. UNIX, however, differs more between hardware vendors; therefore, linking a SUN SPARC to an HP-9000 does involve the consideration of minor operating system differences or kernel configuration differences.

Different RDBMS Vendors

Even when you solve any lower-level adjustments that you must make to realize communication between two databases on two separate machines, you still might be running two different RDBMSs. For instance, if you run Oracle on one Windows NT machine and SQL Server on another Windows NT machine, you need a translation process when sending transactions from one machine to another. Most of gateway theory as related to this chapter concentrates on the similarities and differences between databases and common ways they connect with one another.

Different Paradigms

The first three differences listed between an Oracle RDBMS and another system were physical and objective in nature. Yet this final difference refers to the paradigms of the different human groups that manage applications and must talk to one another. The way that hardware architecture, a database, or an application are built relate very closely to this paradigm.

For instance, if your system was built using an object-oriented methodology, yet the system that you must interface with was not, you need more components for a successful interface. Furthermore, the most efficient solution might not be to force another system to think like you do but instead learn to think like the other system when communicating with it.

You see this paradigm split most commonly between mainframe systems and more open systems. Both systems have been successful doing certain tasks but need to talk to each other. In this case, if you use Oracle, you are probably on the “open” side wondering how you are going to communicate with a mainframe. Suddenly, your “open” system doesn’t appear very open when it needs to interface with a legacy mainframe.

If the paradigm of the people who manage the mainframe is similar to yours, in that they might also use another relational database such as DB2, you can communicate and build bridges rather quickly. If they come from a completely different paradigm of VSAM files or IMS, it will be harder to map their data to yours because both data sets are structured from different paradigms.

It is important for an Oracle project to have flexible methods for propagating data to other systems that add value to the Oracle system. Many times, your Oracle system will have to conform to the data structure of the system to which you need to interface. This will probably involve many table joins and other complex SQL to “flatten” and denormalize your data for a mainframe dataset.

Your system interface will need to communicate to a legacy system from which it is very different. But this foreign system also needs to talk to your system. Both systems need to understand the other’s protocol to communicate through a network. At a higher level, both databases on these two systems need to talk to each other and understand a part of the other’s language.

Instead of building this interface from scratch, you can purchase products that connect networks, isolating you from the problems of protocol differences. The Oracle network offering that isolates you from different protocols is SQL*Net. Likewise, you do not need to build the database interface; you can purchase one. This interface between two different databases is a gateway.