September 27, 2013 at 3:39 am
Hi
I am having problems with the "Duplicate key was ignored" warning message. The problem is that the message seems to happen randomly and cannot be reproduced. If i take the same set of data and run the stored procedure that causes the problem i don't get the warning message a second or subsequent time. Also all the SELECT statements have criteria set to remove duplicates before they are inserted into the tables.
Background to the problem:
I have a data feed that pulls data from a DB2 database to a SQL Server 2008 staging table as a flattened set of records. A stored procedure in SQL Server is run to load the data into the destination tables. The data feed is run hourly for new and updated records in DB2 Monday-Friday 09:00-17:00 and then there is a midnight run of all the records going back for the last 12 months.
The data feed was originally sent from DB2 as a CSV file and pulled into SQL Server using SSIS but is now an Informatica workflow that pulls the data directly from DB2.
It is the Informatica workflow that is returning the "duplicate key was ignored" warning message and this stops the workflow. The workflow is restarted and the data is always loaded the second time without the warning message. The warning does not happen every time the workflow is run - it can run for a number of days with no warnings and then one will come through
I can see in Profiler that it is SQL Server that returns the Duplicate key was ignored warning message so it is not an issue with Informatica.
I cannot reproduce the problem to get to the root cause of the issue. I would expect that if i run the same set of data through the stored procedure i would get the warning message every time, but this is not the case. Even when i step through the stored procedure i do not get the message. As the midnight data feed returns the records from the last 12 months, so by definition would include duplicates, the warning message only appears randomly and is not consistent.
Is this a bug in SQL Server or does anyone have a suggestion of where else to look?
September 27, 2013 at 3:53 am
jatighe (9/27/2013)
HiI am having problems with the "Duplicate key was ignored" warning message. The problem is that the message seems to happen randomly and cannot be reproduced. If i take the same set of data and run the stored procedure that causes the problem i don't get the warning message a second or subsequent time. Also all the SELECT statements have criteria set to remove duplicates before they are inserted into the tables.
Background to the problem:
I have a data feed that pulls data from a DB2 database to a SQL Server 2008 staging table as a flattened set of records. A stored procedure in SQL Server is run to load the data into the destination tables. The data feed is run hourly for new and updated records in DB2 Monday-Friday 09:00-17:00 and then there is a midnight run of all the records going back for the last 12 months.
The data feed was originally sent from DB2 as a CSV file and pulled into SQL Server using SSIS but is now an Informatica workflow that pulls the data directly from DB2.
It is the Informatica workflow that is returning the "duplicate key was ignored" warning message and this stops the workflow. The workflow is restarted and the data is always loaded the second time without the warning message. The warning does not happen every time the workflow is run - it can run for a number of days with no warnings and then one will come through
I can see in Profiler that it is SQL Server that returns the Duplicate key was ignored warning message so it is not an issue with Informatica.
I cannot reproduce the problem to get to the root cause of the issue. I would expect that if i run the same set of data through the stored procedure i would get the warning message every time, but this is not the case. Even when i step through the stored procedure i do not get the message. As the midnight data feed returns the records from the last 12 months, so by definition would include duplicates, the warning message only appears randomly and is not consistent.
Is this a bug in SQL Server or does anyone have a suggestion of where else to look?
Show us the code??..thanks
September 27, 2013 at 4:15 am
Sounds like you have a unique index with IGNORE_DUP_KEY set to ON. You get this warning when, and only when, you attempt to insert something that would violate the uniqueness of the index. Have a look at the CREATE INDEX topic in Books Online for more information.
John
September 27, 2013 at 4:33 am
Hi John
You are correct i do have some of the tables set to ignore duplicates - on my original data feed that used SSIS i used that switch to lazily remove the duplicates. SSIS did not stop when the duplicate key warning message was returned, but Informatica does as this uses ODBC to connect to SQL Server rather then a native SQL Server driver. ODBC does not differentiate between a warning and an error.
Because of the change in the data feed i updated all the SELECT statements to remove the duplicates - and tested them to make sure this worked.
It is the randomness of the problem that I am struggling with. The Informatica workload is restarted each time it fails and it always works the second time it runs using the same records, but I would expect it to fail every time once a duplicate has been found. This is not the case so when I try to run the SP directly I never get the warning message.
September 27, 2013 at 4:40 am
OK, as has previously been requested, please will you post the definition of the stored procedure you mentioned?
John
September 27, 2013 at 5:21 am
jatighe (9/27/2013)
It is the randomness of the problem that I am struggling with. The Informatica workload is restarted each time it fails and it always works the second time it runs using the same records, but I would expect it to fail every time once a duplicate has been found. This is not the case so when I try to run the SP directly I never get the warning message.
Two things ,
1) Are you sure that you are populating the DUPLICATE data ? have you verified that .
2) You can set the option Turncate and load in your informatica mapping.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 27, 2013 at 8:07 am
Ok I have attached the 2 SP that carries out the transformation from the de-normalized data that I receive from the DB2 database into the normalized structure for my application - which is why there is a lot INSERT and UPDATE commands.
September 27, 2013 at 8:23 am
OK, so you have a staging database and a live database, right? And one of those procs runs to "tidy up" the staging database before the other one runs to copy the data into the live database? I would expect that if you run that lot twice, you would start getting duplicates. How many of your indexes have IGNORE_DUP_KEY set to ON? If you temporarily set it to OFF then the error message would tell you which are the offending indexes.
John
September 27, 2013 at 8:25 am
i dont think only SP definiton will provide much help here . Exec plan will be required.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 27, 2013 at 8:31 am
Bhuvnesh (9/27/2013)
i dont think only SP definiton will provide much help here . Exec plan will be required.
Do you think so? The warning messages occur at run time, which is after the plan has compiled. Table and index DDL would be more helpful, although I fear that it would be so complex that we'd lose the will to live. That's why I recommended starting with the indexes that filter out duplicates.
John
September 27, 2013 at 8:57 am
John Mitchell-245523 (9/27/2013)
Bhuvnesh (9/27/2013)
i dont think only SP definiton will provide much help here . Exec plan will be required.Do you think so? The warning messages occur at run time, which is after the plan has compiled. Table and index DDL would be more helpful, although I fear that it would be so complex that we'd lose the will to live. That's why I recommended starting with the indexes that filter out duplicates.
John
Ooopppss. yes i mis-directed the post some where else. 😀
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 27, 2013 at 9:15 am
The high level process for the incremental and full download is:
1) Informatica deletes data in the destination table on the staging SQL Server
2) Informatica on the runs the incremental or full stored procedure on the DB2 source database, loading the flat data into the destination table on the staging SQL Server
3) Informatica runs the import SP on the staging SQL Server which is a wrapper for the SP: PROC_IMPORT_INFORMATICA_PRE_LOAD_UPDATE and PROC_IMPORT_INFORMATICA_DATA and then copies the data to a history table
I could put a TRY...CATCH around the SQL statements but that would not catch the Duplicate Key was Ignored as this is a warning and not an error.
If i took one of the SQL INSERT blocks as an example:
INSERT INTO dbo.PORT
(
Port_Code ,
Port_Name ,
Blocked_Ind
)
SELECT LOAD_PORT ,
LOAD_PORT AS PORT_NAME ,
0 AS BLOCKED
FROM TRE3BFRP.dbo.DEX_IMPORT_INFORMATICA AS DI
WHERE LOAD_PORT NOT IN ( SELECT port_code
FROM dbo.port )
GROUP BY LOAD_PORT
and removed the WHERE clause, when i run this against some imported data i will always get the Duplicate Key Was Ignored warning. With the WHERE clause I never get the warning.
There are some triggers on some of the tables which are not disabled during the import which I thought might have confused SQL server, but when i look at the profiler results of the SP the server seems to run the SQL in the right order.
It is a very frustrating problem!
September 27, 2013 at 12:37 pm
Can you provide the table definition for PORT?
I ran into an issue like this where by there were nulls in the source data. Once I excluded them I had no further issues.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply