Fixes are available
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,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"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.0.1","Line of Business":{"code":"LOB45","label":"Automation"}}]
Document Information
Modified date:
14 October 2021