Page 1 of 1
Transformer Stage
Posted: Mon Mar 01, 2010 4:32 am
by probal
In my job I have a database table as a source and there are two o/p links from the transformer which is used only for mapping.Two o/p links are for two target database table.One is supposed to be a parent table and the other to be a chi;d table.
Is there any method or functionality available in datastage so that the parent o/p table will be populated first and only then the child table.
Can anyone share some light on this.
Appreciate any kind of help.
Posted: Mon Mar 01, 2010 6:46 am
by nani0907
this can be done using the muti insert staement .so give the parent table first and then child .and handle rejetcts too.
Posted: Mon Mar 01, 2010 7:33 am
by priyadarshikunal
even with multiple insert statements it may not work unless you have transaction size 1. So that your job commits the master record first. As a workaround you can disable the foreign key constraint, load data and then enable the constraint. But in that case make sure you validate the records prior to loading in job itself.
I would prefer to break the job in 2 and load parent table first and child table in subsequent job.
Re: Transformer Stage
Posted: Tue Mar 02, 2010 5:22 am
by probal
Thanks for your replies.
The issue has been resolved using link ordering in the transformer stage.
Re: Transformer Stage
Posted: Thu Sep 08, 2011 3:20 pm
by SKBurra
probal wrote:Thanks for your replies.
The issue has been resolved using link ordering in the transformer stage.
Thank you!
Posted: Thu Sep 08, 2011 5:06 pm
by ray.wurlod
People searching this thread in future might also contemplate investigating the Distributed Transaction stage.
Posted: Sun Sep 11, 2011 12:39 am
by qt_ky
I don't understand how changing the link order could resolve this. It seems like both tables would load simultaneously no matter what the link order is. What am I missing? I guess I would lean towards two jobs run in sequence for this.
If it is resolved, then you can mark the topic as resolved. I would like to understand the resolution. Thanks.
Posted: Sun Sep 11, 2011 8:24 am
by chulett
Link ordering would ensure that the 'master' record is sent to its target first before the child record would begin its journey. If this was a Server job you could use a single target stage and thus keep all of this work in a transaction or unit of work which can cut down on the shenanigans needed. However, having two separate targets means one is not aware of the other and thus (I assume) they are doing as priyadarshikunal suggested, setting both the Array Size and Transaction Size to 1 so that the child record can 'see' the master record when it arrives.
I too would like to see the details of their resolution.