Enhancing Impact Analysis of DataStage projects for Cloud Migration: Automating for Comprehensive Data Lineage Insights

In the world of constantly evolving technology, we've experienced a migration revolution to the Cloud. It's like the bright future of Cloud technology is waiting for us. Feels like all sunshine and rainbows, right? Well, that might be true for some folks(working in the cloud technology), but if you're in data migration team or responsible for decommissioning legacy systems, things might get just a little cloudy!!

As part of this team, imagine dealing with code documentation in IGC that's more out of sync than a toddler's dance moves, or code version control descriptions that were last updated back when we were all jamming along with our iPods, or everyone in the team is waiting for that 1 guy who knows everything but somehow is always busy! If even 0.001% of this exaggeration is true and you are migrating code from DataStage, this article was crafted only for you. If not, you can slip away with my heartfelt gratitude for patiently reading it till now. It's not you; it's the DataStage tools' mystique.

Now that only 2 of us are left in this glorious adventure, before we dive headfirst into planning data migration activities, we need answers to the most mysterious riddle of all - the legendary data lineage(table usage) in DataStage jobs across projects! You know, we can't expect those DataStage jobs to spill the beans by themselves. If only it were that easy! So, our first step is to understand what's currently working and how it's implemented. Understanding the impact will help us to plan our migration activities better. Let's grab our magnifying glasses and embark on a quest to automate data lineage in DataStage jobs.

Understanding DataStage jobs' .xml export file

Ah, the mysteries of DataStage jobs – if working on this ETL tool wasn't complex enough, XML files can make your head spin!

To analyze all the DataStage jobs in a project, you'll need to export the project in a .xml file. If you can do this solo, consider yourself DataStage Hero! yes, you've served DataStage well, my friend. Sorry, it's just that these tools have been our companions for so many years, and it's tough to say goodbye! But hey, enough with the emotions; let's dive back.

Before we venture any further, let's make sure everyone is on the same page about XML tree structures. If you're not familiar, every XML tree starts with a root element, and under that root, you'll find child branches. These child branches can have their own sub-child branches, and this can go on and on until you decide that the child has had enough. Think of it as a family tree, but with elements: Parents have children; Children have parents; and Siblings are children on the same level (just like brothers and sisters).

Now, in the realm of DataStage, the XML structure starts with DSExport as the root element. DSExport is like your wise old grandpa who knows everything about DataStage project export. It provides juicy details about the DataStage server and project, all neatly wrapped up with an export time stamp.

But that's not all, DSExport is not a loner; it has a child branch named Job, and under Job, things start to get interesting. Identifier property of Job gives you jobname. Here, you'll also find Record (child branch of Job), and Record is like the cool, wise-cracking uncle who's full of surprises.

Record itself has many types parameter values defined, each with its own unique role. Below are few important ones for our analysis of parallel jobs:

• JobDefn: JobDefn offers comprehensive insights into the metadata and job parameters stored within the job. It also tracks job performance and tracing properties.
• CustomStage: This record type includes properties at the stage level, displaying information such as Stage Name, Stage Type, and Input/Output links associated with the stage. Most database connectors house their properties, including connection details and queries, in this section.

Only SQL Server stage queries are stored in Input or Output links

• CustomInput: These records contain information about input links originating from the CustomStage.
• CustomOutput: These records store information about output links originating from the CustomStage.

In addition to these, there are other types like Annotation, TransformerStage, TrxInput, and TrxOutput that are used within records to represent various stages and links. However, our focus is solely on record types that can be employed to extract data lineage and table information.

Its not the Journey, It's the Destination!!

To reiterate, it's not about the journey; it's about the destination! Even though Ralph Waldo Emerson might disapprove, what I mean is that you can utilize any programming language like Python, Spark, shell scripting, or another language of your choice, as long as you refrain from creating a new language. I trust in your intelligence; there's no need for me to teach you programming or hold your hand. Our objective is to extract valuable information from the exported .xml file, which is invaluable for conducting an Impact analysis of DataStage jobs.

Here's a breakdown of the information you should extract:

Job Name: Utilize the Job Identifier value to extract the job name.

Job Type: Use the 'JobType' Property value under the 'JobDefn' record type. Parallel jobs will have a value of 3, while Sequence jobs will have a job type of 2.

Stage Name: The stage name is found in the 'Name' property within the CustomStage record type.

Stage Type: Within the CustomStage record, the 'StageType' property value defines the specific type of stage being used. For example, you can identify ODBC connectors and Copy stages.

Database Connector Context: To determine whether the connector is used as a source or target, examine the value of the 'Context' property under the SubRecord of CustomStage (except for SQL Server).

Database Source Query: The source query can be located within the 'XMLProperties' property under the SubRecord of CustomStage (except for SQL Server). Check out for text after <![CDATA[ to identify the source query when you encounter TableName or SelectStatement in XMLProperties.

Database Target Details: The target details are similar to the 'Database Source Query' as they rely on the 'XMLProperties' property under the SubRecord of CustomStage (except for SQL Server). Look for the string following <![CDATA[ to identify the target table name when you encounter TableName or InsertStatement in XMLProperties.

XMLProperties can also serve as a resource to verify various kinds of information, such as Fail on Type Mismatch, Fail on Size Mismatch, Drop Unmatched Fields, and Before & After SQL.

In conclusion, I trust that this information will arm you with the power to decode the enigmatic data lineage and table information required for your impact analysis. And, if by some bizarre twist of fate, this doesn't do the trick, fear not! You can simply sit back, relax, and wait for me to unveil the entire program in a couple of months... or years. But here's the catch: I'll only do this if I receive a million likes on this article, or if someone's feeling super generous and ready to pay me millions to spill the code. Until then, happy data decoding!