Loading parent/child tables in one job

jherr22 · Post by **jherr22** » Thu Sep 25, 2008 8:41 am

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?

shamshad · Post by **shamshad** » Thu Sep 25, 2008 12:18 pm

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

girija · Post by **girija** » Thu Sep 25, 2008 2:17 pm

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.

csri · Post by **csri** » Thu Sep 25, 2008 3:44 pm

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:

Code: Select all

                 parentlink   
seqfile----->Xmer------->OraEnterpStgforParent
             |  |
   childlink |  |parentrejectlink
             |  |
             V  V
            JoinStg  
              |                     
              |                     
              |                     
              V                     
  OraEnterpStgforChild

Thanks for your suggestion.

John Smith · Post by **John Smith** » Thu Sep 25, 2008 10:35 pm

How about turning off your FK constraint and loading both tables and then reenable FK post completion of your job?

jherr22 · Post by **jherr22** » Fri Sep 26, 2008 8:55 am

girija: Thanks! Will this work even though nothing will go down the reject link? (I think this is what csri is getting at).

Smith: We thought of disabling the FK as you mentioned, but the powers that be will not allow this.

girija · Post by **girija** » Fri Oct 10, 2008 8:41 am

chulett · Post by **chulett** » Fri Oct 10, 2008 8:52 am

Sheesh... don't people 'preview' what they are going to post? ASCII art requires the "diagram" be enclosed in code tags to retain all of that loverly whitespace so the picture makes some sense.

girija · Post by **girija** » Fri Oct 10, 2008 9:14 am

Sorry Craig !

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

chulett · Post by **chulett** » Fri Oct 10, 2008 9:22 am

There ya go!