IBM Support

JR50495: PERFORMANCE AFFECTED BECAUSE CANNOT DEFINE A QUERY ORDER FOR DOCUMENT PROPERTIES

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • IBM Business Process Manager (BPM) provides the Document
    Attachment feature that is handled by a servlet. When you query
    document attachments from a servlet with multiple applications,
    there is no way to define a search order on the document
    properties that you specified. However, the implementation of
    the document query uses a prepared statement to retrieve the
    document information from the database. The order in which the
    property attributes are passed can affect performance.
    

Local fix

Problem summary

  • If the position of the property attribute is different from the
    precompiled execution plan and the new executed prepared
    statement, a large result set of several million rows might be
    processed as part of a nested loop join on the database server.
    Run times of several minutes can be expected if the matching
    filter attributes are  several thousand entries.
    

Problem conclusion

  • A fix is available for IBM BPM V8.0.1.2. . With this fix
    applied, you can define a fixed sort order in the 100Custom.xml
    configuration file by using the document-property-query-order
    configuration parameter. Therefore, you can place property
    filters that have a large result set in the same position in the
    where clause of the SQL statement.
    
    To define a global document property query order, add the
    following property to 100Custom.xml:
    
     <server merge="mergeChildren">
        <document-property-query-order>
           [comma separated order list of properties]
        </document-property-query-order>
     </server>
    
    For example, here is how you would define the following order of
    the quantity, color, and size document properties:
    
     <server merge="mergeChildren">
        <document-property-query-order>
           quantity,color,size
        </document-property-query-order>
     </server>
    
    If a property name contains a comma, the comma in the property
    name must be encoded, for example, property name ?color,red?
    becomes ?color&#044;red?. Property names that are not listed in
    the query order but are passed in as search criteria are
    regarded last when creating the database query.
    
    You must restart the server after you change the 100Custom.xml
    file so that the changes take effect.
    
    The first time the prepared statement to query the document
    information runs, an execution plan is created that is used in
    all subsequent requests of the same SQL statement structure and
    they include the same number of filter properties. Performance
    might be impacted if more than one application has the same
    number of property filters and the order of the property filter
    attributes is changed.
    
    This is an example of the affected prepared statement for
    Microsoft SQL Server:
    
    select distinct d.doc_id, d.bpd_instance_id, d.parent_doc_id,
                    d.doc_name, d.filename, d.filetype, d.author_id,
                    d.authored_dtg, d.version, d.content_type,
                    d.hide_in_portal
    from lsw_bpd_instance_documents d , LSW_BPD_INSTANCE_DOC_PROPS
                    a1, LSW_BPD_INSTANCE_DOC_PROPS a2
    where ((a1.name = @P0 and a1.value = @P1 and a1.doc_id =
            d.parent_doc_id) and
           (a2.name = @P2 and a2.value = @P3 and a2.doc_id =
            d.parent_doc_id)) and
           d.doc_id in
             (select max(doc_id)from lsw_bpd_instance_documents
               group by parent_doc_id)
    
    A number of similar SQL statements, especially with an
    additional filter on the process instance ID, are not heavily
    impacted because the filter on the process instance ID is very
    selective.
    
    To get the fix,  search for JR40495on Fix Central
    (http://www.ibm.com/support/fixcentral):
    
    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 JR40495 , 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

    JR50495

  • Reported component name

    BPM ADVANCED

  • Reported component ID

    5725C9400

  • Reported release

    801

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2014-06-12

  • Closed date

    2014-08-12

  • Last modified date

    2014-08-12

  • 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:
12 August 2014