Troubleshooting the Business Process Choreographer database and data source
Use this task to solve problems with the Business Process Choreographer database and data source.
About this task
Procedure
- If the Business Process Choreographer database is getting bigger
and slower over time, consider performing following:
- To get a better idea of how many instances and activities are
in the database and to verify their numbers you can use variations
on the following example queries:
- If your typical process models run an average of x activities,
then the number of activity instances should not be more than x times
larger than the number of process instances. Any large deviation from
this ratio might indicate a problem and be worth investigating further.
- To display the number of process instances that are in the database,
we count the number of rows in the process instance table using the
following query:
By specifying with UR, locking is avoided, which could affect other users of the database.select count(*) from process_instance_b_t with ur
- To display the number of activity instances that are in the database,
we count the number of rows in the activity instance table using the
following query:
select count(*) from activity_instance_b_t with ur
Note: Only activities that have the "business relevance" flag set are persisted in the activity instance table
- To display the number of process instances that are in the database,
we count the number of rows in the process instance table using the
following query:
- To see which process instances have the most activities, use the
following query:
where PI.PIID is the process instance ID from the process_instance_b_t table and PT.NAME is the name of the process template. This query might result in output that is similar to the following example:SELECT PI.PIID, PT.NAME, PI.STATE, COUNT(AI.AIID) AS NUMBER_OF_ACTIVITIES FROM ACTIVITY_INSTANCE_B_T AS AI, PROCESS_INSTANCE_B_T AS PI, PROCESS_TEMPLATE_B_T AS PT WHERE PI.PTID = PT.PTID AND AI.PIID = PI.PIID GROUP BY PI.PIID, PT.NAME, PI.STATE ORDER BY NUMBER_OF_ACTIVITIES DESC FETCH FIRST 20 ROWS ONLY WITH UR
In this example, the claimWork process template has the most activities in the database. For process instances, the state, 6, indicates that they are in the state TERMINATED. If the database is filling up with such instances, you should consider setting the property for automatic deletion in the model, configuring the cleanup service and cleanup jobs to periodically delete eligible instances, or running a script to delete completed instances. These and other techniques for reducing the size of the database are described in Cleanup procedures for Business Process Choreographer.PIID NAME STATE NUMBER_OF_ACTIVITIES ----------------------------------- ---------- ------ -------------------- x'9003011CE5DED75B3EFDEB538C02DAE4' claimWork 6 147047 x'9003011E841DE9AF3EFDEB53045C4103' claimWork 6 96609 x'9003011E841DDEF13EFDEB53045C3DD9' claimWork 6 96462 ...
- If you are interested in process instances that are in a particular
state, use the following query:
where processState is one of the following integer values representing the state of the process instance:select count(*) from process_instance_b_t where state = processState with ur
0 = DELETED
1 = READY
2 = RUNNING
3 = FINISHED
4 = COMPENSATING
5 = FAILED
6 = TERMINATED
7 = COMPENSATED
8 = TERMINATING
9 = FAILING
10 = INDOUBT
11 = SUSPENDED
12 = COMPENSATION_FAILED - If you are interested in activity instances that are in a particular
state, use the following query:
where activityState is one of the following integer values representing the state of the activity instance:select count(*) from activity_instance_b_t where state = activityState with ur
1 = INACTIVE
2 = READY
3 = RUNNING
4 = SKIPPED
5 = FINISHED
6 = FAILED
7 = TERMINATED
8 = CLAIMED
9 = TERMINATING
10 = FAILING
11 = WAITING
12 = EXPIRED
13 = STOPPED
14 = PROCESSING_UNDONote: Only activities that have the "business relevance" flag set are persisted in the activity instance table
- If your typical process models run an average of x activities,
then the number of activity instances should not be more than x times
larger than the number of process instances. Any large deviation from
this ratio might indicate a problem and be worth investigating further.
- If you want to investigate the activity instances belonging to
a particular process instance ID, use a query that is similar to the
following example:
This query might result in output that is similar to the following example:SELECT AI.LAST_STATE_CHANGE, ATP.NAME, AI.STATE FROM ACTIVITY_INSTANCE_B_T AI, ACTIVITY_TEMPLATE_B_T ATP WHERE AI.ATID = ATP.ATID and AI.PIID = '9003011CE5DED75B3EFDEB538C02DAE4' ORDER BY AI.LAST_STATE_CHANGE DESC FETCH FIRST 40 ROWS ONLY WITH UR
In this example, multiple instances of activity 14 are changing to the state FINISHED per second. Comparing this information with your knowledge about the process and how you expect it to behave, this might indicate an unintended loop that needs to be corrected in the model.LAST_STATE_CHANGE NAME STATE ----------------------- ------------- ------- 2011-03-22-16.24.17.964333 Activity_17 7 2011-03-22-16.23.55.925757 Activity_14 5 2011-03-22-16.23.32.528576 Activity_14 5 2011-03-22-16.23.11.976875 Activity_14 5 2011-03-22-16.22.49.582347 Activity_14 5 2011-03-22-16.22.24.257894 Activity_14 5 2011-03-22-16.22.01.723894 Activity_14 5 ...
- To get a better idea of how many instances and activities are
in the database and to verify their numbers you can use variations
on the following example queries:
- If you are using DB2®:
- If you use the DB2 Universal JDBC driver type 4
and get DB2 internal errors such as "com.ibm.db2.jcc.a.re:
XAER_RMERR : The DDM parameter value is not supported. DDM parameter
code point having unsupported value : 0x113f DB2ConnectionCorrelator:
NF000001.PA0C.051117223022" when you test the connection
on the Business Process Choreographer data source or when the server
starts up, perform the following actions:
- Check the class path settings for the data source. In a default setup the WebSphere® variable ${DB2UNIVERSAL_JDBC_DRIVER_PATH} can point to the embedded DB2 Universal JDBC driver which is found in the universalDriver_wbi directory.
- The version of the driver might not be compatible with your DB2 server version. Make sure that you use the original db2jcc.jar files from your database installation, and not the embedded DB2 Universal JDBC driver. If required, changed the value of the WebSphere variable ${DB2UNIVERSAL_JDBC_DRIVER_PATH} to point to your original db2jcc.jar file.
- Restart the server.
- If the db2diag.log file of your DB2 instance
contains messages like ADM5503E as illustrated below:
Increase the LOCKLIST value. For example to set the value to 500, enter the following DB2 command:2004-06-25-15.53.42.078000 Instance:DB2 Node:000 PID:2352(db2syscs.exe) TID:4360 Appid:*LOCAL.DB2.027785142343 data management sqldEscalateLocks Probe:4 Database:BPEDB ADM5503E The escalation of "10" locks on table "GRAALFS .ACTIVITY_INSTANCE_T" to lock intent "X" has failed. The SQLCODE is "-911"
This can improve performance significantly.db2 UPDATE DB CFG FOR BPEDB USING LOCKLIST 500
- To avoid deadlocks, make sure your database system is configured to use sufficient memory, especially for the buffer pool. For DB2, use the DB2 Configuration Advisor to determine reasonable values for your configuration.
- If you get errors mentioning the data source implementation class COM.ibm.db2.jdbc.DB2XADataSource:
- Check that the class path definition for your JDBC provider is correct.
- Check that the component-managed authentication alias is set to BPCDB_nodeName.serverName_Auth_Alias if Business Process Choreographer is configured on a server, and BPCDB_clusterName_Auth_Alias if Business Process Choreographer is configured on a cluster.
- If you use the DB2 Universal JDBC driver type 4
and get DB2 internal errors such as "com.ibm.db2.jcc.a.re:
XAER_RMERR : The DDM parameter value is not supported. DDM parameter
code point having unsupported value : 0x113f DB2ConnectionCorrelator:
NF000001.PA0C.051117223022" when you test the connection
on the Business Process Choreographer data source or when the server
starts up, perform the following actions:
- If you get a database error when deploying an enterprise application that contains a BPEL process or human task, make sure that the database system used by the process container is running and accessible. When an enterprise application is deployed, any process templates and task templates are written into the Business Process Choreographer database.
- If you have problems using national characters. Make sure that your database was created with support for Unicode character sets.
- If tables and views cannot be found in the database and
the create schema option is not enabled, check the following settings:
- If a database schema qualifier is configured, check the following
settings:
- The schema qualifier must match the schema in the database. It must be the same schema as used in the scripts.
- The user must be granted the privileges to work with the database tables and views.
- If no schema qualifier is configured, ensure that:
- The authentication alias of the user must be the same user ID as the one that is used to run the scripts, or must match the schema qualifier that is used in the scripts.
- The user must be granted the privileges to work with the database tables and views.
- If a database schema qualifier is configured, check the following
settings:
- If the create schema option is enabled, and the database
table and views cannot be found, the database tables and objects will
be created automatically using the following terms:
- If a schema qualifier is configured, the tables and views will be created using the schema qualifier.
- If no schema qualifier is configured, the tables and views will be created using the user ID.
- If you get the error message com.ibm.bpe.util.Assert.assertion(Assert.java:66) Assertion violation ! (pWifl != null) in method >> com.ibm.bpe.database.Tom.augmentSharedWorkItem(Tom.java:9815), there is a problem with shared work items in the database, run the dbUtility.py script to check for and repair any database consistency problems. For details about using the utility refer to dbUtility.py administrative script.