odbc problem with DB2 cannot upsert due to Index error

abhilashnair · Post by **abhilashnair** » Sun Apr 03, 2011 2:39 am

I am trying to load DB2 table in Upsert method. Updates are happening first and then Inserts. I am getting the error

INDEX1 RESTRICTS COLUMNS WITH SAME VALUES. TABLE NAME

But if it is Update Then Insert then why is this issue happening?
Even if there are duplicates in my source, the problem should happen only if the Option selected was Insert Then Update or Insert Only

Not sure why it is happening here though?

abhilashnair · Post by **abhilashnair** » Sun Apr 03, 2011 11:23 pm

When I changed the partitioning to Hash, hash keys were same as the update keys used in the query, the error went away.

This is strange. The partitioning was Auto previosuly and the job used to work fine. Can this be explained?

blewip · Post by **blewip** » Mon Apr 04, 2011 4:52 am

Is it possible the input data has duplicate rows. Therefore there are two copies of the same primary keys. Both are tried to be updated and fail, so both become inserts?

I know strictly speaking you may not expect this to happen.

abhilashnair · Post by **abhilashnair** » Mon Apr 04, 2011 5:24 am

Assuming this to be true, Do you mean both duplicates tried to insert at the same time ? How did Hash Partition resolve this then ?

blewip · Post by **blewip** » Wed Apr 06, 2011 5:04 am

Not necessarily. I was saying that both updates were tried (and failed) before either insert occurred.

I take it that de-duping the data would also fix the problem.