IBM Support

Pre-processing the search index fails with 'Error code -302, SQLSTATE=22001'

Technote (troubleshooting)


Problem(Abstract)

The di-preprocess utility fails with the following error:

INFO: DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null,
DRIVER=4.12.55

com.ibm.commerce.foundation.dataimport.preprocess.DataImportPreProcessor
Main logExitCode
FINER: ENTRY 1

com.ibm.commerce.foundation.dataimport.util.DataImportHelper getLocalizedMessage
FINER: ENTRY _INFO_DI_PREPROCESSING_EXIT_FAILURE_UNRECOVERABLE_ERROR

Symptom

If using an Oracle database, the equivalent Oracle error might resemble the following error:

com.ibm.commerce.foundation.dataimport.preprocess.DataImportPreProcessorMain processDataConfig(DataProcessingConfig, String)
INFO: ORA-01461: can bind a LONG value only for insert into a LONG column


Cause

Data being inserted into the database table column is larger than the assigned column length.

Diagnosing the problem

The di-preprocess utility first creates, then populates a number of temporary tables. When this error occurs, preprocessing fails when populating the temporary tables. To resolve this error, the table column must be redefined to increase the allocated size of the column.

1. The failing table first must be identified. Enable more detailed tracing for di-preprocess:

Navigate to :

WC_installdir/instances/instance_name/xml/config/dataimport

and open the logging.properties file. Find all instances of INFO, and change it to FINEST. Optionally increase the size of the log file, and the number of historical log files while editing this file.

2. Re-run di-preprocess to gather more detailed logs

3. Open the log files, and find the last table that was created. An entry such as the following should be in the log:

"FINER: ENTRY <table name> CREATE TABLE <table name> ..."

For example:

FINER: ENTRY TI_APGROUP_0 CREATE TABLE TI_APGROUP_0 (CATENTRY_ID BIGINT NOT NULL, CATGROUPS VARCHAR(1024), PRIMARY KEY (CATENTRY_ID))

Typically there are not many columns in the temporary tables, and it is evident which column needs an increased size. Using the example, the CATGROUPS column is too small.


Resolving the problem

With the table and column identified, the next step is to increase the size of the column during table creation.


The tables are created in preprocessing XML files, located in the following directory:

WC_installdir/instances/instance_name/search/pre-processConfig/MC_MasterCatalogID>/Database type/

Search through the preprocess XML files until you find the statement creating the table. Continuing the example, the TI_APGROUP_0 table is created in the wc-dataimport-preprocess-parent-catgroup.xml file.

Find the line that creates the table. For example:

<_config:table definition="CREATE TABLE TI_APGROUP_0 (CATENTRY_ID BIGINT NOT NULL, CATGROUPS VARCHAR(4000), PRIMARY KEY (CATENTRY_ID))" name="TI_APGROUP_0"/>

Change to a CLOB:

<_config:table definition="CREATE TABLE TI_APGROUP_0 (CATENTRY_ID BIGINT NOT NULL, CATGROUPS CLOB, PRIMARY KEY (CATENTRY_ID))" name="TI_APGROUP_0"/>

You must change an additional file for di-buildindex to work. Find the wc-data-config.xml for the specific index being changed. This file is located in the following directory:

../solr/home/MC_MasterCatalogID/locale/indexType/conf/wc-data-config.xml

Open the file, and locate the altered column in the select statement. In this example, the SQL statement renames the CATGROUPS column to APCATGROUP:

...
TI_DPGROUP.CATGROUP DPCATGROUP,
TI_APGROUP.CATGROUPS APCATGROUP,
TI_PRODUCTSET.PRODUCTSET,
...

Find the line that maps the database column to the index field:

<field column="parentCatgroup_id_search" splitBy=";" sourceColName="APCATGROUP"/>

Above this mapping, add a 'clob =true' statement:

<field column="APCATGROUP" clob="true"/>

The SOLR DIH process requires this line to map the CLOB database type to a string.

Related information

A simplified Chinese translation is available

Document information

More support for: WebSphere Commerce Enterprise
Catalog

Software version: 7.0.0.3, 7.0.0.4, 7.0.0.5

Operating system(s): AIX, IBM i, Linux, Solaris, Windows

Software edition: All Editions

Reference #: 1585238

Modified date: 15 July 2014


Translate this page: