JR44467: BADSQLGRAMMAREXCEPTION WHEN SEARCHING INSTANCE BY PERSON IN WEB PROCESS INSPECTOR IF THE QUERY IS TOO LONG

Version 8.0.1 Fix Pack 1 for the IBM Business Process Manager products
Version 7.5.1 Fix Pack 2 for the IBM Business Process Manager products

APAR status

• Closed as program error.

Error description

• BadSqlGrammarException when searching instance by person in Web
  Process
  Inspector if the query is too long.
  

Local fix

Problem summary

• Problem Description:
  The Web Process Inspector supports viewing tasks and processes
  assigned to a specific user.  When the user belongs to a large
  number of Participant Groups, the generated SQL query may be
  nested too deeply for MSSQL.
  
  
  Problem Summary:
  The incoming where clause is parsed and a tree generated.  When
  this tree has too many branches SQL code is generated which is
  too complex for MS SQL Server, so that the following error
  occurs:
  
  com.microsoft.sqlserver.jdbc.SQLServerException: Some part of
  your SQL statement is nested too deeply. Rewrite the query or
  break it up into smaller queries.
   at
  org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.
  translate(SQLStateSQLExceptionTranslator.java:111)
   at
  org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTransla
  tor.translate(SQLErrorCodeSQLExceptionTranslator.java:322)
   at
  org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.
  java:407)
   at
  org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.ja
  va:458)
   at
  org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.ja
  va:466)
   at
  org.springframework.jdbc.core.simple.SimpleJdbcTemplate.query(Si
  mpleJdbcTemplate.java:187)
   at
  com.lombardisoftware.server.core.pse.impl.ProcessSearchEngineImp
  l.executeCountQuery(ProcessSearchEngineImpl.java:346)
   at
  com.lombardisoftware.server.core.pse.impl.ProcessSearchEngineImp
  l.executeCountQuery(ProcessSearchEngineImpl.java:265)
   at
  com.lombardisoftware.server.core.pse.impl.ProcessSearchEngineImp
  l.executeCountQuery(ProcessSearchEngineImpl.java:121)
   at
  com.lombardisoftware.server.ejb.api.ProcessSearchAPICore.execute
  CountQueryInternal(ProcessSearchAPICore.java:161)
   at
  com.lombardisoftware.server.ejb.api.ProcessSearchAPICore.access$
  300(ProcessSearchAPICore.java:49)
   at
  com.lombardisoftware.server.ejb.api.ProcessSearchAPICore$2.doInT
  ransaction(ProcessSearchAPICore.java:140)
   at
  com.lombardisoftware.server.ejb.api.ProcessSearchAPICore$2.doInT
  ransaction(ProcessSearchAPICore.java)
   ... 130 more
  Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Some
  part of your SQL statement is nested too deeply. Rewrite the
  query or break it up into smaller queries.
   at
  com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabase
  Error(SQLServerException.java:196)
   at
  com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQ
  LServerStatement.java:1454)
   at
  com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteCursore
  d(SQLServerStatement.java:1824)
   at
  com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStateme
  nt(SQLServerStatement.java:760)
   at
  com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doEx
  ecute(SQLServerStatement.java:685)
   at
  com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:40
  26)
   at
  com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(
  SQLServerConnection.java:1416)
   at
  com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(S
  QLServerStatement.java:185)
   at
  com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement
  (SQLServerStatement.java:160)
   at
  com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQL
  ServerStatement.java:620)
   at
  com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.pmiExecuteQuery(WSJdbc
  Statement.java:1621)
   at
  com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.executeQuery(WSJdbcSta
  tement.java:925)
   at
  org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallba
  ck.doInStatement(JdbcTemplate.java:443)
   at
  org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.
  java:396)
   ... 140 more
  
  Problem Conclusion:
  The SQL query has been simplified, reducing the nesting depth.
  

Problem conclusion

• Problem fixed
  iFix available on top of V751 FP1
  

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 STANDARD

• Fixed component ID

  5725C9500

Applicable component levels

• R751 PSY

     UP