Loading parent/child tables in one job
Moderators: chulett, rschirm, roy
Loading parent/child tables in one job
I have a job where I split off from a transformer to load two Oracle tables, one parent, and one child. Even though I ordered the links appropriatly in the transformer stage (parent load first, child second), the child table does not get loaded. I assume it is because the parent Oracle stage has not committed the transaction before the child table attempts to load. What I have done is create a data set down the child link, then in another job, load the data set into the child, but I would prefer to do it all in one job.
Interestingly, when I put a copy stage down the child link, then write to the DB and a file, it seems to "slow down" the processing of the child enough to allow the load to occur, but I do not think that is guaranteed.
Any suggestions?
Interestingly, when I put a copy stage down the child link, then write to the DB and a file, it seems to "slow down" the processing of the child enough to allow the load to occur, but I do not think that is guaranteed.
Any suggestions?
If I were in your place, I would always run 2 separate jobs, one for parent and one for child and set dependency of the job that is supposed to run latter on successful completion of the former job.
This way we can mainting data consistency plus have more control on the end to end execution
This way we can mainting data consistency plus have more control on the end to end execution
Datawarehouse Consultant
Yes you can do this but it is tricky.
First of all you need to ensure the parent population first and then child.
1. Use parent oracle stage write method as upsert
2. Parent oracle stage properties : Reject mode is true
3. Get reject link from that parent oracle stage and join with the input child link
4. Output of join (Join stage) to child oracle stage.
I hope this will help you to do what you want.
First of all you need to ensure the parent population first and then child.
1. Use parent oracle stage write method as upsert
2. Parent oracle stage properties : Reject mode is true
3. Get reject link from that parent oracle stage and join with the input child link
4. Output of join (Join stage) to child oracle stage.
I hope this will help you to do what you want.
I appolize for my intrusion but my question is related to approach on this topic.
Girija, by setting "Reject mode" to true on parent stage will the reject link receive every row written to parent table? If not how does it work.
Are you suggesting the following job design:
Thanks for your suggestion.
Girija, by setting "Reject mode" to true on parent stage will the reject link receive every row written to parent table? If not how does it work.
Are you suggesting the following job design:
Code: Select all
parentlink
seqfile----->Xmer------->OraEnterpStgforParent
| |
childlink | |parentrejectlink
| |
V V
JoinStg
|
|
|
V
OraEnterpStgforChild
-
- Charter Member
- Posts: 193
- Joined: Tue Sep 05, 2006 8:01 pm
- Location: Australia
Sorry for late reply !!!!!!!!!!!!!
parentlink
seqfile----->Xmer------->OraEnterpStgforParent
| |
childlink | / |parentrejectlinkrom Oracle stage
| /
| /
| /
V V
JoinStg (with sort restrict till parent finished)
|
|
|
V
OraEnterpStgforChild
Assuming that there should not be any data coming from parent reject link.
Thanks
parentlink
seqfile----->Xmer------->OraEnterpStgforParent
| |
childlink | / |parentrejectlinkrom Oracle stage
| /
| /
| /
V V
JoinStg (with sort restrict till parent finished)
|
|
|
V
OraEnterpStgforChild
Assuming that there should not be any data coming from parent reject link.
Thanks
Sorry Craig !
I think it will make more sense now.
I think it will make more sense now.
Code: Select all
parentlink
seqfile----->Xmer------->OraEnterpStgforParent
| /
childlink | / reject link from oracle stage
| /
| /
| /
| /
V V
JoinStg (Required to restrict the childlink until parent finished)
|
|
|
V
OraEnterpStgforChild