Symposium talks tackle migration views

Macro, micro outlooks emerge in presentations

By Steve Hammond

What’s the difference between a Canada goose and an HP 3000 user? The goose thinks about migrating twice a year, but the HP 3000 user has to be thinking about it constantly from now until 2006.

Migrating from the HP 3000 to another computing platform was one of the main topics of the recent HP 3000 Solutions Symposium in suburban Philadelphia. Several presentations covered this issue, giving everything from the macro to the micro view. All had a similar message — the task is not easy, but it’s not impossible. The key to any migration is simple — planning.

Driving any migration is the selection of platform, OS and a relational database system. HP obviously wants you to choose its hardware platform HP 9000, but there’s a myriad of choices out there. But when it comes to OS, there are various flavors of Unix, Windows and the darling of the open source community, Linux. And if you thought that decision was complicated, the choice of database has a lot of options — Oracle, MySQL, SQLServer, PostgreSQL. And these decisions are just the start of the fun — or the start of the snowball rolling down the hill.

John Paolello, Director of Project Management for the PIR Group, discussed the macro view of a migration project, something which Paolello has worked on for almost 20 years. He advocated dividing a project into four phases: Analysis, Pilot, Conversion and Implementation. The Analysis Phase should be where most of the time is spent — finding source code, determining whether a program needs to be converted. (You should answer questions such as “Does anyone use the report generated by this program?”) You need to also determine who is the ultimate authority, the person who decides that the conversion is successful.

The staging of the conversion should be decided in this phase — do you have a magic weekend or can you do it gradually by modules? The users can help you determine if there are any segments that don’t have to convert right away. If you maintain history files, must all of them be available on that Monday following the magic weekend? Or can some of those files migrate a week or two later, after those who worked the weekend have had a chance for some sleep and some nourishment other than pizza and diet soda. Discussions with the users will get them involved and allay some fears. Even little things like how reports will be distributed are usually better understood by the end user as opposed to anyone in IT.

Sometime outside elements affect the Analysis Phase. Paolello related how the conversion of a system for the Board of Elections of a southern state took a dramatic turn when the Office of the Secretary of State became aware of the project. It seemed someone in that office wanted to direct the project toward a hardware platform/vendor for reasons only he knew.

The Pilot Phase could be called the dry run. Select a program or series of programs, convert them to the new platform, create and load data into the database and run the programs. If you get output different from the same run on the original platform, find out why. The output may be reports or may be new or updated data in the database — determine a means of comparing the output. Once you find out the reason for the differences, fix the programs, refresh the database and rerun. When you get output that matches, show the comparison to the ultimate authority. Test data in this phase and in Conversion are very important, so either users or IT staff are involved here.

The Conversion Phase is just a bigger version of the Pilot and Implementation during that magic weekend or staged switch-over. When this is complete, the ultimate authority has to sign off and you’re in a whole new world.

Paolello gave a couple of caveats. If you hire a vendor to do the conversion, the agreement should be to pay either by program or line of code — avoid open-ended agreements. And beware of the project when the conversion involves converting a system on a platform at one location (field office) to a platform at another location. The employees at one site generally bear some animosity toward the other site, and will help (i.e., provide test data) grudgingly at best.

IMAGE transfers, transformations

At the other end of the spectrum, two speakers gave presentations on the micro view of a migration. Birket Foster, the founder and owner of MB Foster, addressed transforming an IMAGE database to SQL and Lee Tsai, the owner of iMaxsoft, spoke on taking an IMAGE database to HP-UX Oracle.

After giving a crosswalk table of IMAGE to SQL terminology (dataset becomes table, record becomes row, etc.), both went over issues that would need to be addressed in the Analysis Phase of a migration project. The first item that came to mind was one of the basic premises of IMAGE database structure — the records are placed in the datasets in chronological order. You cannot retain this order in an SQL database without adding a sequence number to each record. This can affect reports, if you expect the records to be reported in sequential order.

Further, before you start the actual migration, you will need to examine your locking strategy. IMAGE does what is best described as a virtual lock — using a flag descriptor to indicate the lock and preclude other processes from grabbing the record for a write. Most relational databases do a lock of the physical record. This causes a significant performance hit, so you need to address locking before the programs are rewritten.

Relational DBMS products also have a structure of SELECT...COMMIT, similar to a DBXBEGIN and DBXEND, but not similar enough to allow a one-for-one replacement in the conversion of the programs. This must be addressed carefully in any conversion planning.

Another gotcha is that RDBMS systems do not have any concept of security to protect specific fields from being updated by groups of users. In the RDBMS world, if you have write capability to the record, you can update anything in the record.

On the plus side, the RDBMS generally does a cached read — similar to reads done by Robelle’s Suprtool, bringing a large block of records into memory even though you may only need one record. The problem is that if you are doing the equivalent of a chained read, you have all the records at once and must buffer them to examine and manipulate each one. The buffering is necessary if you want to do backward reads, since there is no such function in most RDBMS systems.

Data types also must be addressed — J2 and I4 data types have no equivalent in the relational side. VARCHAR is large enough so that if you had to break up large chunks of data in TurboIMAGE, you can ‘glue’ them back together in a relational environment.

A conversion also gives you the opportunity to examine and clean up the data. And if you have used certain non-intuitive flags in the data, you may need to modify them as the data is loaded. Relational systems feature several different date fields. If you have used ‘99/99/2099’ to indicate a date of infinity, that value will not load into any of the standard date fields in the conversion.

It’s good at this time to understand all the valid data values for a field. Say the accepted values for region code are “E” and “W,” you should make sure those are the only values in that field. If there’s something else, this may be an error or an exception. Ferret these out before final conversion. It’s good at this time to also determine the largest and smallest values in any field — both in length for character and actual value for numeric. The latter is significant since you can size the field properly.

Finally, you need to decide if you are going to do a simple conversion at this point, then optimize performance later. Or do you need to get optimal performance as quickly as possible. The simple conversion is going to be quicker, but you may want to take advantage of some of the features of your RDBMS, such as data rules built into the database. If you choose that route, it will take longer and require more testing, but if you have the time, it can save you tinkering later.

If you make the decision to migrate, it is not going to happen in just a weekend. It will take significant effort in planning, design, programming, data loading and testing.