Link Ordering

[palak08]

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!

[chulett]

I tried doing link ordering in Transformer, but of no use.

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]

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

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

But with my current design, It is doing Step 2,3 and then 1 resulting in deletion of all the records.

[mobashshar]

Unfortunately you can not use link ordering in parallel transformer.

One work around will be to split the job design in two jobs.
Job 1 will do your step a (delete) and job 2 will do b & c (insert).
Use a sequencer to first run job 1 and then job 2.

Hope this helps

[chulett]

Wait... the parallel transformer doesn't support the concept of link ordering?

[palak08]

It does support link ordering. The link you connect first will be executed first. It does not work though when you set link ordering through Transformer stage -> output column ordering setting.

Any thoughts why??

[ray.wurlod]

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.

[mobashshar]

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