Migrate ETL from DataStage - automatically!

Many companies are in the process of changing integration technology, for all sorts of reasons. One frequent case is migration from IBM DataStage — a very widespread platform from a blue-chip vendor.

What does a DataStage migration really involve?

Imagine you are a developer on such a project. Or, even worse, the technical lead. The company has hundreds — if not a four-digit number — of integration routines. Developing all of them from scratch in the new technology could potentially last years with the team you have. Obviously, some sort of automation and refactoring will need to be done.

You find a simple, typical DataStage job and start studying it. Since you are still deciding on the target technology, you decide to document it in a simple Excel format. One sheet will be the select section — the destination table columns and all the transformations needed to get them. Another will be the from section — all the source tables and joins. The third will contain the where section and all the other parameters for that integration routine.

OK, it looks like there are two source tables and one transformer. You put the stuff from the transformer in the select sheet, and the joins between the sources in the from sheet.

Where does the hidden complexity come from?

But then you start examining the sources and get unpleasantly surprised. One of the sources is a so-called hash file — a proprietary DataStage structure that gets loaded in another job. After some thinking and consultation, you decide to replace hash files with tmp_ tables. Once you migrate the previous job that loads the hash file to the new technology, you will have your temp table loaded, and you'll be able to use it. You adjust the from sheet accordingly. Alright — one source solved.

But another source is a so-called connector. Here the situation is even worse: it is actually an SQL query with even more source tables and transformations. It is still doable — you need to further adjust the specification of the new job. You add the newly discovered source tables to the from section, and the second set of transformations to the select sheet.

Now imagine you had even more connectors, hash files, and transformers — which is actually quite a common situation. Reverse engineering, specifying, and developing new jobs would quickly become a real nightmare.

“Reverse engineering, specifying, and developing thousands of DataStage jobs by hand quickly becomes a nightmare. Trust me — I've been there.

How does EPIC Ingenioso automate it?

Luckily, there is a solution. Using EPIC Ingenioso, you can automatically transform any DataStage job into an Excel format just like the one from our story. You just export the job to XML format.

Select the file, click — and there you go. The whole ordeal described above is now replaced with a couple of clicks. Neat.

But isn't Excel just the halfway point?

OK — but now we have an Excel. It's nice for documenting and specifying, but don't forget what we actually want. We want our ETL routine in the target ETL technology of our choice. That's the next step — turning the captured specification into runnable jobs on the platform you're migrating to, automatically and deterministically. Don’t miss what’s next — subscribe to our newsletter.