order of execution of Upsert strategy

[Maharaj]

Hi

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

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

[bgs]

Order of execution is update and then insert

[vcannadevula]

Hi

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

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

I assume you should be having higher commit interval and that is the only reason i am able to see for this to occur. When u run the job for the third run the sequence will start with 4,5,6,7 ........As the commit interval is higher......it will update the existing rows not the key fields and then insert the 4th which is new with value 7. If you set the commit interval to 1 it will not happen.

This query was raised earllier and Sai has given a good explanation too

[dsxuserrio]

hgs
I am thinking insert is done first. If insert is unsuccessful then update takes place.

[bgs]

dsxuserrio
I think it will first search if the record is present,and if it finds then it updates else it inserts.

[dsxuserrio]

bgs
Take a look at the following post

viewtopic.php?t=91256&

You have to have a unique index or a primary key constraint over the 'key' fields so that Oracle (and DataStage) has a clue that the record is either new or a duplicate. My guess is you don't.

It will only perform the second action, in this case the update, if the first action (the insert) fails - that is the only way it knows that it is not 'new' and that it needs to perform the update action. The failure in this case that would trigger the update would be a unique index / duplicate key violation.