Upsert Strategy- Order of Execution

[Maharaj]

Upsert Strategy- Order of Execution


Hi

I have implemented the UPSERT strategy for the Dimension load. Iam using a oracle sequence as the surrogate key. In the Target stage i have specified seq.nextval for the surrogate key

First Run: Source Records =3

Oracle Sequence ( surrogate key)

1
2
3

Second Run : No change in the source record

Oracle Sequence ( surrogate key)

1
2
3

ThirdRun : Source records = 4( 3 old + 1 new record)

Oracle Sequence ( surrogate key)

1
2
3
7 - New record

Please let me know why this is happening , since in the second run only update is happening for the exising record why the sequence is getting incremented.

What is the order of execution of Upsert strategy ( Update and Insert / Insert and Update)

Thanks in advance
Maharaj

[dennis77]

Hi Maharaj,

This was due to the following reason.

I think u r using oracle enterprise stage. What about the commit frequency u have specified in the enterprise stage. R u commiting single record ? Or multiple records ? If you are commit frequency is greater than one in the stage , then the following value generated by oracle stage is correct.

When ever a execution happens it reads all the rows from the source stage and passes all the rows to target if you are not having any constraints in between the stages . When all rows are processed , either it will try to insert or update the record using oracle stage .

When ever insertion happens since u r having a primary key the old record will not be inserted , for the old value it will try to update the records.

When ever u r having a new record (other than the old) it will start using seq.nextval in the oracle session from the first record onwards.

So it will start 4, 5,6 for the old records and 7 for the new rows , since ur commit frequency is more than 1.

If u set commit frequency as 1 in the same session , until the new records it will retain the old value (that is 3). U can set the commit frequency as 1 using the following option in the oracle enterprise stage
(Insert Array Size - Default value of commit size is 500 records. If you want each insert statement to be executed individually,
specify 1 for this property) .

Either try it out changing the commit frequency value or have 2 enterprise stage in oracle , 1 for insert and one for update (which will always increase ur performance too).

[vcannadevula]

THough we are specifying the commit interval as 500. Let us say it has inserted 3 records, but once the job is finished , doesn't it commit.
I beleive it should .............

[chulett]

Of course... every 500 records plus when the stage 'finishes' normally a commit will be performed.