JR60303: WebSphere Commerce V9 DB2 hard coded value causing an issue when switching to Oracle database.

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