Page 1 of 1
How to handle the records which are delated in the source
Posted: Thu Mar 09, 2006 4:10 pm
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....
Posted: Thu Mar 09, 2006 4:22 pm
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.
Re: How to handle the records which are delated in the sourc
Posted: Thu Mar 09, 2006 4:27 pm
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....
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
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
Posted: Thu Mar 09, 2006 9:45 pm
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.
Posted: Fri Mar 10, 2006 7:59 am
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
Posted: Fri Mar 10, 2006 8:36 am
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
