Workflow dependency in Informatica: Part-2 : Load Status Entries

This post is a continuation of the first part here and explains how to deal with workflow dependencies in Informatica using Load Status entries. I have listed out the limitations with using event wait files here.

Note : this example and scripts are for Informatica running on Oracle and Unix (ksh). You can of course come up with a similar set of objects and script for any other database and scripting language.

Load_Status Table Structure and Other scripts…

This is how the basic structure of a load status table would look.

Load Status Entries

The next piece is to create a script which will insert records into the load status table. This is the base version, which can be changed based on your needs…

We can then create a reusable command task like below and override it for our session based on the workflow name and the folder name. For each workflow, override the workflow name and the folder name with corresponding details. For our case…

would be changed to

in the customer workflow…

Once we have workflows that run this session, we’ll be able to see records in the load_status table..

As you can see from the result set above,we can query this data to find out other details such as …”when a load completed on a given day for a given workflow”..”on which dates the load was not run” and so on.

Wait Event Script

The final part of this task is to simulate the “wait” behavior . As you can guess, we’ll use the “sleep” command to wait till the file is available.

In addition to simply waiting on a file as is the case with Informatica’s default event wait file behavior, the following script also gives you the ability to fail the workflow after a given point of time in a day(so that someone could look at it and see what’s wrong before the next day’s load begins..).

This shell script would be included in a reusable command task similar to the load status entries. The only difference is that this task is included before each child dependent workflow

Based on the when the parent workflow completed on a a given day, the child workflow might…

a) Find the load status entry because parent workflow has completed…in which case the log would look like…

b) Wait some time after the start and then find the load status entry and succeed.

c) Cross the threshold time and still not find the load status entry in which case, the workflow fails.

You can, of course, modify the load status and the event wait scripts if your requirements are more complex, but this should give you a general idea.

The part I like the most about these is that there is no maintenance of event wait files and once you know your pre-requisite workflows and they have the load status set up, you only need to add the event wait task to the child workflow. You don’t have to modify the parent workflow every time there is a new dependent child workflow.

Cheers…
Rajesh.

  • sdhar

    Informative….thanks for ur posts

  • waseem

    Thanks for your posts …..Good Job keep doing 🙂

  • LP

    Is there another way of implementing this scenario without having to do shell scripting?

    • Rajesh

      You could implement the same logic using just a stored procedure (and then use stored procedure transformation to call it). , if the underlying database has an equivalent of sleep command. (most databases do).

      If your question was about any in-built Informatica mechanism, I haven’t yet found any approach that supports auditing, multiple dependant workflows and load analysis as completely as this one.Informatica’s event-wait, event-raise and “link-conditions” are pretty decent when it comes to dependency within a given workflow, but not so great when it comes to the features I described above.

  • Pingback: Informatica Command Tasks()