Load aborting due to duplicate keys values

[Marley777]

Hi,when loading data from a file into a db2 table I get the following error.

"One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "XXXXXXX" from having duplicate values for the index key. SQLSTATE=23505 (CC_DB2DBStatement::executeInsert, file CC_DB2DBStatement.cpp, line 657) [pxbridge.C:5685]"

Anyone know how can I ask datastage to show me in the log the record that is a duplicate? A server job will show you the record in the log but a PX job will not for us. Is there an env variable that we need to set?

[marothisu]

In my experience, for each of these errors you're getting, there should be another error/warning right under it. Right click and go to Detail on that. it should give you a list of all the values involved in the insert statement.

[Marley777]

There are no records in the log giving us a hint to which record is the duplicate. Has anyone else experienced this? What did you do to fix?

[marothisu]

There are no records in the log giving us a hint to which record is the duplicate. Has anyone else experienced this? What did you do to fix?

You're sure? I encounter this quite a bit and there is always a separate warning message associated with each of these.

[Marley777]

This is all we get in the warning message.

lod_DMTEMP_UW_EXPENSE_FACT,0: Statement INSERT INTO DMTEMP.UW_EXPENSE_FACT(AGCY_ID,RISK_LOC_ID,REINS_ID,MEASURE_ID,ACCT_DT_ID,AMT,UOB_GRP_ID) VALUES(?,?,?,?,?,?,?) failed to run. [:0]

We are loading using a db2 connector stage. Not sure why we don't get a record in the log pointing us to the duplicate record?

[marothisu]

Interesting. Personally, we use the DB2 UDB API stage to insert data into tables. Not sure if this is what you're referring to, but with that I usually have another message associated telling me (when I click on its details) the values of the insert.

[Marley777]

"One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "XXXXXXX" from having duplicate values for the index key. SQLSTATE=23505 (CC_DB2DBStatement::executeInsert, file CC_DB2DBStatement.cpp, line 657) [pxbridge.C:5685]"



Found a way to see errors in the log. I'm using a DB2 Connector stage. If you don't set a reject link from this stage you will no get reject records in the job log.

Open the db2 connector stage properties.

On the top left part of the properties window you will see the connector stage with two links. Click on the output link and it will open a Reject tab. Select the conditions for the reject link.

[Marley777]

My problem was I couldn't see rejected records in the logs like I could in the server environment. But figured out what I was missing when using the PX DB2 Connector stage, wrote the following, and resolved the case.

"Found a way to see errors in the log. I'm using a DB2 Connector stage. If you don't set a reject link from this stage you will no get reject records in the job log.

Open the db2 connector stage properties.

On the top left part of the properties window you will see the connector stage with two links. Click on the output link and it will open a Reject tab. Select the conditions for the reject link. "