Hi All,
I need to implement SCD2 logic using datastage parallel job. We are using Netezza for storing data.
I am using Netezza Enterprise stage and ODBC connector stage for this design.
As Netezza is not very good at Updating records, I will be using following approach :
1. Check for src and target record, if there is a difference then
a. Delete existing record from target. (ODBC Connector)
b. Insert a new record with the same value as target with Active Indicator='N' (Netezza Enterprise stage)
c. Insert a new record with the updated values coming in source with Active Indicator ='Y' (Netezza Enterprise stage)
Now as Netezza Enterprise stage does not provide functionality of deleting one record while inserting, I will be using odbc connector stage for step (a).
Issue
My job performs step (b) and (c) first and then step (a). Which results in deleting all the records for that particular case.
I tried doing link ordering in Transformer, but of no use.
Is there a work around for that.
Any help will be much appreciated.
Thanks!
Link Ordering
Moderators: chulett, rschirm, roy
Re: Link Ordering
What exactly does this mean? Proper link ordering is your answer. That and an Array Size of 1 and (perhaps) a Transaction Size of 1 as well.palak08 wrote:I tried doing link ordering in Transformer, but of no use.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Re: Link Ordering
Thanks for your reply. For link ordering, I opened transformer and moved the delete link to the top with Array Size of 1 and a Transaction Size of 1. But when I run the job, it deletes all the records.
Here is an example of current scenario
But with my current design, It is doing Step 2,3 and then 1 resulting in deletion of all the records.
Here is an example of current scenario
Code: Select all
Src File
ID Name
A1 Abc
Initial Load into target
Skey ID Name Active Ind
1 A1 abc Y
Next time-- Src record
Src File
ID Name
A1 xyz
Operation to be performed in target:
1. Delete existing record from target
Skey ID Name Active Ind
1 A1 abc Y
2. Insert Record with old values and Active IND as 'N'
Skey ID Name Active Ind
1 A1 abc N
3. Insert another record with new values and Active IND as 'Y'
Skey ID Name Active Ind
2 A1 xyz Y
-
- Participant
- Posts: 91
- Joined: Wed Apr 20, 2005 7:59 pm
- Location: U.S.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
All links are buffered, at least by default, which basically means that you can't control the speed at which they process rows.
You might try changing the buffering policy to "do not buffer", but that will slow things down somewhat. And I don't guarantee it, since I've never tried it in that context.
You might try changing the buffering policy to "do not buffer", but that will slow things down somewhat. And I don't guarantee it, since I've never tried it in that context.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 91
- Joined: Wed Apr 20, 2005 7:59 pm
- Location: U.S.
Hi Craig,
As far as I know.. you can use the link ordering in parallel transformer but it will not give you desired result.. During my previous project I had to redesign the job by splitting it into Upsert and Delete jobs because after using link ordering in transformer stage.. the delete was not happening in desired order.
I have not tried link ordering in server transformer but I remember it used to work in DS v6.5
As far as I know.. you can use the link ordering in parallel transformer but it will not give you desired result.. During my previous project I had to redesign the job by splitting it into Upsert and Delete jobs because after using link ordering in transformer stage.. the delete was not happening in desired order.
I have not tried link ordering in server transformer but I remember it used to work in DS v6.5