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.
Transformer Stage
Moderators: chulett, rschirm, roy
Transformer Stage
Probal
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
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.
I would prefer to break the job in 2 and load parent table first and child table in subsequent job.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
Re: Transformer Stage
Thanks for your replies.
The issue has been resolved using link ordering in the transformer stage.
The issue has been resolved using link ordering in the transformer stage.
Probal
Re: Transformer Stage
Thank you!probal wrote:Thanks for your replies.
The issue has been resolved using link ordering in the transformer stage.
SKBURRA
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
If it is resolved, then you can mark the topic as resolved. I would like to understand the resolution. Thanks.
Choose a job you love, and you will never have to work a day in your life. - Confucius
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.
I too would like to see the details of their resolution.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers