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

Download Version 8.0.1 Fix Pack 3 for the IBM Business Process Manager products
Version 8.5 Refresh Pack 6 for the IBM Business Process Manager products

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 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