CHAPTER 9
Linking Oracle And Non-Oracle Databases

Now is the time to take a look at the different methods for linking together databases from many vendors. In general, there are three approaches to achieving these links:

•  Via manual consolidation—Data is extracted from a variety of databases and loaded into a common repository, an approach commonly used with data warehouses. Red Brick Systems are an example of manual consolidation.

•  Via remote connection—Many databases are monitored via a common console. In this scenario, status information is shipped from a variety of databases to a common console that is used to alert the operations staff to extraordinary conditions. Patrol by BMC Software is an example of this type of linking.

•  Via online applications—An online application (usually running on a PC) accesses data from a variety of databases, presenting the data to the user as if the data was coming from a single source. EDA-SQL and UniFace are examples of this type of linking.

We will be exploring the following issues:

•  History and evolution of linking multivendor databases

•  The gateway products

•  Data replication products

•  Middleware solutions

•  Remote measurement solutions

The History And Evolution Of Linking Multivendor Databases

You will find two schools of thought about having applications that span across multiple vendor databases. One group maintains that a single vendor should be able to provide a global, omniscient database that is capable of supporting all types of data requirements—from online transaction processing to decision support. Many vendors foster this belief by marketing their database products as suitable for every type of application, pointing out the nightmares that result when a multidatabase application attempts to make diverse databases communicate with each other in a seamless fashion. The opposing philosophy believes that the only way to successfully implement database technology is to use numerous databases, leveraging the strengths of each engine. As recently as five years ago, database vendors touted their products as a corporate panacea, appropriate for all types of data and application systems.

All of this began to change around the time when IBM conceded that its DB2 database was not appropriate for high-volume systems. Prior to this, DB2 was marketed as an all-purpose engine that was suitable for any application, regardless of the size or performance requirements. With time, it became clear that DB2 was not appropriate for systems that required thousands of transactions per second, so IBM began recommending IMS for very large, high-speed databases.

The whole multidatabase trend is a reaction to corporate acquisitions and weak development. Companies have had little or no strategic direction for databases, although it is doubtful that any company ever decided to go heterogeneous, either. Heterogeneous databases mean duplicating licenses and talent, and few companies have a large enough talent pool to support multiple databases. The difference between the engines is too minimal.

From this beginning, other vendors began to embrace the concept of “the right database for the right application.” Systems that required flexibility and ad-hoc query were implemented with relational databases, while systems with complex data relationships and high performance requirements were implemented with network or hierarchical database architectures. New types of specialized database products began appearing on the scene: special word-searchable databases for textual data, databases designed especially for CAD/CAM systems, and so on. Database systems began their long march away from corporate repositories into specialized niche markets, some of which are shown in Table 9.1.

Originally, the desire for a central repository of data was driven by the need to impose some control over data redundancy and multiple updates to the same information placed on various file systems. From this situation evolved our centralized systems, using a single-vendor database. However, end-user computing—and to some degree, slow data transmission—helped to justify to the corporate world a move to client/server platforms. Unfortunately, there has been no standard model for client/server data management. In spite of all the open systems standards (OSF, POSIX, X-Windows, IEEE, and so on), nothing truly solid has emerged for databases. Furthermore, the standards that we do have—such as SQL—are implemented in very different ways. While real-world developers wait anxiously for standards to emerge, they simply make their own decisions on the best DBMS platform. Other departments in the corporate world have followed the same thinking, choosing their idea of the best DBMS, and so on. The results of this scattered approach are islands of information distributed among our LANs and WANs—not only distributed among different file systems on the same box, but distributed across the country and around the world. Linking these databases has become a very complicated but critical chore.

External influences have also impacted the database configuration of shops. Corporate acquisition, mergers, and right-sizing efforts have all had the side effect of leaving a plethora of database engines within the newly reformed organization. Regardless of the wisdom of having multiple types of databases, most IS departments must link more than one database, and cross-database connectivity has become commonplace. Sentry Market Research estimates that the average corporation has approximately nine different databases. End users are demanding applications that integrate legacy data from mainframes with data in open systems, while MIS designers are faced with the incredible challenge of making these diverse systems function as a unified whole.

While linking nonrelational databases such as IMS and CA-IDMS into relational databases is considered challenging, it is a common misconception that relational databases are basically the same and therefore easy to link together.