I recently completed the migration of a very large Hospital Management Application running on VAX/VMS RDB database and developed in RALLY, to Unix Oracle and Developer 2000. I have done several migrations earlier but this one was a particularly painful. This had many important lessons, which I attempt to share in this write-up.

Digital India had cleverly sold a large costly VAX VMS cluster solution to a large Hospital in Calcutta. The Hospital Management software covering almost all aspects of the commercial activities of the Hospital was developed using RALLY and lots of ESQL/C. The database was RDB. For the entire life of this system with only a peak load of maximum 30 interactive users, the VAX/VMS response time was very poor. A lot of effort by Digital experts could not rev up the application. The customer lived with this costly mistake for years.

The modules developed were Admission, Out Patient, In Patient, Diagnostics (covering a very large number of disciplines - Imaging Pathology ENT etc.), Pharmacy, Emergency, Operation Theatre and many more. About 40 vt220 terminals were spread all over the hospital.

The following considerations prompted the customer to migrate all applications to Unix Oracle and Developer 2000:

1. The age and obsolescence of the VAX/VMS hardware. The high cost of Annual Maintenance.
2. The awful response times.
3. The fact that the market had almost no RALLY or RDB skills or for that matter VMS skills.
4. The reason Oracle and Developer 2000 was chosen because Developer 2000 is perhaps the only tool that support character mode vt220 terminals and it was considered too expensive to discard all vt220s.

I studied all the RALLY and RDB literature available and decided to develop my own tools for the migration. These were mostly in Microsoft Visual Basic with extensive use of Regular Expression libraries.

MigSchema: This utility reads an RDB schema and translates to Oracle. RDB triggers have to be hand coded to Oracle. RDB has a limit of 31 characters in name whereas Oracle has 30. The utility generates RDB unload scripts and corresponding Oracle SQL loader scripts. We did not have any open SQL connect and had to  handle NULL values and fractional seconds. Data was unloaded and transferred to UNIX via a PC connected using DECNET.

ScToPls: This utility converts all ESQL/C (SC in VAX) programs to Oracle PL/SQL. I decided not to use Oracle Pro/C because the SC programs made extensive use of VAX proprietary routines especially in date computations. The utility does 80 to 90% - the rest has to be a small edit of the output. The big advantage is that all ESQL/C ended up in Oracle server side packages, or Forms Routines or PLLs(libraries of routines). This resulted in a marked improvement of maintainability of the new system. The benefits in configuration management are extraordinary. About 100 major files(PLL FMB RDF and PLS) against several thousand in VAX/VMS (RGA and SC). Rally had a a 4GL language called ADL but it had many limitations and users were constrained to use many small C routines resulting in the plethora of source code files.

MigRally: This program reads a Rally Report and creates an output oracle FMB. The conversion efficiency is 60% to 80%. Almost all Rally features are carried across. Reports are done mostly using Oracle TEXT-IO package. A few complex reports have to be re-coded in RDF(D2k Reports). Those interested in the details of MigRally may wish to access rallytooracle.html The technical barriers were immense but we managed to overcome.

I developed tools to ensure that this indeed was the case. For example I used intelligent name transformation from Rally 80 ch. names to Oracle 30ch. max names. I developed tecniques to flawlessly convert messy and deeply nested if while constructs in C or ADL to PL/SQL.  I have planned for  potential re-use in Ingres Informix Sybase migrations as well to Oracle or Microsoft SQLServer.

It was easier to overcome technological hurdles but the bitter lessons were more to do with non-technical aspects.

Domain Knowledge: One cannot migrate applications by technology alone. One has to understand the domain. This takes a lot of time and each aspect has to be done with individual care. I had the advantage of having developed a Hospital Management application in a large Nursing Home in Calcutta. Still there are vast differences in commercial policy, billing procedures, customer handling. With all original developers having gone I had only users to educate me. The rest I had to glean from the actual software.

Dead Code: An application that has survived for a long is bound to have tons of dead code. These are there because of changed business rules, poor maintenance resulting in mindless retention of dead functionality. For example I faced a lot of problems regarding customer classification. The initial software had initially a very naive approach for corporate patients - patients who work for organizations that have commercial deals with the hospital. However the actual business rules were much more realistic and pragmatic. The problem was that all the original tables and code remained active with zero rows!

Duplicate Code: The reality of software maintenance results in large amounts of duplicate functionality. This Hospital was particularly bad regarding version control and configuration management.

Anachronistic Work Processes: Over time the working practices or workflow changes. In the Diagnostic and OT modules there was a large amount of functionality that had the users doing things that were at complete variance with reality.

Errors and Glitches: My commitment was to deliver the software with no value addition. This was also my intention. However one cannot but eliminate glitches as it would be a "crime" to knowingly retain potential bugs.

Poor version control: I had to convert over 20thousand lines of C code twice. This application related to the weekly MIS. I was given a wrong version initially! Had I been more familiar with the domain I could have detected the problem. There are always tell tale signs in source code that you can use to resolve version conflicts. Most often this requires domain knowledge.

The above (Domain Knowledge, Dead Code & Duplicate Code) are the primary obstacles in migration. There are no short cuts. To gave a lean robust and reliable Oracle D2K application that has a long life I had to painfully identify and kill all dead code and duplicate code. This resulted in a migration to a code that has high performance, reliability and zero fat.

I also did the minimum essential value addition restructuring and facelifts that were needed to eliminate the changes in workflow and known glitches.