Technote

(FAQ)
WebSphere Commerce Analyzer V5.6 gives duplicate key exceptions on the WCA.SUM_MEMBER table during replication
Problem
The following error is shown in the log files during the first run of replication:

#- 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.SUM_MEMBER" from having duplicate rows for those columns. SQLSTATE=23505
Cause
The cause of the problem was the failure of the following insert statement:

insert into wca.sum_member (MEMBER_ID , SUM_RPT_SALES_VAL , SUM_NUM_ORDERITEMS , SUM_NUM_ORDERS , REPORT_CURRENCY , LATEST_ORDER_DATE , ORG_USER , last_updated, last_updated_id)
(SELECT WCA.FACT_ORDERITEMS.MEMBER_ID AS MEMBER_ID, SUM(WCA.FACT_ORDERITEMS.RPT_SALES_VALUE) AS SUM_RPT_SALES,
COUNT(DISTINCT WCA.FACT_ORDERITEMS.ORDERITEMS_ID) AS COUNT_ORDERITEMS,
COUNT(DISTINCT WCA.FACT_ORDERITEMS.ORDER_ID) AS COUNT_ORDER,
WCA.FACT_ORDERITEMS.REPORT_CURRENCY AS REPORT_CURRENCY,
max(WCA.FACT_ORDERITEMS.CREATED_TIME) AS LAST_ORDER_DATE,
wca.member.org_user as org_user, CURRENT TIMESTAMP AS LAST_UPDATED,
1 AS LAST_UPDATED_ID
FROM WCA.FACT_ORDERITEMS, WCA.MEMBER
where WCA.FACT_ORDERITEMS.member_id = wca.member.member_id and
WCA.FACT_ORDERITEMS.Deleted_Status=0 and
WCA.FACT_ORDERITEMS.ORDER_STATUS_ID IN
(SELECT INTEGER(P.PARAM_VALUE)
FROM WCA.PARAMETERS P
WHERE P.PARAM_TYPE='ORDER_STATUS_ID_SUM_MEMBER') and
wca.fact_orderitems.member_id in
(select distinct wca.fact_orderitems.member_id
from wca.fact_orderitems
where last_updated >= timestamp(wca.param_value('TIME_CUT_OFF_LOCAL') ) )
GROUP BY WCA.MEMBER.org_user, WCA.FACT_ORDERITEMS.MEMBER_ID, WCA.FACT_ORDERITEMS.REPORT_CURRENCY )

Solution
To determine what constraints exist for the table, run the following command : > db2 describe indexes for table wca.sum_member show detail 

Index Index Unique Number of
schema name rule columns Column names
------------------------------- ------------------ -------------- -------------- ---------------------------
SYSIBM SQL060428142910310 P 1 +MEMBER_ID

1 record(s) selected.

After retrieving and analyzing all the tables mentioned in the INSERT statement above, the WCA.FACT_ORDERITEMS table showed a single MEMBER_ID with multiple REPORT_CURRENCY values.

WebSphere Commerce Analyzer will support only one report currency, which is selected from the store currency panel of WebSphere Commerce Analyzer during configuration. This problem could arise if the report currency is changed and configuration is run again, but this scenario is not supported.

To determine which currency was selected during configuration, the following SQL statement can be issued:

select * from wca.parameters where param_type = 'REPORT_CURRENCY'; 

The solution is to reconfigure the WebSphere Commerce Analyzer database to have only one currency. This can be done in two ways:

You can update the report currency for all orders manually or reconfigure WebSphere Commerce Analyzer using the WebSphere Commerce Analyzer Configuration Manager.

To reconfigure WebSphere Commerce Analyzer using the WebSphere Commerce Analyzer Configuration Manager:
1) Back up your current WebSphere Commerce Analyzer database.
2) Start the WebSphere Commerce Analyzer Configuration Manager.
3) Enter information and execute each step as in the initial configuration. When asked if you want to drop the existing database, click Yes.
4) At the last screen of the configuration, select store, report language, and currency. Ensure that you choose the desired currency for your report currency.

 

Document Information

Current web document: http://www.ibm.com/support/docview.wss?uid=swg21239764