WebSphere Commerce 7 | Solr | Preprocess failed to load TI_CATGPENREL_0 | ERRORCODE=-4229 | SQLCODE=-1476, SQLSTATE=40506, SQLERRMC=-289

While running the Solr Preprocess, it might fail to complete with the following error,

Table name:     TI_CATGPENREL_0
Fetch size:     500
Batch size:     500
0 Jan 30, 2019 7:00:37 PM com.ibm.commerce.foundation.dataimport.preprocess.DataImportPreProcessorMain processDataConfig(DataProcessingConfig, String)
INFO: Error for batch element #1: DB2 SQL Error: SQLCODE=-1476, SQLSTATE=40506, SQLERRMC=-289, DRIVER=4.19.49
0 Jan 30, 2019 7:00:37 PM com.ibm.commerce.foundation.dataimport.preprocess.DataImportPreProcessorMain processDataConfig(DataProcessingConfig, String)
INFO: SQL: SELECT DISTINCT CATGPENREL.CATENTRY_ID CATENTRY_ID, rtrim(cast(catgpenrel.catalog_id as char(23))) ||'_'|| rtrim(cast(CATGPENREL.CATGROUP_ID as char(23))) ||'_'|| rtrim(cast(cast(catgpenrel.sequence as decimal(20,5)) as char(26))) SEQUENCE     FROM CATGPENREL, TI_CATENTRY_0 CE, TI_CGPUB_0 CD      WHERE CATGPENREL.CATENTRY_ID=CE.CATENTRY_ID AND CATGPENREL.CATGROUP_ID = CD.CATGROUP_ID AND CD.PUBLISHED IN (?unpublished?, ?published?)     ORDER BY CATENTRY_ID
0 Jan 30, 2019 7:00:37 PM com.ibm.commerce.foundation.dataimport.preprocess.DataImportPreProcessorMain logExitCode
INFO:
The program exiting with exit code: 1.
Data import pre-processing was unsuccessful. An unrecoverable error has occurred.

0 Jan 30, 2019 7:00:37 PM com.ibm.commerce.foundation.dataimport.preprocess.DataImportPreProcessorMain logEndDateAndTime
INFO: Data import pre-processing ended:Wed Jan 30 19:00:37 GMT 2019
0 Jan 30, 2019 7:00:37 PM com.ibm.commerce.foundation.dataimport.preprocess.DataImportPreProcessorMain logEndDateAndTime
INFO: Data import pre-processing completed in 17.554 seconds.
0 Jan 30, 2019 7:00:37 PM com.ibm.commerce.foundation.dataimport.preprocess.DataImportPreProcessorMain handleExecutionException
SEVERE: Exception message: CWFDIH0002: An SQL exception was caught. The following error occurred: [jcc][t4][102][10040][4.19.49] Batch failure.  The batch was submitted, but at least one exception occurred on an individual member of the batch.
Use getNextException() to retrieve the exceptions for specific batched elements. ERRORCODE=-4229, SQLSTATE=null., stack trace: com.ibm.commerce.foundation.dataimport.exception.DataImportSystemException: CWFDIH0002: An SQL exception was caught. The following error occurred: [jcc][t4][102][10040][4.19.49] Batch failure.  The batch was submitted, but at least one exception occurred on an individual member of the batch.
Use getNextException() to retrieve the exceptions for specific batched elements. ERRORCODE=-4229, SQLSTATE=null.
        at com.ibm.commerce.foundation.dataimport.preprocess.DataImportPreProcessorMain.processDataConfig(DataImportPreProcessorMain.java:1311)
        at com.ibm.commerce.foundation.dataimport.preprocess.DataImportPreProcessorMain.execute(DataImportPreProcessorMain.java:1137)
        at com.ibm.commerce.foundation.dataimport.preprocess.DataImportPreProcessorMain.main(DataImportPreProcessorMain.java:449)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:60)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
        at java.lang.reflect.Method.invoke(Method.java:611)
        at com.ibm.ws.bootstrap.WSLauncher.main(WSLauncher.java:267)
Caused by: com.ibm.db2.jcc.am.BatchUpdateException: [jcc][t4][102][10040][4.19.49] Batch failure.  The batch was submitted, but at least one exception occurred on an individual member of the batch.
Use getNextException() to retrieve the exceptions for specific batched elements. ERRORCODE=-4229, SQLSTATE=null
        at com.ibm.db2.jcc.am.kd.a(Unknown Source)
        at com.ibm.db2.jcc.am.Agent.endBatchedReadChain(Unknown Source)
        at com.ibm.db2.jcc.am.gp.a(Unknown Source)
        at com.ibm.db2.jcc.am.gp.c(Unknown Source)
        at com.ibm.db2.jcc.am.gp.executeBatch(Unknown Source)
        at com.ibm.commerce.foundation.dataimport.preprocess.AbstractDataPreProcessor.populateTable(AbstractDataPreProcessor.java:369)
        at com.ibm.commerce.foundation.dataimport.preprocess.StaticAttributeDataPreProcessor.process(StaticAttributeDataPreProcessor.java:426)
        at com.ibm.commerce.foundation.dataimport.preprocess.DataImportPreProcessorMain.processDataConfig(DataImportPreProcessorMain.java:1278)
        ... 7 more

Issue:
DB2 transaction logs are full and is the reason of the issue

Resolution:
Run the following command to check the current transaction log,
db2 get db config for COLSUDB
LOGFILSIZ 2048


Increase the transaction log,
db2 UPDATE db cfg for COLSUDB using LOGFILSIZ 4096

If you are working with Server version of Solr, work with DBA to increase the transaction logs.

1 comment:

  1. Hi there, in a wcs 9 preprocess setup I got this one: Non-recoverable chain-breaking exception occurred during batch processing. The batch is terminated non-atomically. ERRORCODE=-4225. I increased the logs size and number but still won't work. I suspect something related to the commit freq, do you know if or where i can check this? thx in advnace

    ReplyDelete

My Writings........

HCL Commerce v9.1 | Local Store Vs Remote Store

  Local Store Remote Store Project Type Migrated (Lift & Shift) - Newly Imp...