AUTHOR: SANDER DE BREE
The migration of airline data is a process requiring significant skill and attention. Especially when aircraft airworthiness data is involved. After all, it’s based on this information that maintenance on aircraft is planned and the continued airworthiness of the aircraft is safeguarded.
Interestingly, within the world of Airline Maintenance & Engineering the usage of the IMS mainframe systems is still widely present. Either in full, or in partial as the backbone of more modern systems that have been put on top of the IMS mainframe system. So once this data is going to migrated out of these systems into a new, more modern, MRO software system one can imagine that it is not a straight forward process and even not remotely similar to any other type of aircraft data migration project.
But before we go into some pitfalls to watch out for, a bit of history on the IMS mainframe system: The IMS (Information Management System) mainframe was developed by IBM during the 1966 for the Apollo space program (...yes, the very same that landed the first men on the moon….). It was used as an inventory program for the Bill of Work for the Saturn V moon rocket and Apollo shuttle. After it reached a stage of maturity it was taken to the market as a commercial information management system and was widely adopted by airlines to serve as database system for maintenance, spare parts inventory, passengers & ticketing as well as airports.
Interestingly, were most parts of aviation have moved away from IMS over the course of the last 20 years, some Maintenance & Engineering divisions have kept the system for some core functions within Airworthiness Management. However as newer technology is becoming more and more accepted in aviation (Electronic TechLogs, usage of mobile devices, predictive analytics, etc) and the pool of people still knowing how to work with an IMS system is vastly shrinking a trend can be seen that the last remainders are now also moving away from the IMS mainframe systems and move into newer MRO software system. However, migrating the required data out of the IMS mainframe system is not even remotely similar to migrating data out of any other database system. The three main differentiators are:
- Hierarchical database structure
- COBOL copybooks
- ETL plugins
Hierarchical database structure:
Unlike most other database systems used for aircraft airworthiness data management, the IMS system is a hierarchical database instead of a relational database system. This means that data in the system is not linked to each other by means of primary keys but by means of a hierarchy structure in the database itself without the content being linked with each other. You can imagine this as a treelike structure of the database, working with “parent” and “child” records rather then a relational structure between tables.
The challenge that this possess is that, next to a significant different mindset, it also allows for more data pollution as data is not governed by primary key definitions. This could result in examples such as the definition of an Airworthiness Directive reference to be differently typed in the parent and child records of the very same Airworthiness Directive. E.g. the parent record will contain AD number EASA-2018-0001 whilst the child record holding the status of the AD on each aircraft might work with the definition EASA_AD_2018_0001. To the human eye this is easily identified as the same Airworthiness Directive, however when migrating data and requiring matching records this will pose a multitude of mapping issues.
IMS systems often work with so-called COBOL copybooks. COBOL is a programming language developed around the same time as the IBM IMS mainframe system. The reason these copybooks were introduced is that fields in the IMS system were only able to store up to 6 bytes per database field and 40 bytes per segment. This required the rest of the information to be stored in a separate place, with a reference link between the record in the database and the place in the Copybook that stored the rest of the information. An example where this would be used is the calculation of the latest due date to comply with the Airworthiness Directive, used in our previous example, on every individual aircraft MSN. Typically, the database record would hold the main header information of the Airworthiness Directive and a child record of that same AD would hold the time requirement information, e.g comply within 500 Flying Hours. However, the calculation of when these 500 flying hours will be used on each individual aircraft in the fleet will then continuously be calculated based on parameters and formulas stored in the Copybooks and then displayed in the User Interface.
When migrating the Aircraft airworthiness data out of the IMS system it will hence also mean that a means must use to access the parameters and calculations used within the COBOL Copybooks. Without accessing the Copybooks, the information migrated will be incomplete and not contain crucial Aicraft Airworthiness Data.
Crucial for any aircraft data migration project is the usage of a proper ETL (Extract-Transform-Load) infrastructure (…and no, excel is not a proper ETL structure….). However, when migrating aircraft data out of an IMS system things are a bit more complicated. The complication arising here, is a combination of the above two mentioned factors (hierarchical database and COBOL copybooks). This means that traditional ETL tools won’t suffice as these are not able to access the data from the copybooks. Next to that, converting hierarchical stored data into a relational format is a painstaking process and will require you to be able to run ETL processes continuously with as les effort as possible. More information on plugins can be found here: TITAN