JR53315: di-calculateprice or ContractPriceCalculate cause sql error when trying to truncate the content of a table

APAR status

• Closed as program error.

Error description

• When di-calculateprice or ContractPriceCalculate is executing
  the build of the price index for preparation
  it truncates the pre-processing tables. This error has been seen
   only with DB2 V9.5, because DB2 V9.5 does not
  support the truncate table sql statement.
  
  The error can look like the following:
  [4/29/15 15:42:00:346 AST] 00000028 ContractPrice E   Clean
  price record returns an SQL errorcom.ibm.db2.jcc.a.io: DB2 SQL
  Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=TABLE;TRUNCATE
  ;JOIN <joined_table>, DRIVER=4.3.111
  [4/29/15 15:42:00:348 AST] 00000028 SystemErr     R
  com.ibm.db2.jcc.a.io: DB2 SQL Error: SQLCODE=-104,
  SQLSTATE=42601, SQLERRMC=TABLE;TRUNCATE ;JOIN <joined_table>,
  DRIVER=4.3.111
  [4/29/15 15:42:00:349 AST] 00000028 SystemErr     R    at
  com.ibm.db2.jcc.a.dd.a(dd.java:676)
  [4/29/15 15:42:00:349 AST] 00000028 SystemErr     R    at
  com.ibm.db2.jcc.a.dd.a(dd.java:60)
  [4/29/15 15:42:00:349 AST] 00000028 SystemErr     R    at
  com.ibm.db2.jcc.a.dd.a(dd.java:127)
  [4/29/15 15:42:00:349 AST] 00000028 SystemErr     R    at
  com.ibm.db2.jcc.a.jm.c(jm.java:2493)
  [4/29/15 15:42:00:349 AST] 00000028 SystemErr     R    at
  com.ibm.db2.jcc.a.jm.d(jm.java:2470)
  [4/29/15 15:42:00:349 AST] 00000028 SystemErr     R    at
  com.ibm.db2.jcc.a.jm.a(jm.java:1971)
  [4/29/15 15:42:00:349 AST] 00000028 SystemErr     R    at
  com.ibm.db2.jcc.t4.db.g(db.java:138)
  [4/29/15 15:42:00:350 AST] 00000028 SystemErr     R    at
  com.ibm.db2.jcc.t4.db.a(db.java:38)
  [4/29/15 15:42:00:350 AST] 00000028 SystemErr     R    at
  com.ibm.db2.jcc.t4.t.a(t.java:32)
  [4/29/15 15:42:00:350 AST] 00000028 SystemErr     R    at
  com.ibm.db2.jcc.t4.sb.h(sb.java:141)
  [4/29/15 15:42:00:350 AST] 00000028 SystemErr     R    at
  com.ibm.db2.jcc.a.jm.db(jm.java:1942)
  [4/29/15 15:42:00:350 AST] 00000028 SystemErr     R    at
  com.ibm.db2.jcc.a.km.gc(km.java:2566)
  [4/29/15 15:42:00:350 AST] 00000028 SystemErr     R    at
  com.ibm.db2.jcc.a.km.e(km.java:3267)
  [4/29/15 15:42:00:350 AST] 00000028 SystemErr     R    at
  com.ibm.db2.jcc.a.km.Ub(km.java:640)
  [4/29/15 15:42:00:351 AST] 00000028 SystemErr     R    at
  com.ibm.db2.jcc.a.km.executeUpdate(km.java:619)
  [4/29/15 15:42:00:351 AST] 00000028 SystemErr     R    at
  com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteUpda
  te(WSJdbcPreparedStatement.java:1156)
  [4/29/15 15:42:00:351 AST] 00000028 SystemErr     R    at
  com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeUpdate(
  WSJdbcPreparedStatement.java:797)
  [4/29/15 15:42:00:351 AST] 00000028 SystemErr     R    at
  com.ibm.commerce.foundation.server.services.dataaccess.JDBCQuery
  Service.executeSQLUpdate(JDBCQueryService.java:627)
  [4/29/15 15:42:00:351 AST] 00000028 SystemErr     R    at
  com.ibm.commerce.foundation.dataimport.priceindex.commands.Contr
  actPriceCalculateCmdImpl.deletePriceRecordForOneShard(ContractPr
  iceCalculateCmdImpl.java:2823)
  [4/29/15 15:42:00:351 AST] 00000028 SystemErr     R    at
  com.ibm.commerce.foundation.dataimport.priceindex.commands.Contr
  actPriceCalculateCmdImpl.deletePriceRecordIfNotEmpty(ContractPri
  ceCalculateCmdImpl.java:2765)
  [4/29/15 15:42:00:351 AST] 00000028 SystemErr     R    at
  com.ibm.commerce.foundation.dataimport.priceindex.commands.Contr
  actPriceCalculateCmdImpl.performExecute(ContractPriceCalculateCm
  dImpl.java:573)
  [4/29/15 15:42:00:352 AST] 00000028 SystemErr     R    at
  com.ibm.commerce.command.ECCommandTarget.executeCommand(ECComman
  dTarget.java:157)
  ...
  

Local fix

Problem summary

• USERS AFFECTED:
  Customer with DB2 9.5 will have issue
  
  PROBLEM ABSTRACT:
  di-calculateprice or ContractPriceCalculate cause sql error when
   trying to truncate the content of a table
  
  BUSINESS IMPACT:
  Customer with DB2 9.5 will have issue. The new solution is to
  try to use truncate and if exception is thrown, to catch the
  exception and to use drop function.
  
  RECOMMENDATION:
  

Problem conclusion

• The solution is to try to use truncate and if exception is
  thrown, to catch the exception and to use drop function.
  

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

• R700 PSY

     UP