Update then insert not working in 8.7

chulett · Post by **chulett** » Fri Mar 08, 2013 8:10 am

Hmmm... hard to say based on what was posted. For a "combo" action to work the first action must fail before the second option will fire. So the update must update zero records before the insert is attempted. It sounds like that happens and then you get an PK violation on the insert, yes?

Your PK column, is it a surrogate key? If so, how are you handling it? And what is your "key" field for the update - one or more business key values?

prasannakumarkk · Post by **prasannakumarkk** » Fri Mar 08, 2013 9:17 am

1) Have you specified auto partition in the transformer stage.
2) Did you check the key option for the primary key column in connector stage.

You have mentioned there are duplicates in the source. If there are duplicates, which records must get inserted and which must be updated.

akarsh · Post by **akarsh** » Sun Mar 10, 2013 9:36 am

No Its not a primary key. Its coming in file itself. an may come as duplicate.
that's the reason i want to use update then insert.

akarsh · Post by **akarsh** » Sun Mar 10, 2013 9:38 am

yes, in tfm its auto and in connector its hash partition.

there is no such requirement which need to be updated.

chulett · Post by **chulett** » Sun Mar 10, 2013 2:45 pm

Hey, you're the one that said you had a "single primary key (numeric) column in table".

So these duplicates... do have the duplicates in the file itself, or is there just a single instance of the key value in the file which may or may not exist in the table? The answer changes how you need to handle this.

chandra.shekhar@tcs.com · Mon Mar 11, 2013 12:52 am

@akarsh
We have also experienced the same issue while updating data on our DB2 tables. Try one of these solution :-
1) Use Oracle Connector Partitioning instead of Hash Partitioning in the target.
OR
2) Reduce the "Array Size" to 1, keeping Record Count to higher value e.g 2000.
OR
3) Run your job on a single node.

akarsh · Post by **akarsh** » Tue Mar 12, 2013 2:10 am

chulett wrote:So these duplicates... do have the duplicates in the file itself, or is there just a single instance of the key value in the file which may or may not exist in the table? The answer changes how you need to handle this.

We have duplicate key values in file itself.

akarsh · Post by **akarsh** » Thu Mar 14, 2013 3:41 am

Nothing worked out.

Finally had to split the job and create separate update and insert job

Thanks all for your suggestions.

chandra.shekhar@tcs.com · Thu Mar 14, 2013 7:36 am

@akarsh
Did you try any of my options ?

akarsh · Post by **akarsh** » Tue Apr 30, 2013 7:55 am

Yeah.. but didn't work as expected