How to handle the records which are delated in the source

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
vardhan354
Participant
Posts: 76
Joined: Wed Jan 25, 2006 6:42 pm

How to handle the records which are delated in the source

Post by vardhan354 »

Hi D.S Gurus:

I'm new to Datastage.I need your suggestions for implementing the logic.

Here is the situation
X is the source
Y is the target
Z is a target in a warehouse
Today the job was run X->Y-->Z
Tommorow, certain rows from X are deleted
I need to update those records in Z which are deleted in X to 'DELETE' in the Z table(Flag the particular record which was deleted from the source)(As I have to maintain the history of the source table in Z)
any suggestions wud be greatly appreciated.

Thanks.... :roll: :idea: :?: :idea:
ETL
Top
I_Server_Whale
Premium Member
Premium Member
Posts: 1255
Joined: Wed Feb 02, 2005 11:54 am
Location: United States of America

Post by I_Server_Whale »

Hi,

Before deleting records from X, write them to a hashed file. After the deletion, perform a look-up of the hashed file against Xto get the missing records.

Use this result set to update the records in the Z table.

Thanks,
Naveen.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Top
gateleys
Premium Member
Premium Member
Posts: 992
Joined: Mon Aug 08, 2005 5:08 pm
Location: USA

Re: How to handle the records which are delated in the sourc

Post by gateleys »

vardhan354 wrote:Hi D.S Gurus:

I'm new to Datastage.I need your suggestions for implementing the logic.

Here is the situation
X is the source
Y is the target
Z is a target in a warehouse
Today the job was run X->Y-->Z
Tommorow, certain rows from X are deleted
I need to update those records in Z which are deleted in X to 'DELETE' in the Z table(Flag the particular record which was deleted from the source)(As I have to maintain the history of the source table in Z)
any suggestions wud be greatly appreciated.

Thanks.... :roll: :idea: :?: :idea:
1. Have a job which the keys of X into a hashed file.
2. Perform a lookup of yesterday's Z against this hashed file with the constraint

Code: Select all

IsNull(HashLink.Key)
This gives all records in Z which have been deleted in X.
3. Perform an update only in Z, to check the deleted flag.

gateleys
Top
AGStafford
Premium Member
Premium Member
Posts: 30
Joined: Thu Jan 16, 2003 2:51 pm

Post by AGStafford »

Another option is to Load Z (or at least the unique key) to a hash file and add a "status" field set to "Delete".
For each row read in from X do a lookup against Z. If found update the "status" field in the Z hash file to "Exists".

Then read the Z hash file and for any rows still in "Delete" mode can be updated to indicate the row is deleted.
Top
vardhan354
Participant
Posts: 76
Joined: Wed Jan 25, 2006 6:42 pm

Post by vardhan354 »

Yes,This was the job design I planned to implement,as I'm new to D.S, Im not sure how can I implement the logic.I mean looping and stuff.

Thanks a lot for all Gurus for their valuable suggestions.

Cheers,
Vish :D
ETL
Top
vardhan354
Participant
Posts: 76
Joined: Wed Jan 25, 2006 6:42 pm

Post by vardhan354 »

GATELEYS,
Amazing....Thanks a lot... U logic really helped me a lot.
This Forum is great for beginers like me....
Keep up your good work.
Regards,
Vish. :) :) :D :) 8)
ETL
Top
Post Reply

Return to “IBM<sup>®</sup> Infosphere DataStage Server Edition”