Primary Key violation on Update/Insert

[wwalker]

We have an interesting problem that I am having trouble understanding...

We have an environment that has been running daily for about 9 years and is quite stable. However, yesterday, on a load of about 100,000 records, a single record failed with :

[DataStage][SQL Client][ODBC][Microsoft][SQL Native Client][SQL Server]Cannot insert duplicate key row in object 'dbo.L1D1_xxxx' with unique index 'L1D1_xxxx_IDX_xxxx_NO'.
SQLSTATE=01000, DBMS.CODE=3621

I have confirmed that the record exists at source and target with the identical DWID key, and the FKY lookup for this key correctly worked (91,357 existing DWID lookups succeeded out of 91,365 total records...in other words, only 8 new incoming records). There is only one instance of this record being written to the target.

Normally, this should not be an issue, as with Update/Insert (and also Insert/Update which also fails on this record), the Insert should fail and then pass to the Update resulting in a successful load.

Looking at the target database, we see that the record was updated the previous day successfully before failing the following day.

I did experiment with commit intervals also, setting Rows per Transaction to 1 (had been 0). Same effect...

There is definitely a problem with this record, as this same error occurs on the same line in QA and in PRD...

Any of those good brains out there experience something similar??

[Sainath.Srinivasan]

As the jobs are working fine for more than 9 years, it is hard to nail down.

Anyhow first things comes to mind is
1.) NULL in data
2.) Trim spaces
3.) More than actual key columns defined
4.) Change capture values incorrect.

[wwalker]

This was a data related issue...the UPDATE failed on a unique index due to a duplicate SAP customer number..which should theoretically be impossible...perhaps someone manually "enhanced" the data?

Thanks...L8r!

Wade

[ray.wurlod]

I think it's important to discover whether this was, in fact, the case so that you can educate your users about the dangers of manually enhancing data.