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

Technote (troubleshooting)


Problem(Abstract)

You attempt to run di-preprocess against the master catalog on IBM WebSphere Commerce Enterprise V7.0 Feature Pack 2, or a later feature pack, but the operation fails with the DB2 error SQLCODE=-803, SQLSTATE=23505 on TI_APGROUP_0.

Symptom

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

Cause

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.

Environment

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

Diagnosing the problem

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
    -----------------------------------------------------------

In the above example, it indicates that DB2 failed to insert catentryId 12345. In the cache content section the data is formatted as: catalogId_categoryId with semicolons separating the different entries. This is the category chain in which it traverses. The first entry would be the direct parent category of the catalog entry. The second entry would be the top level category in which ecompasses all the subcategories, one of which contains the catalog entry. The remainder of the string would be the subcategory tree in which would be traversed from the top level category to reach the parent category.

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>


Resolving the problem

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.

Rate this page:

(0 users)Average rating

Document information


More support for:

WebSphere Commerce Enterprise
Feature Packs / Enablement

Software version:

7.0

Operating system(s):

AIX, Linux, Solaris, Windows, i5/OS

Reference #:

1508894

Modified date:

2012-11-13

Translate my page

Machine Translation

Content navigation