IBM Support

Improving the performance of complex BPC API queries on DB2

Question & Answer


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?


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.


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.

[{"Product":{"code":"SSQH9M","label":"WebSphere Process Server"},"Business Unit":{"code":"BU004","label":"Hybrid Cloud"},"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":""}]

Product Alias/Synonym

WPS WebSphere Process Server;BPC Business Process Choreographer;HTM Human Task Manager;BFM Business Flow Manager

Document Information

Modified date:
15 June 2018