WebSphere Commerce Analyzer ETL fails with a duplicate key exception Technote
(troubleshooting)Problem(Abstract) While running ETL, the following error occurs during extraction of the store_rel :
[etl] Running Extraction Step: 22/86 (store_rel)
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0803N 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 "WCA.STORE_REL" from having duplicate rows for those columns. SQLSTATE=23505
Cause To determine what unique constraint is getting violated, run the following SQL query on the data mart database. > db2 describe indexes for table wca.store_rel show detail
The output should be similar to this:
Index Index Unique Number of
schema name rule columns Column names
------------------------------- ------------------ -------------- -------------- -------------------------------------
SYSIBM SQL061026152047570 P 3 +STRELTYP_ID+RELATEDSTORE_ID+STORE_ID
So, some of the data that was replicated is causing a duplicate key exception based on the unique index STRELTYP_ID, RELATEDSTORE_ID and STORE_ID.Resolving the problem A duplicate key exception indicates that the data is already entered in the WCA.STORE_REL table. Thus, for existing entries, their LOAD_STATUS and IBMSNAP_OPERATION can be updated so that ETL will treat this row as an update rather than an insert. Run the following SQL query against the data mart: update IWH.STOREREL_R STR set load_status=(INTEGER((select param_value from wca.parameters where param_type='EXTRACTION_COUNT'))-1) , ibmsnap_operation='U' where (STR.STRELTYP_ID, STR.RELATEDSTORE_ID, STR.STORE_ID) in (select STRELTYP_ID, RELATEDSTORE_ID, STORE_ID from wca.store_rel) and STR.load_status=-1;
This SQL query should update the LOAD_STATUS to a number greater than -1, because the EXTRACTION_COUNT, which indicates the number of times WebSphere Commerce Analyzer ran, should be a value greater than 0.
After you run the previous SQL query, continue with the ETL step where it failed. It should now pass and continue to the next step.
Document Information Current web document: http://www.ibm.com/support/docview.wss?uid=swg21251033