Profound Information

Helping businesses fulfil their potential through information and technology

  • About Us
  • Contact Us
  • Who We Help
    • Executives and Heads of Departments
    • CTOs, CIOs and IT Managers
    • CEOs
    • Boards of Directors
  • How We Help
    • Informing Business Strategy
    • Designing Data and Technology
    • Fulfilling Business Requirements
    • Developing People
  • Our Services
  • Case Studies

COBOL Migration to Relational Database

Background

Blackwoods is part of the Wesfarmers Group. With revenue in excess of $1Billion p.a. Blackwoods is Australia’s largest supplier of industrial and safety equipment. It is a B2B distributor of industrial tools, safety equipment, electrical components, mining and engineering supplies.

The company had invested almost 40 years in the development of a bespoke ERP system developed in COBOL. However, now that Blackwoods was planning to move to a modern ERP platform, it was proving nigh impossible to access the data in the proprietary data files.

Profound Information was asked to provide a solution that could automatically read thousands of files which were updated daily and convert the data into a modern, accessible relational database.

The Challenge

This was a technically difficult undertaking – seven companies had previously attempted it and none had succeeded. The COBOL system was an integral component in the high-volume distribution chain:

  • Number of files: 220,000+
  • Number of records: 5 Billion+

38 years of development resulted in many different coding approaches over time. Consequently there were many inconsistencies and idiosyncrasies in the data stored:

  • Data was stored as pure hexadecimal codes with no separators to define where one column finished and the next one started (e.g. 13E2FA6D379304ED2…)
  • Some data was read right-to-left, and some was partially read left-to-write – (for illustration, some of the data was stored in this order: 1, 2, 3, 4, 5, 6, 10, 9, 8, 7, 11, 12, 13…
  • Eight different date formats were used, sometimes with multiple formats in the same records
  • Multiple approaches were adopted for storing numbers – even within a single file there were multiple approaches to storing data
  • Multiple record formats were mixed together in many of the data files – in one case over 300 record formats were mixed together in one data file.
  • At least six different encoding methods were used to encode the data stored
  • There was no documentation

The quantity of data made it infeasible to write the code to process each file individually, yet the idiosyncrasies inherent in the source data generated by the COBOL system didn’t allow for any off-the-shelf solution. An intelligent, automated bespoke solution was required.

Profound Information’s technical team had to decode a wall of hexadecimal code before they could start work on converting the data from the COBOL system into a modern relational database.

The Solution

Profound Information was asked to take the COBOL data and convert it into a relational database on a daily basis. Watch Alan Perkins’ presentation about this project to the ADAPT Connected Cloud and DC Edge conference on the Gold Coast.

Alan Perkins drew on his expertise in databases and his understanding of COBOL to reverse engineer the data structures hidden in the hexadecimal code. Profound Information developed a software code generator which reads the COBOL copybooks from the source and then parses them to automatically generate data transformation software for each type of more than 1000 record types. Whenever a change is made to the COBOL data structures, the code generator picks up the change and generates a new data extraction program. Another program was written to act as a management layer and watchdog to oversee the execution of the automatically generated data transformation program.

Results

Now that the business has access to this rich mine of data, more and more use cases are opening up and demand has been high. The initial stated reason for undertaking the work was to prepare the data for a migration to a new ERP system, and maintaining focus on delivering this was challenging in the light of several business units who saw opportunities to leverage the data.

One such additional use case that was sanctioned was the provision of data to support a new digital platform. When the consultants who were working on the new platform requested the COBOL team to provide certain data that proved prohibitively expensive to collate, the work we did proved to be a viable alternative for sourcing the data and saved millions of dollars.

The two and a half years after delivering the project, the data is still being processed every day. A major test came when a change to the data structures occurred and the company was able to regenerate the code automatically by simply putting the new data structures into a receiving directory and apply the code generator, redeploy and the new tables were automatically created and populated in the database on the next process run.

It is impossible to put a price on the value this project has generated, but it runs into the millions of dollars.

Whenever a change is made to the COBOL data structures, the code generator picks up the change and generates a new data extraction program

Technology used

  • Python with Django for the data layers
  • PostgreSQL for hosting the translated data
  • Azure Virtual Machine Scale Sets were used as the main infrastructure layer
  • Terraform infrastructure as code to automate the generation of the Azure platform
Ajit
Ajit head & shoulders

"Alan is a highly customer centric, people oriented and strongly ethical technology leader"

Ajit Melarkode
Vice President, Asia Pacific and Japan, Dell Boomi
Humanico
Ajit head & shoulders

"Alan is a tech visionary who has added immeasurable value to our startup, in particular his natural ability to simplify complex concepts to deliver outstanding technology."

Fiona Vale and Paul Rush
Founders, Humanico
Sanjeev Gupta
Image is not available

"Alan Perkins is a technical genius who can also think in a non-technical way, which has helped us to see the shortfalls well in advance"

Sanjeev Gupta
Technical Support Engineer, Bluestone APAC
Nathan Stanfield
Nathan Stanfield - head and shoulders

"Alan has exceptional vision for the future. He is a technology pioneer and is always ahead of the game"

Nathan Stanfield
Founder of Stanfield IT
Jakub
Tony Heywood head and shoulders

Alan's advice, mentorship and guidance made a profound impact on my career, most importantly on how I frame problems and situations I encounter

Jakub Krajcovic
Investment Manager, Sandberg Capital
Per Dahlberg
Per Dahlberg - Head and shoulders

"[Alan] is a recognized cloud industry pioneer and his contributions at ACCA (Asia Cloud Computing Association) board and working group levels have helped build the success of the association"

Per Dahlberg
Director, Springpoint and Founder, Asia Cloud Computing Association
previous arrow
next arrow
  • Home
  • About Us
  • Contact Us
  • Privacy

Copyright © 2020–2024 Profound Information Pty Ltd · ACN 073 061 631 · All Rights Reserved