Database Considerations

When you cross back into the past and look at earlier database forms, you might at first think how different they are from one another. Say that you are migrating data from an IMS database to an Oracle database. This means that you’re crossing the bridge between the hierarchical model and the relational model and are in need of a complex translation between both worlds.

Many times, items in a hierarchical model, or in any earlier form, data is not in Third Normal Form. This is the standard form you try to approach when designing a relational database. Data tends to be flatter, containing attributes of many keys. Most times, when you migrate from a mainframe to Oracle’s RDBMS, if you chose to normalize, you would end up with more tables on the new target system. This is important to realize when planning to migrate your data; the mapping of data won’t necessarily be a one-to-one process (see Figure 7.8).

Figure 7.8.  Most mainframe downsizing efforts to Oracle involve a complex mapping of data.

A mainframe-to-Oracle downsizing effort usually involves a great deal of data. Mainframes pride themselves on the capability to store large amounts of information and handle a large number of users. It is important not to blindly rush into a downsizing effort just to obtain a new technology. Many times, the amount of data you see on a mainframe will involve more complex disk strategies, such as the purchase of RAID technology. From the database perspective, this amount of data might require more advanced Oracle strategies, such as clustering and/or parallel processing, to handle all the requests the mainframe did (see Figure 7.9).

Figure 7.9.  In many cases, downsizing large mainframe applications require you to employ advanced technology to realize the same performance on a “smaller,” open system.

The actual volume of data that needs to be moved from the mainframe to the newer system needs to be taken into consideration and estimated. If you have enough space, you can set up a “staging area” for the new data (see Figure 7.10).

Figure 7.10.  If you have the space, a staging area allows you to load data into Oracle as you receive it.

Normally, a staging area consists of a set of Oracle tables that are designed around the flat files received from the mainframe. Data is loaded into the staging area as it is received—in the format of the flat files from the source database.

Loading the data into a staging area allows you to write simpler data transfer routines from the mainframe to the new system (see Figure 7.11). Data does not have to be massaged and mapped into the new normalized Oracle format, but simply loaded one flat file at a time. This allows the use of faster tools, such as SQL*Loader, and enables you to verify the quality of your mainframe data stored now in your Oracle database.

Figure 7.11.  Coding data translation to read from an Oracle staging area gives a wider range of tools and greater ability to check for mistakes.

Once the data is in the staging area, you can write routines to select the data and move it into the new Oracle schema. Data can be “scrubbed” and manipulated directly from an Oracle database, instead of a mysterious flat file you receive from the mainframe.

The only problem with a staging area is that you need to duplicate at least a portion of the data twice in your Oracle database. If you have a 1-terabyte mainframe application, a full staging area for the mainframe data and an area for the new Oracle database would take up 2TB!

Of course, you can plan out your staging area in steps if you have limited space, or you can decide not to use a staging area and therefore spend more time developing routines to convert data from flat files directly into the new normalized format. It is a trade-off between disk space versus the cost and time to write a more complex single-step conversion from the mainframe to Oracle.

If you decide to perform a one-time data conversion from a mainframe to an Oracle system, first download the flat files and transfer them to the newer system. After this is done, you would use a structured language such as C++ or Java to read data in from a file and perform the complex mapping and data conversion routines. Data can then be loaded into new files mapped to your normalized target tables, or be inserted directly into the database through the language’s SQL interface.

Without a staging area, you don’t have the luxury of trapping data and number conversion errors in your intermediate tables (see Figure 7.12). You also are not able to use a simpler and faster tool such as SQL*Loader, needing, instead, to encapsulate all of your logic in one set of programs that load data.

Figure 7.12.  Without a staging area, all of the logic and error-handling need to be coded into load routines that perform the complex translation.

Remember that if you choose to write your own one-step load, record logs and error logs will have to be manually created to handle data exceptions.

Management Considerations

The management of the migration process in a downsizing effort becomes an art in itself. Many companies totally split the functionality of those who manage the older mainframes and those who work with newer, open systems. This philosophy, from the start, increases the chances of a power struggle.

Often the legacy system group resents that their tasks are being taken away and given to people who use a newer technology. You must assure your employees that they are not obsolete just because they use older technology. It is desirable in this market to retrain computer talent, considering the great shortage. If you can help move the legacy team into new positions in your company, you reduce the chance they will hold back the migration.

The group receiving the data and managing the new Oracle system must also be flexible. Data often will not come in an ideal format for Oracle, but Oracle probably has the built-in SQL or PL/SQL to make the conversion easier. There is no need to force the mainframe group to massage all of the data using tools like COBOL. With a well-thought-out plan, many of these tasks are quicker to do in Oracle than on the mainframe. This is why it is ideal to have at least a partial staging area. Other tasks, like sorting data and removing packed-decimal data, still need to be performed by the mainframe group.

As manager of a downsizing effort, it’s also good to migrate your system in logical pieces rather than all at once. You can solve many technical issues and improve your project plan once you have completed even a partial transfer of data from the mainframe to Oracle. This will allow you to improve and refine your migration process.