ETL Migration Tool

Why I automated it

A German university hospital wanted to move the jobs that loaded its data warehouse from IBM Data Manager to IBM DataStage. Our team knew DataStage well, so we were going to migrate a few complicated jobs by hand and use them as examples for the rest.

Once we looked through the system, though, that plan seemed less useful. There were complicated jobs, but also a long list of small ones. Rebuilding each of those by hand would mean repeating much of the same work. I’d already built tools for changing Cognos reports programmatically, and we’d experimented with generating DataStage jobs. It seemed worth finding out how much of this migration I could automate.

Working from exports

For most of the project, I couldn’t work directly in the client’s systems. I relied on spreadsheet exports, which the tool read as CSV files. One export listed the objects in the system. Another held their definitions, split across ordered rows. A single job could occupy dozens of rows and refer to objects elsewhere in the export.

I hadn’t used Data Manager before, so I was learning how its jobs worked while trying to understand how those rows described them. Useful documentation was hard to find. Following the references often meant loading a much larger part of the export just to reconstruct one job.

I used state-machine-style parsers to work through the definitions a section at a time. They kept track of which part of a definition they were reading and gradually built up a structured model of the job. Different rules handled the source queries, transformations, and output definitions. When a parser hit something it didn’t understand, it reported an error I could inspect.

Working through those errors was much easier with a way to explore the parsed system, so I built a web interface for inspecting objects and following their relationships. I could see which definitions the parser understood and where it still needed work.

Separating reading from generation

Once I had a structured model of a job, I could work on translating it into DataStage without having to deal with the raw export rows at the same time. One part of the tool reconstructed the Data Manager definitions; another translated that model into DataStage jobs. The source parser stayed separate from the details of DataStage’s file format.

Most of the implementation was TypeScript. It felt much like writing a compiler for a poorly documented language: first I had to recover the meaning of the input, then put it into a form I could use to generate something another system could run. I handled almost all of the technical implementation myself, including the inspection tools.

Figuring out the target format

Knowing how to build jobs in DataStage didn’t tell me how to generate its import files. I needed examples of what DataStage itself wrote, but I didn’t have regular access to the target environment either.

I spent about a day with a colleague who had access, creating small test jobs and exporting them. Those files gave me something concrete to work from. By comparing the exports, I could work out how DataStage encoded its stages and the connections between them, then generate those structures myself.

The translation rules also drew on my earlier DataStage performance work. Where it made sense, I kept transformations and aggregation in source SQL. That let DataStage read less data and kept the generated jobs simpler, with fewer processing stages.

The client also wanted to retain the original folder hierarchy. I reconstructed it from the exports and included it in the generated files. DataStage’s import limitations meant those files still had to be imported in batches. The tool could generate them automatically, but getting them into DataStage still involved manual work.

What was finished

The tool generated replacements for roughly 1,200 jobs. The parser understood more of the source system than the generator could translate, so some job types still needed additional translation rules. We’d identified approaches for those cases, but that work was removed from scope when the project was shortened from about a year to roughly seven months.

Validation remained limited too. We wanted to run the old and new jobs, capture their outputs, and compare the results automatically. I built SQL comparison helpers and support for writing into test tables, but we didn’t get the complete end-to-end process running before the project ended. System access came very late, and we checked samples rather than validating every generated job.

What I found most interesting was figuring out enough of both formats to make the translation work. I’d write parts of the parser more cleanly now, but the approach worked well for taking those irregular legacy definitions apart and turning them into jobs I could inspect and generate programmatically.