Sunday, December 2, 2012

Updating Previous or Next Record in the Batch via DataStage Transformer - 1

 It was quite sometime since my last update. Recently I used InfoSphere DataStage Transformer for couple of areas and thought sharing them with others.When we do Data Integration Transformation is always the key and required innovative ways to handle problem.

2 Problems are quite common updating the Next Record in the batch or Previous Record in the batch with some value from current record. In this 2 Series Blog I intend to discuss these 2 cases with the help of InfoSphere DataStage. In Part 1 I will Cover How we change the Next Record. In Part 2 will handle How to change the Previous Record.
Assume we have Data as shown on left hand side. ID is a group where we have different records and end date of Next Record should be Begin Date -1 of Current Record. e.g. End Date of Record 2 should be 19990104 - 1. For explanation purpose I am treating Date as Number. (Off-course you can convert Date Number into valid Date via available functions).

I am not going to discuss basic transformation functions here and also not providing details on looping functions. It is already done @ Looping-in-infosphere-datastage  and other relevant blogs earlier.
This is how DataStage Transformer looks like. To Start with we need to define the Stage Variables. Make Sure Data is sorted on the variable where it is grouped like in this case "ID". I used SaveInputRecord() which is available since Information Server 8.5 to cache set of records based on the group.
 Now we need to add Loop Conditions and required logic based on the Group. I am using GetSavedInputRecord() which saved as part of Stage Variable definition. Now I defined 3 Loop Variables to store Current, Previous and Final State.
Now this is How Output Columns are mapped and Altered Data is processed.
 Based on above logic this is what I receive. End Date is Changed with Begin Date - 1 of Previous Record. It is being done based on Group which is ID here.
I haven't done anything Special. It is same old mechanism we learns in programming days and even with-in DataStage earlier.
SV1= (if SV2= SV3Then 1 Else 0) 
SV2 = current.value
SV3= SV2
Off-course I can get rid of 3rd Stage Variable and made it simple. Reason I used that to have same logic works in both Previous and Next case and hence it is like this. So please go-ahead and improve the logic in your design. This is just an sample and I wanted to keep it consistent.
In Part - 2Continuation of this, will discuss how we can do changes in the Previous Record based on content of Current Record.
Disclaimer: The postings on this site are my own and don't necessarily represent IBM's positions, strategies or opinions


  1. Post is really very nice and very descriptive. I Like to read your post.Your post give me more informative about transformer. Thanks for sharing.
    Variable Auto Transformer

  2. This is one awesome blog article. Much thanks again.
    I really enjoy the blog.Much thanks again. Really Great.

    oracle online training
    sap fico online training
    dotnet online training

  3. Pretty good post. I just came across your site and wanted to say that I’ve really enjoyed reading your posts. In any case I’ll be subscribing to your feed and I hope you will keep a good work!Cheer!

    sap online training
    software online training
    sap sd online training
    hadoop online training