Oracle and DB2, Comparison and Compatibility/Architecture/Summary

Summary

The introduction of a generalized relational database architecture at the beginning of this chapter serves as a framework for describing the major functional components of relational databases. Each of these major architectural areas, storage, memory and process, are then discussed in more detail for as they pertain to the specifics of Oracle and DB2. Although there is a great deal of difference in the implementation at the actual database code level, the way that the databases work is very similar. This is not surprising since they use standard tried and true computer science techniques for memory management, the structures described in Dr. Codds seminal work for handling relational data, and are both implementing the same basic functionality. It can be seen that both use memory allocated at the Instance, Database and Application level, and that structures implemented in these memory areas depends on whether the database is stand alone (Dedicated) or shared (Partitioned). The same techniques are used for storing disk data in memory (the buffer pool) and the buffer pool is serviced by similar LRU algorithms in both cases.

The architectures described in this chapter are necessarily simplistic. This chapter is intended to give a fairly detailed functional overview of Oracle and DB2, but some disk resident files, memory structures and processes have been omitted for the sake of clarity. Important instances not included in this architectural overview will be introduced in later sections where appropriate, and the general architectures used here will be referred back to for context. Oracle and DB2 are complex and sophisticated products, and there are structures and processes that are specific to different options and features in each database. Some of these will be covered in later sections, but the primary intent of this section is to introduce general concepts around how each DBMS implements and manages the common structures and processes necessary for basic database operation (reading and writing data from memory to disk and vice versa, logging changes, handling user connections, locking objects in the database and managing the buffer pool).

Professionals familiar with Oracle and /or DB2 can easily identify components specific to both, and additional structures unique to each. One of the great strengths (and weaknesses) of software is that there are often many different ways of implementing the same functionality. These differences are usually described by the database vendors as ‘competitive differentiators’; the reason why their chosen implementation is superior.

The reason for staring with a generalized architecture is so that people familiar with only one database can see how the other database implements the same functionality – the English to Spanish translation if you wish. The objective of this book is not just to compare and contrast database implementations, it is to describe how Oracle Compatibility is achieved in DB2, and to do this it is necessary to see how each handle implementation of functionality that we need to be equivalent in both.

For organizations wishing to change their database, architectural discussions of similarities and differences are just a starting point. There necessarily needs to be a detailed comparison of all similarities and differences because no two Oracle or DB2 implementations are the same. Different organizations implement different features and options, and changing databases is a significant understanding. It is Murphys law that some overlooked difference at the beginning of this process will be the undoing of the whole project, so it is important that all differences are covered as completely as possible so that you can start with the answers to two very important questions:

a) Have we identified the differences that are important to us, and

b) Have we identified all of them?

All relational databases store user data as rows and columns in tables. This structure and its access methods are described by Dr. Codds seminal paper in 1970. The operation of the database is handled by software. Since the model is essentially the same, and software is flexible, it would seem that the data and DBMS should be interchangeable. As database functionality evolved, implementation differences of common functions, and the addition of valuable, but proprietary functionality has meant that database implementations have diverged, and now it is not simply sufficient to know how a database works, but also who’s database it is you are working with. Since the original database implementations, there have always been database migrations – the moving of data from one vendors database to another. This involves comprehensive data manipulation, called Extract, Transform and Load (ETL). Once the data lands, there is another exercise that needs to be undertaken, the modification of the application logic (both in user programs, and in stored procedures, triggers, rules and functions within the database) to have your systems work with the newly migrated database.

The next logical step in this process is Compatibility, and the ultimate goal of this is that data can be unloaded from one database and loaded into another without having to worry about manipulating incompatible data structures, and that the applications that access this data simply have their connections routed from the original database to the new database. This approach is achieved by vendors implementing competitive vendors’ features and functionality. The objective is to make either wholesale moving of databases, or interoperability of heterogeneous databases seamless. While there certainly are projects that have gone this smoothly, all of them require the following:

• An understanding of the implementation details of the source database.

• An understanding of the implementation details of the target database.

• An inventory of the features used in the source database system (data structures and application logic)

• A mapping of structures in the source database to the target.

All user implementations are different, they use different subsets of database functionality and not all of this functionality is directly equivalent between the databases.

The remaining sections of this Wikibook provide a more comprehensive list of the implementation details of Oracle and DB2, using the framework of the Memory Model, Process Model and Storage Model for comparison.