How and why I built Pentaho scripts automation

The problem I wanted to solve

We are doing database migration from DB2 to Oracle as part of Application modernization. We have around 300+ tables across 4 applications. We used Liquibase for DDL migration and Pentaho for data migration. Problem here is we never worked on Pentaho, it took us 2 weeks to do POC and test it it cloud environment. It worked fine. next step is we need to create Kettle Transformation (.ktr files) for each table. As we dont have experience with Pentaho before, it took us 15-20 min per table which is lot as we have timelines set already.

What is Pentaho scripts automation?

When I look at the ktr file in Intellij it rendered as xml file, that is when I got the idea of automating it as we have lot of libraries available in Java for reading and writing xml files. The tool will read all the columns and primary keys from DB2 and generates ktr file for each table. It took 20 min to generate ktr files for all 300 tables when compared to 20 min per table manually. And it is less error prone.

Tech stack

It it simple Java application which does marshalling and un-marshalling of XML files.

Challenges I faced

I sat one whole day and implemented it. Actual logic is minimal but it took time to understand the structure of ktr file and creating Java objects for each type/tag.

Key learnings

There will be alternative for everything, all we need to look at from different angle. Initially I didn't thought of automation until I look at ktr file in Intellij. That gave me the idea and implementation.