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?
Load aborting due to duplicate keys values
Moderators: chulett, rschirm, roy
Re: Load aborting due to duplicate keys values
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.
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?
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?
"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.
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.
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. "
"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. "