JR47789: MANY SHARED WORKITEMS IN THE SYSTEM LEAD TO ORA-01795 ERROR

Download Version 8.0.1 Fix Pack 2 for the IBM Business Process Manager products
Version 8.5.0 Fix Pack 1 for the IBM Business Process Manager products
Version 8.5 Refresh Pack 5 for the IBM Business Process Manager products

APAR status

• Closed as program error.

Error description

• Many records in BPEDB table SWI_T can lead to ORA-01795
  (ORA-01795: maximum number of expressions in a list is 1000)
  
  Query:
  
  select S.PKID  BPEDB.SWIT S  WHERE S.HASH_CODE=1: AND S.WSID NOT
  IN
  (HEXTORAW(:2),HEXTORAW(:3),HEXTORAW(:4),.......HEXTORAW(:4XX)
  
  Stacktrace
  
  [9/11/13 15:09:42:490 CST] 00000049 LocalExceptio E   CNTR0020E:
  EJB threw an unexpected (non-declared) exception during
  invocation of method "claim" on bean
  "BeanId(TaskContainer_BPM.AppTarget#taskejb.jar#GenericHumanTask
  ManagerEJB, null)". Exception data:
  java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of
  expressions in a list is 1000
   at
  oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
   at
  oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
   at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
   at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
   at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
   at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
   at
  oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedState
  ment.java:205)
   at
  oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPr
  eparedStatement.java:861)
   at
  oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleSt
  atement.java:1145)
   at
  oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleSt
  atement.java:1267)
   at
  oracle.jdbc.driver.OraclePreparedStatement.executeInternal(Oracl
  ePreparedStatement.java:3449)
   at
  oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePr
  eparedStatement.java:3493)
   at
  oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(O
  raclePreparedStatementWrapper.java:1491)
   at
  com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteQuer
  y(WSJdbcPreparedStatement.java:1110)
   at
  com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(W
  SJdbcPreparedStatement.java:712)
   at
  com.ibm.bpe.database.TomPreparedStatement$3.run(TomPreparedState
  ment.java:185)
   at
  com.ibm.bpe.database.TomPreparedStatement$3.run(TomPreparedState
  ment.java:1)
   at
  java.security.AccessController.doPrivileged(AccessController.jav
  a:280)
   at
  com.ibm.bpe.database.TomPreparedStatement.executeQuery(TomPrepar
  edStatement.java:180)
   at com.ibm.bpe.database.Tom.getSharedWorkItem(Tom.java:10647)
   at
  com.ibm.bpe.database.SharedWorkItemCache.get(SharedWorkItemCache
  .java:476)
   at
  com.ibm.bpe.database.SharedWorkItemManager$SharedWorkItemPattern
  Cache.get(SharedWorkItemManager.java:2173)
   at
  com.ibm.bpe.database.SharedWorkItemManager.assignExistingSharedW
  orkItemID(SharedWorkItemManager.java:866)
   at
  com.ibm.bpe.database.SharedWorkItemManager.assignSharedWorkItemI
  D(SharedWorkItemManager.java:774)
   at
  com.ibm.bpe.database.SharedWorkItemManager.transform(SharedWorkI
  temManager.java:731)
   at
  com.ibm.bpe.database.SharedWorkItemManager.transform(SharedWorkI
  temManager.java:699)
   at
  com.ibm.bpe.database.SharedWorkItemManager.beforeCompletion(Shar
  edWorkItemManager.java:204)
   at
  com.ibm.bpe.database.TomExtended.beforeCompletion(TomExtended.ja
  va:1109)
   at
  com.ibm.bpe.database.TomExtended.flush(TomExtended.java:1078)
   at
  com.ibm.task.framework.TaskManagerServiceImpl.completeTransactio
  n(TaskManagerServiceImpl.java:40233)
   at
  com.ibm.task.framework.TaskManagerServiceImpl.claim(TaskManagerS
  erviceImpl.java:1317)
   at
  com.ibm.task.framework.TaskManagerServiceImpl.claim(TaskManagerS
  erviceImpl.java:1255)
  

Local fix

Problem summary

• OutOfMemory or ORA-01795 may occur in systems with authorization
  on very large number of users and groups.
  
  
  PROBLEM DETAILED DESCRIPTION:
  OutOfMemory or ORA-01795 may occur in systems with authorization
  on very large amount of users and groups.  This may be caused
  by the generated variation of the authorization results for a
  database parameter list.
  

Problem conclusion

• The problem is fixed by reducing the number of parameter list
  variations with parameter padding to a subset of combinations.
  
  FIX AVAILABILITY:
  iFix for 8.0.1.1 is available on Fix Central, search for APAR
  JR47789 at http://www.ibm.com/support/fixcentral/
  Fix is also targetted for inclusion in next fixpack for BPM
  8.0.1, BPM 8.5.0
  
  When obtaining any of the above fixes, be sure to download the
  accompanying readme, for itself, and any prerequisite fixes, and
  review them thorougly.
  

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

  BPM ADVANCED

• Fixed component ID

  5725C9400

Applicable component levels

• R801 PSY

     UP