insert into tables w/ RI

[htrisakti3]

the job needs to insert into 2 tables with Referential Integrity constraints (ie: insert into table tCustActv must have reference to existing tCust )

Code: Select all

(if isNewCust)
[T1]-l1-> tCust (insert)
[  ]-l2-> tCustActv (insert)

my question is when i have new Cust record & I need to create both tCust & tCustActv in the same job.
How do i guarantee that insert into tCust (l1) happens before insert into tCustActv (l2) - otherwise Oracle will trap the error.

thanks - HT

[ArndW]

You can specify two output links and set the order in which they get executed. You will also need to ensure that your commit frequency is each row so that an immediate commit is done from one link before it's successor gets executed.

[chulett]

You will also need to ensure that your commit frequency is each row so that an immediate commit is done from one link before it's successor gets executed.

Need? No. You can certainly do it that way, but it's slow and complicates your start/recovery options.

Two links into one OCI stage. This will allow both operations to be aware of each other as they will be part of a single 'transaction' or unit of work. This way you can still use a high (or zero) transaction size without violating RI.

[ArndW]

Craig - oops, I wasn't thinking when I replied to that one. I must have gotten some decaf instead of the normal full-octance stuff that keeps me going.

[htrisakti3]

can i ask for clarification..

in the example above, do i simply send both output of [l1] & [l2] into OCI.
And do I need "Enable transaction grouping" ticked ?

But then, if I do this, I must use "Rows per transaction" = 1 ??
Won't this be really slow ?

internally in Oracle, how does it handle it?
do the parent records need to be commited for the children records to be inserted ?

Thanks
HT

[chulett]

Ideally, yes - it can be as simple as sending both output links to the same OCI stage, properly ordered. It's not a 100% guarantee of error free operation, however.

I generally populate parent tables first and then child tables using separate jobs. However, when you do both in the same job that 'one stage' approach would be the approach I'd take first. For pure inserts you should be fine, I believe the only time I've had 'issues' was mixing updates and/or deletes into the same parent/child processing.

Transaction Grouping is another animal, read up on it in the online help to understand when to use it. Typically you would when you need a 'group' of related links to either all be commited together or all rolled back if any one fails - and this is done on a row by row basis. Meaning, the 3rd record processed might fail in one link so you want all links to be rolled back for that record and then continue on to process the 4th record. To do that it needs to commit on every record so the rollback can effect only the current record and not all records.

Depending on exactly what you are doing, there are times when it pays to commit the parent records before the child records are processed. As noted, I generally accomplish that by using two jobs and by processing all parents first. Doing both inserts (parent and child) in one job using one stage allows the child to 'know' the parent is there even though it hasn't been commited yet because they are part of the same unit of work.

Bottom line is it works the vast majority of the time from my experience. You don't need Transaction Grouping enabled or a Transaction Size (commit level) of 1 either. Give it a shot! What's the worst thing that could happen?