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....
How to handle the records which are delated in the source
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 76
- Joined: Wed Jan 25, 2006 6:42 pm
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
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.
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
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Re: How to handle the records which are delated in the sourc
1. Have a job which the keys of X into a hashed file.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....
2. Perform a lookup of yesterday's Z against this hashed file with the constraint
Code: Select all
IsNull(HashLink.Key)
3. Perform an update only in Z, to check the deleted flag.
gateleys
-
- Premium Member
- Posts: 30
- Joined: Thu Jan 16, 2003 2:51 pm
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.
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.
-
- Participant
- Posts: 76
- Joined: Wed Jan 25, 2006 6:42 pm
-
- Participant
- Posts: 76
- Joined: Wed Jan 25, 2006 6:42 pm