About cookies on this site Our websites require some cookies to function properly (required). In addition, other cookies may be used with your consent to analyze site usage, improve the user experience and for advertising. For more information, please review your options. By visiting our website, you agree to our processing of information as described in IBM’sprivacy statement. To provide a smooth navigation, your cookie preferences will be shared across the IBM web domains listed here.
Question & Answer
Question
How can the performance of complex WebSphere Business Process Choreographer (BPC) API queries be improved if DB2 Version 8 or 9 is used as BPC database?
Cause
BPC uses prepared statements with parameter markers. By default, DB2 determines the access plan for prepared statements once during the preparation phase, without taking into account distribution statistics for parameter markers. The access path determined by DB2 based on the prepared statements with parameter markers may not be optimal. Turning on re-optimization (once) at execution time can greatly improve query performance.
In a lab environment, query response times of the MyToDo's page of BPC Explorer have been reduced from 30 seconds to 300 ms.
Note: In some scenarios, process instance navigation throughput might be negatively impacted if this technote is implemented. Dependent on the impact, it might be a better choice to reverse the changes.
Answer
Turn on reoptimization (once) for prepared statements. Follow the steps described below and restart the WebSphere Process Server afterwards.
The following steps refer to BPEDB as the database for Business Process Choreographer (BPC).
Step 1: Collect statistics. Basically, execute "runstats on table <schema>.<table> with distribution and detailed indexes all" on all tables contained in the BPEDB.
Before executing the steps below: The database must be loaded with a representative amount of processes and/or tasks - execution of 'runstats' on an empty DB2 database can significantly decrease the performance. Note that tuning might be necessary in order to get the system loaded.
a) Connect to the BPEDB database:
db2 connect to BPEDB
b) Create the SQL script for executing runstats: in a DB2 command-line do:
db2 -x "select ' runstats on table ' concat rtrim(tabschema) concat '.' concat tabname concat ' with distribution and detailed indexes all ' from syscat.tables where type='T' AND tabname not in ('SAVED_ENGINE_MESSAGE_B_T') AND TBSPACEID IN (select TBSPACEID from sysibm.systablespaces where TBSPACE IN ('INSTANCE', 'WORKITEM', 'STAFFQUERY','AUDITLOG', 'SCHEDTS', 'BPETS8K')) " >> runStatsScript.sql
Note: the select clause "IN ('INSTANCE', ..., 'BPETS8K')" contains the default table spaces that are created and used when creating the BPEDB database. In case that in your environment the BPC tables are located in different table spaces, change this sub select accordingly.
c) Execute the generated SQL script (runStatsScript.sql):
db2 -f runStatsScript.sql
Step 2: Create package "NULLIDR1" in database BPEDB (see [NULLIDR1] below for details):
a) Connect to the BPEDB database:
db2 connect to BPEDB
b) Create package NULLIDR1 (note that you must change into the 'bnd' directory of your DB2 installation):
db2 bind db2clipk.bnd collection NULLIDR1
Step 3: Customize the BPEDB Datasource on WebSphere Process Server (see [currentPackageSet] below for details):
a) Login to the admin console of your WebSphere Process Server
b) Go to the custom properties page of the BPEDB Datasource and set the value of property currentPackageSet to NULLIDR1.
Step 4: Restart your WebSphere Process Server.
Note: Typically, the performance of queries can be improved by creating indexes which are suggested by the DB2 Design Advisor. It is recommended to run the DB2 Design Advisor on the SQL statements that are executed for BPC queries. You can use either DB2 statement snapshots or the BPE trace to find out the exact SQL statements.
Related Information
[{"Product":{"code":"SSQH9M","label":"WebSphere Process Server"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Business Process Choreographer","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"},{"code":"PF035","label":"z\/OS"}],"Version":"7.0;6.2;6.1","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}}]
Product Synonym
WPS WebSphere Process Server
BPC Business Process Choreographer
HTM Human Task Manager
BFM Business Flow Manager
Was this topic helpful?
Document Information
More support for:
WebSphere Process Server
Software version:
7.0, 6.2, 6.1
Operating system(s):
AIX, HP-UX, Linux, Solaris, Windows, z/OS
Document number:
97499
Modified date:
15 June 2018
UID
swg21299450
Manage My Notification Subscriptions