JR49859: SAVED SEARCH QUERY FAILS BECAUSE OF INCORRECTLY GENERATED SQL INCASES WHEN PROCESS SEARCH OPTIMIZATION IS ENABLED

8.5.0.2-WS-BPM-IFJR49859
Version 8.5 Refresh Pack 5 for the IBM Business Process Manager products
Download Version 8.0.1 Fix Pack 3 for the IBM Business Process Manager products

APAR status

• Closed as program error.

Error description

• When you run a saved search to retrieve task information and
  Process Search Optimization is enabled, the query fails with an
  SQL error because the SQL query was generated incorrectly.
  

Local fix

• Yes. Add at least one of the following columns to the saved
  search and then rerun it:
  
  Process Instance columns:
  - Due Date
  - Name
  - Status
  - ProcessApp
  - Snapshot
  
  Process columns:
  - Name
  

Problem summary

• When you run a saved search to retrieve task information and
  Process Search Optimization is enabled, the query fails with an
  SQL error because the SQL query was generated incorrectly.
  
  PROBLEM DETAILED DESCRIPTION:
  If you define a saved search in the Process Admin Console to run
  on a server where Process Search Optimization is enabled, the
  query fails with the following error.
  
  In Process Designer or the Process Admin Console, the error
  looks similar to the following message where <ScriptName> and
  <Query> are placeholders for the actual values:
  
  Runtime error in script ( <ScriptName> ).Internal Script
  error: [TeamworksException name='<JSScript>',
  message='PreparedStatementCallback; bad SQL grammar [ <Query>];
  nested exception is com.ibm.db2.jcc.am.SqlSyntaxErrorException:
  DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703,
  SQLERRMC=I.BPD_INSTANCE_ID, DRIVER=4.11.69', line=91, pos=0
  nested=<none>]
  
  In the log file, the error looks similar to the following
  message:
  
  com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error:
  SQLCODE=-206, SQLSTATE=42703, SQLERRMC=I.BPD_INSTANCE_ID,
  DRIVER=4.11.69', line=24, pos=0 nested=<none>]
   at com.lombardisoftware.core.script.js.
    JavaScriptRunner$MyErrorReporter.runtimeError
    (JavaScriptRunner.java:542)
   at org.mozilla.javascript.Context.reportRuntimeError
    (Context.java:938)
   at org.mozilla.javascript.Context.reportRuntimeError
    (Context.java:994)
   at com.lombardisoftware.core.script.js.TWSearchScriptable.
    executeSearchInternal(TWSearchScriptable.java:381)
   at com.lombardisoftware.core.script.js.TWSearchScriptable.
    jsFunction_execute(TWSearchScriptable.java:180)
  
  Note that the details of the error are different depending on
  the database type.
  
  Whether this error occurs depends on the definition of the saved
  search. The query fails if the saved search is organized by
  tasks and not one of the following columns, which are marked
  with asterisks (*), is included in the query:
  
  Process Instance columns:
  - Due Date *
  - ID
  - Name *
  - Status *
  - ProcessApp *
  - Snapshot *
  
  Process columns:
  - Name *
  
  This issue occurs only when the SQL query is generated
  incorrectly because the Process Search Optimization is enabled:
  a join is added by using a prefix that is not defined in the
  query.
  

Problem conclusion

• A fix is available that corrects the generated SQL query.
  
  On Fix Central (http://www.ibm.com/support/fixcentral), search
  for JR49859:
  
  1. Select IBM Business Process Manager with your edition from
   the product selector, the installed version to the fix pack
   level, and your platform, and then click Continue.
  
  2. Select APAR or SPR, enter JR49859, and click Continue.
  
  When you download fix packages, ensure that you also download
  the readme file for each fix. Review each readme file for
  additional installation instructions and information about the
  fix.
  

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

• R801 PSY

     UP