IMS Hierarchical Database Oracle Tips by Burleson Consulting

High Performance Data Warehousing

Early Commercial Databases

The problems associated with flat-file systems led to the development of the first commercial database--IMS (Information Management System), from IBM. IMS is considered a hierarchical database, meaning that pointers are used to establish the data relationships. IMS remains very popular today, and it is well suited for modeling systems in which the entities are naturally hierarchical in nature. The data relationships are established with child and twin pointers, and these pointers are embedded into the prefix of every record in the database (see Figure 1.5).

Figure 1.5 Hierarchical databases.

Many data warehouses are populated from data extracted from IMS databases, so a discussion of the internals of IMS will help the warehouse administrator develop a plan for extraction from IMS.

While IMS is very good at modeling naturally hierarchical data relationships, complex data relationships, such as many-to-many and bill-of-materials relationships, have to be implemented in a very clumsy fashion, with the use of phantom records. The IMS database also suffers from its own complexity. Learning to program and administer an IMS database requires many months of training, and, consequently, IMS development remains very slow and cumbersome.

It is interesting to note that while IMS is considered a dinosaur by today's computer standards, IBM continues to sell new copies of IMS, and IMS is still used by hundreds of corporations that require high-speed Online Teleprocessing (OLTP) applications. While some of these systems are legacy systems, which are not easily converted to modern technology, many corporations continue to use IMS because of its high speed. A hybrid of IMS, called IMS/FASTPATH, is one of the fastest commercial databases available, even by today's standards. IMS/FASTPATH is used at companies that may have hundreds, or even thousands, of concurrent transactions, and some IMS configurations have surpassed the 1000 transactions per second barrier.

As fast as IMS is for highly normalized OLTP systems, the de-normalized nature of data warehousing makes much of the fast access of IMS unusable for data warehouses. Consequently, the hierarchical database model remains firmly entrenched in OLTP applications.

---

This is an excerpt from "High Performance Data Warehousing", copyright 1997.

If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts.  You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.