APAR status
Closed as program error.
Error description
The following database errors are after switching to Oracle: [31/10/18 12:02:00:160 EDT] 000000ad CommerceSrvr E com.ibm.commerce.dynacache.commands.DefaultDynaCacheInvalidation Helper cleanCacheivl(int aObsoleteDays) CMN0409E: The following error occurred during processing: "java.sql.SQLSyntaxErrorException: ORA-00936: missing expression ". java.sql.SQLSyntaxErrorException: ORA-00936: missing expression at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494 ) at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446 ) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedState ment.java:226) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedState ment.java:59) at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPrepar edStatement.java:910) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleSt atement.java:1119) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(Oracl ePreparedStatement.java:3780) at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPrepa redStatement.java:1343) at oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(Or aclePreparedStatement.java:3865) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OracleP reparedStatement.java:3845) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate( OraclePreparedStatementWrapper.java:1061) at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeUpdate( WSJdbcPreparedStatement.java:844) at com.ibm.commerce.dynacache.commands.DefaultDynaCacheInvalidation Helper.cleanCacheivl(DefaultDynaCacheInvalidationHelper.java:180 ) at com.ibm.commerce.dynacache.commands.LocalJVMDynaCacheInvalidatio nHelper.myCleanFromCacheivlTable(LocalJVMDynaCacheInvalidationHe lper.java:368) at com.ibm.commerce.dynacache.commands.LocalJVMDynaCacheInvalidatio nHelper.myInvokeClean(LocalJVMDynaCacheInvalidationHelper.java:3 98) at com.ibm.commerce.dynacache.commands.LocalJVMDynaCacheInvalidatio nHelper.myInvokeDynaCacheInvalidation(LocalJVMDynaCacheInvalidat ionHelper.java:283) at com.ibm.commerce.dynacache.commands.LocalJVMDynaCacheInvalidatio nHelper.invokeDynaCacheInvalidation(LocalJVMDynaCacheInvalidatio nHelper.java:229) at com.ibm.commerce.datatype.TransactionCache.transactionNotify(Tra nsactionCache.java:216) at com.ibm.commerce.server.TransactionManager.begin(TransactionMana ger.java:123) at com.ibm.commerce.server.TransactionManager.begin(TransactionMana ger.java:67) at com.ibm.commerce.scheduler.Scheduler.recoverJob(Scheduler.java:2 480) at com.ibm.commerce.scheduler.Scheduler.initialize(Scheduler.java:2 177) at com.ibm.commerce.scheduler.Scheduler.process(Scheduler.java:2705 ) at com.ibm.commerce.scheduler.SchedulerSecurityPriviledgedAction.ru n(SchedulerSecurityPriviledgedAction.java:59) at com.ibm.commerce.scheduler.Scheduler.run(Scheduler.java:2646) at com.ibm.commerce.threadmanagement.internal.BaseWork.run(BaseWork .java:139) at com.ibm.ws.asynchbeans.J2EEContext.run(J2EEContext.java:1046) at com.ibm.ws.asynchbeans.WorkWithExecutionContextImpl.go(WorkWithE xecutionContextImpl.java:199) at com.ibm.ws.asynchbeans.CJWorkItemImpl.run(CJWorkItemImpl.java:23 6) at java.lang.Thread.run(Thread.java:811) Caused by: Error : 936, Position : 48, Sql = delete from (select 1 from cacheivl where (days(CURRENT TIMESTAMP) - days(inserttime)) >= :1 and (TEMPLATE is NULL or TEMPLATE<>'restart:') fetch first 50000 rows only with ur), OriginalSql = delete from (select 1 from cacheivl where (days(CURRENT TIMESTAMP) - days(inserttime)) >= ? and (TEMPLATE is NULL or TEMPLATE<>'restart:') fetch first 50000 rows only with ur), Error Msg = ORA-00936: missing expression at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498 ) ... 33 more The following code accounts for this: synchronized (EXECUTION_MONITOR) { try { PreparedStatement stmt = connection.prepareStatement(CLEAN_SQL); ..... private static final String CLEAN_SQL = "delete from (" +"select 1 from cacheivl where (days(CURRENT TIMESTAMP) - days(inserttime)) >= ? and (" +"TEMPLATE is NULL or TEMPLATE<>'restart:') fetch first 50000 rows only with ur)"; This should be corrected to account for Oracle.
Local fix
Problem summary
USERS AFFECTED: WebSphere Commerce V9 PROBLEM ABSTRACT: WebSphere Commerce V9 DB2 hard coded value causing an issue when switching to Oracle database. BUSINESS IMPACT: Customer can not run cache invalidation correctly on an Oracle database. RECOMMENDATION:
Problem conclusion
Fix the code issue for Oracle support.
Temporary fix
Comments
APAR Information
APAR number
JR60303
Reported component name
WC BUS EDITION
Reported component ID
5724I3800
Reported release
900
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2018-11-15
Closed date
2019-05-24
Last modified date
2019-05-24
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Fixed component name
WC BUS EDITION
Fixed component ID
5724I3800
Applicable component levels
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSYSYL","label":"WebSphere Commerce Enterprise"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.0","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
24 May 2019