JR51718: SEARCHES WITH BUSINESS DATA VARIABLES ARE RECOGNIZED AS UNKNOWN TYPE BY BUSINESS DATA ALIAS CACHE AND RETURN AN SQL ERROR

8.0.1.3-WS-BPM-IFJR51718
8.0.1.2-WS-BPM-IFJR51718
Version 8.5 Refresh Pack 6 for the IBM Business Process Manager products

APAR status

• Closed as program error.

Error description

• When you search for business data variables by using active
  accelerator tables and the business data alias cache returns a
  data type of UNKNOWN for the variables, the generated SQL
  statement is incorrect and you see the following SQL error:
  
  Caused by: [TeamworksException name='<JSScript>',
  message='PreparedStatementCallback; bad SQL grammar {
  SELECT *
  FROM
    (SELECT searchWithInstanceIdx.*,
      ROW_NUMBER() OVER ( order by instanceId, taskDueDate,
  taskPriorityRanking, taskId ) AS search_result_row_idx
    FROM
      (SELECT actualSearch.*,
        ROW_NUMBER() OVER ( PARTITION BY instanceid order by
  instanceId,
  taskDueDate, taskPriorityRanking, taskId ) AS
  instance_result_row_idx
      FROM
        (SELECT t.bpd_instance_id AS instanceId,
          istatus.name            AS instanceStatus,
          vp.CustomerAddress           AS PSCustomerAddress,
          t.due_date              AS taskDueDate,
          tpriority.ranking       AS taskPriorityRanking,
          t.task_id               AS taskId
        FROM lsw_task t
        INNER JOIN lsw_bpd_instance i
        ON t.bpd_instance_id = i.bpd_instance_id
        LEFT JOIN lsw_bpd_status_codes istatus
        ON i.execution_status = istatus.status_id
        LEFT JOIN lsw_priority tpriority
        ON t.priority_id = tpriority.priority_id
        INNER JOIN lsw_bpd_instance_vars_pivot vp
        ON vp.bpd_instance_id       =i.bpd_instance_id
        WHERE ( UPPER(istatus.name) = UPPER(?)
        AND ( (CAST(vp.DATE_VALUE AS  VARCHAR(128))
          || CAST(vp.DATE_VALUE AS    VARCHAR(128))
          || CAST(vp.DEC_VALUE AS     VARCHAR(128))
          || CAST(vp.INT_VALUE AS     VARCHAR(128))
          || CAST(vp.BOOLEAN_VALUE AS VARCHAR(128))
          || CAST(vp.STRING_VALUE AS  VARCHAR(512)) ) = UPPER(?)
  ) )
        ) actualSearch
      ) searchWithInstanceIdx
    WHERE searchWithInstanceIdx.instance_result_row_idx = 1
    ) searchWithRowIdx
  WHERE SEARCH_RESULT_ROW_IDX <= ?
  ORDER BY instanceId,
    taskDueDate,
    taskPriorityRanking,
    taskId
  INNER JOIN lsw_bpd_instance_vars_pivot vp                   <-
  accelerate table
        ON vp.bpd_instance_id       =i.bpd_instance_id
        WHERE ( UPPER(istatus.name) = UPPER(?)
        AND ( (CAST(vp.DATE_VALUE AS  VARCHAR(128))           <-
  column
  in LSW_BPD_INSTANCE_VARIABLES
          || CAST(vp.DATE_VALUE AS    VARCHAR(128))           <-
  column
  in LSW_BPD_INSTANCE_VARIABLES
          || CAST(vp.DEC_VALUE AS     VARCHAR(128))           <-
  column
  in LSW_BPD_INSTANCE_VARIABLES
          || CAST(vp.INT_VALUE AS     VARCHAR(128))           <-
  column
  in LSW_BPD_INSTANCE_VARIABLES
          || CAST(vp.BOOLEAN_VALUE AS VARCHAR(128))           <-
  column
  in LSW_BPD_INSTANCE_VARIABLES
          || CAST(vp.STRING_VALUE AS  VARCHAR(512)) ) = UPPER(?)
  ) )   <-
  column in LSW_BPD_INSTANCE_VARIABLES
        ) actualSearch
  
  This issue is related to JR51707, which covers UNKNOWN data type
  problem of the business data alias cache.
  

Local fix

Problem summary

• Sometimes the business data alias cache returns the data type
  UNKNOWN for a  business data variable of a search. JR51707
  handles that problem. However, the search does not handle the
  unexpected wrong data type UNKNOWN correctly and generates an
  invalid SQL statement.
  

Problem conclusion

• A fix is available that determines the data type definition
  from the accelerator tables when the variable data type is
  UNKNOWN to generate a valid SQL statement.
  
  On Fix Central (http://www.ibm.com/support/fixcentral), search
  for JR51718:
  
  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 JR51718, 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

• Not applicable
  

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