DB2 error SQLCODE=-803, SQLSTATE=23505 occurs during di-preprocess *|* -CA5E1F1396D34478852578EA0082B870- *|*

After di-preprocess fails to run successfully you see an error message similar to the following example in the logs:

DB2 SQL Error: SQLCODE=-803 SQLSTATE=23505 SQLERRMC=1;DB2INST1.TI_APGROUP_0 DRIVER=4.12.55

The DB2 error states that DB2 failed to insert the new record into the due to a primary key constraint violation. This means that there is already a record in the TI_APGROUP_0 table that has the same CATENTRY_ID value as the record you are attempting to insert.

This issue relates to the new search tool used in Feature Pack 2 and newer Feature Packs of WebSphere Commerce V7.0.

There are two main causes for this issue. Either a catalog entry (catentry) has multiple parent categories or a category has multiple parent categories.

IDENTIFYING THE CAUSE:

To identify if there is a catalog entry that has more than one parent category run the following SQL query against the database:

select catentry_id catgroup_id from catgpenrel where catentry_id in (select catentry_id from catgpenrel where catalog_id = <catalog_id> group by catentry_id having count(catentry_id) > 1)

where <catalogId> is the catalog ID value for which di-preprocess is being run.

The above query will provide a list of catalog entry IDs and the categories that they are mapped to if they are mapped to more than one category in the master catalog ID.

To identify if the issue is caused by a category having multiple parent categories in the master catalog run the following SQL query against the database:

select catgroup_id_parent catgroup_id_child from catgrprel where catalog_id = <catalogId> and catgroup_id_child in (select catgroup_id_child from catgrprel where catalog_id = <catalogId> group by catgroup_id_child having count(catgroup_id_child) > 1)

COLLECTING MORE DATA:

Alternatively you can collect more logging data in the di-preprocess process to identify the suspect category and catalog entry experiencing the issue.

In <WC_installdir>/instances/<instanceName>/search/pre-processConfig/MC_<catalogId>/<target_db>

For Feature Pack 2 modify the wc-dataimport-preprocess-common.xml file.

For Feature Pack 3 modify the wc-dataimport-preprocess-parent-catgroup.xml file.

1. In the section:
   
   <_config:data-processing-config processor="com.ibm.commerce.foundation.dataimport.preprocess.CatalogHierarchyDataPreProcessor" masterCatalogId="10001" batchSize="10000">
   
   <_config:table definition="CREATE TABLE TI_APGROUP_0 (CATENTRY_ID BIGINT NOT NULL CATGROUPS VARCHAR(4000) PRIMARY KEY (CATENTRY_ID))" name="TI_APGROUP_0"/>
   
   <_config:query sql=""/>
   
   Where the batchSize value is set change the value to batchSize="1".
2. In <WC_installdir>/instances/<instanceName>/xml/config/dataimport modify logging.properties. Change java.util.logging.FileHandler.level to FINEST:
   
   java.util.logging.FileHandler.level=FINEST
   
3. Also within the logging.properties file change the java.util.logging.FileHandler.limit to 250000000 and java.util.logging.FileHandler.count to 10:
   
   java.util.logging.FileHandler.limit=250000000
   java.util.logging.FileHandler.count=10
   
   This will increase the logging however there would be a lot of data logged. In this case it would log 2.5GB of data across 10 files.
4. Then reproduce the di-preprocess issue. After di-preprocess fails examine the wc-dataimport-preprocess.log* files. Find the line that has the following error:
   
   INFO: DB2 SQL Error: SQLCODE=-803 SQLSTATE=23505 SQLERRMC=1;DB2INST1.TI_APGROUP_0 DRIVER=4.12.55
5. Parse the lines above it to look for the last record that the operation attempted to insert. For example:
   
   0 Aug 11 2011 7:05:33 PM com.ibm.commerce.foundation.dataimport.preprocess.CatalogHierarchyDataPreProcessor fetchChildCatalogEntries
   FINER: RETURN [12345]
   0 Aug 11 2011 7:05:33 PM com.ibm.commerce.foundation.dataimport.preprocess.CatalogHierarchyDataPreProcessor populateTable
   FINER: ENTRY
   0 Aug 11 2011 7:05:33 PM com.ibm.commerce.foundation.dataimport.preprocess.CatalogHierarchyDataPreProcessor logCacheContents
   FINER: ENTRY
   0 Aug 11 2011 7:05:33 PM com.ibm.commerce.foundation.dataimport.preprocess.CatalogHierarchyDataPreProcessor logCacheContents
   FINEST:
   -----------------------------------------------------------
   Cache contents
   -----------------------------------------------------------
   12345=10001_12321;10001_10002;10001_10005
   -----------------------------------------------------------

For each category ID presented you can check the values against the values in the CATGRPREL table to confirm that there is only one entry for the category in question to ensure that there is only one CATGROUP_ID_PARENT. For example:

select CATGROUP_ID_PARENT CATGROUP_ID_CHILD CATALOG_ID from CATGRPREL where CATGROUP_ID_CHILD = <categoryId> and CATALOG_ID = <catalogId>

For the catalog entry id presented it can be checked against the CATGPENREL table to confirm that there is only one entry for the catalog entry id associated to only one category for the master catalog. For example:

select CATGROUP_ID CATALOG_ID CATENTRY_ID from CATGPENREL where CATENTRY_ID = <catalog entry id> and CATALOG_ID = <catalogId>

After you identify the multiple parent categories for the catalog entry or child category you need to make the appropriate corrections so that there is only one parent for the catalog entry or child category. Having multiple direct parent categories for a single catalog entry or a single child category in the master catalog is not supported.