IBM Support

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

Subscribe

You can track all active APARs for this component.

 

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 number

    JR51718

  • Reported component name

    BPM STANDARD

  • Reported component ID

    5725C9500

  • Reported release

    801

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2014-11-04

  • Closed date

    2015-02-05

  • Last modified date

    2015-02-05

  • 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

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSFTDH","label":"IBM Business Process Manager Standard"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.0.1","Edition":"","Line of Business":{"code":"LOB36","label":"IBM Automation"}}]

Document Information

Modified date:
05 February 2015