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??
Primary Key violation on Update/Insert
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 40
- Joined: Thu Mar 30, 2006 6:30 am
- Location: Near Geneva, Switzerland
- Contact:
Primary Key violation on Update/Insert
Wade Walker
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: