Fraska Portal

Exploring the WebSphere Commerce world

WebSphere Commerce, FEP 6 installation and SOLR index error

Posted by on in Administration
  • Font size: Larger Smaller
  • Hits: 6106
  • 21 Comments
  • Subscribe to this entry
  • Print

The upgrade to FEP6 brings some odd side effect on SOLR search indexes (tested with DB2).

In particular, the pre-process task is impacted; in fact, once the system is upgraded to the last Feature Pack the pre-process throwed the following exception:

SEVERE: Exception message: CWFDIH0002: An SQL exception was caught. The following error occurred: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=LASTUPDATE, DRIVER=4.12.55., stack trace: com.ibm.commerce.foundation.dataimport.exception.DataImportSystemException: CWFDIH0002: An SQL exception was caught. The following error occurred: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=LASTUPDATE, DRIVER=4.12.55.
    at com.ibm.commerce.foundation.dataimport.util.DataImportDatabaseHelper.setIndexingActionJ2SE(DataImportDatabaseHelper.java:258)
    at com.ibm.commerce.foundation.dataimport.preprocess.DataImportPreProcessorMain.main(DataImportPreProcessorMain.java:280)
Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=LASTUPDATE, DRIVER=4.12.55
    at com.ibm.db2.jcc.am.hd.a(hd.java:676)
    at com.ibm.db2.jcc.am.hd.a(hd.java:60)
    at com.ibm.db2.jcc.am.hd.a(hd.java:127)
    at com.ibm.db2.jcc.am.mn.c(mn.java:2621)
    at com.ibm.db2.jcc.am.mn.d(mn.java:2609)
    at com.ibm.db2.jcc.am.mn.a(mn.java:2085)
    at com.ibm.db2.jcc.am.nn.a(nn.java:7054)
    at com.ibm.db2.jcc.t4.cb.g(cb.java:141)
    at com.ibm.db2.jcc.t4.cb.a(cb.java:41)
    at com.ibm.db2.jcc.t4.q.a(q.java:32)
    at com.ibm.db2.jcc.t4.rb.i(rb.java:135)
    at com.ibm.db2.jcc.am.mn.ib(mn.java:2055)
    at com.ibm.db2.jcc.am.nn.rc(nn.java:3219)
    at com.ibm.db2.jcc.am.nn.b(nn.java:4002)
    at com.ibm.db2.jcc.am.nn.fc(nn.java:769)
    at com.ibm.db2.jcc.am.nn.executeUpdate(nn.java:748)
    at com.ibm.commerce.foundation.dataimport.util.DataImportDatabaseHelper.setIndexingActionJ2SE(DataImportDatabaseHelper.java:251)
    ... 1 more

In order to fix this issue I made the following steps:

1) Log level

I set the log level to FINEST to get more detailed information about the issue.
In particular, I opened the file C:\<WC toolkit installation directory>\workspace\WC\xml\config\dataimport\logging.properties and set the following property:

# Default global logging level, INFO
.level=FINEST

 

2) Source of the issue

Than I executed again the index pre-process and analyzed the result log. This time the information was- as expected- more detailed and it was stating the following problem:

0 Apr 26, 2013 1:17:41 PM com.ibm.commerce.foundation.dataimport.util.DataImportDatabaseHelper getTemplateSQL(String)
FINER: RETURN INSERT INTO TI_DELTA_CATENTRY (MASTERCATALOG_ID, CATENTRY_ID, ACTION, LASTUPDATE) values (?, ?, ?, ?)
0 Apr 26, 2013 1:17:41 PM com.ibm.commerce.foundation.dataimport.util.DataImportDatabaseHelper getCurrentTimestamp(Connection)
FINER: ENTRY
0 Apr 26, 2013 1:17:41 PM com.ibm.commerce.foundation.dataimport.util.DataImportDatabaseHelper getCurrentTimestampSQL()
FINER: ENTRY
0 Apr 26, 2013 1:17:41 PM com.ibm.commerce.foundation.dataimport.util.DataImportDatabaseHelper useOracle()
FINE: dbtype: db2
0 Apr 26, 2013 1:17:41 PM com.ibm.commerce.foundation.dataimport.util.DataImportDatabaseHelper getCurrentTimestampSQL()
FINER: RETURN select current_timestamp from sysibm.sysdummy1
0 Apr 26, 2013 1:17:41 PM com.ibm.commerce.foundation.dataimport.util.DataImportDatabaseHelper getCurrentTimestamp(Connection)
FINER: ENTRY
0 Apr 26, 2013 1:17:41 PM com.ibm.commerce.foundation.dataimport.preprocess.DataImportPreProcessorMain logExitCode
FINER: ENTRY 1 null
0 Apr 26, 2013 1:17:41 PM com.ibm.commerce.foundation.dataimport.util.DataImportHelper getLocalizedMessage
FINER: ENTRY _INFO_DI_PREPROCESSING_EXIT_FAILURE_UNRECOVERABLE_ERROR [Ljava.lang.Object;@758d758d
0 Apr 26, 2013 1:17:41 PM com.ibm.commerce.foundation.dataimport.util.DataImportHelper getLocalizedMessage
FINER: RETURN Data import pre-processing was unsuccessful. An unrecoverable error has occurred.
0 Apr 26, 2013 1:17:41 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.

In some way it seemed the temporary index table, TI_DELTA_CATENTRY was missing the field LASTUPDATE. To confirm this statement, I merely executed the following query:

SELECT * FROM TI_DELTA_CATENTRY

realizing the field LASTUPDATE was missing.

 

3) Investigate FEPs

Starting a brief investigation of FEPs db schema changes I found FEP5 has introduced the filed LASTUPDATE for the tables TI_DELTA_CATENTRY and TI_DELTA_CATGROUP.

My environment was FEP4 and I made an upgrade directly to FEP6. The installation process suppose to make all the changes needed for the DB.

 

4) DB Schema

Once I realized the source of the problem I tried to update the DB schema as the index was expecting. So, I executed the enablement of foundation and store-enhancements manually but without any luck (the db schema was not impacted at all). Than I tried the utility updatedb, but it seemed to align the schema due to changes in fix packs (not feature packs). I actually did not find any official way to update the DB schema.

 

5) Possible workaround

Tired to look for any possible "clean" solution I made the DB schema update manually, executing the following queries:

ALTER TABLE TI_DELTA_CATENTRY ADD COLUMN LASTUPDATE TIMESTAMP;
ALTER TABLE TI_DELTA_CATGROUP ADD COLUMN LASTUPDATE TIMESTAMP;

Once the DB has been updated in the suggested way the pre-process and than the build worked as expected.

 

 

Rate this blog entry:
0

Comments

Leave your comment

Guest
Guest Thursday, 13 December 2018

Most Popular Post

WebSphere Commerce, the SOLR extension index
Administration
Rate this blog entry:
4
WebSphere Commerce, Data Load and SOLR Delta Index
Data Load
Rate this blog entry:
0
WebSphere Commerce, the curious life of a front-end catalog request
Core
Rate this blog entry:
5

Latest Blogs

WebSphere Commerce, CommandLevelAuthorizationCache
Cache
Rate this blog entry:
0
WebSphere Commerce v8, toolkit exception, ClassNotFound db2
Administration
Rate this blog entry:
1
WebSphere Commerce, ATP migration
Store
Rate this blog entry:
0
WebSphere Commerce, the curious life of a front-end catalog request
Core
Rate this blog entry:
5
WebSphere Commerce, Performance analysis of few European stores
Performance
Rate this blog entry:
0