Troubleshooting the Business Process Choreographer database and data source

IBM BPM, V8.0.1, All platforms > Troubleshooting and support > Troubleshooting installation and configuration > Troubleshooting the Business Process Choreographer configuration
Troubleshooting the Business Process Choreographer database and data source

Use this task to solve problems with the Business Process Choreographer database and data source.

Both Business Flow Manager and Human Task Manager need a database. Without the database, enterprise applications that contain BPEL processes and human tasks will not work.

Procedure
If the Business Process Choreographer database is getting bigger and slower over time, consider performing following:
Remove unnecessary objects from the Business Process Choreographer database
Tuning the DB2 database for Business Process Choreographer
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:
select count(*) from process_instance_b_t with ur
By specifying with UR, locking is avoided, which could affect other users of the database.
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
Only activities that have the "business relevance" flag set are persisted in the activity instance table
To see which process instances have the most activities, use the following query:
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
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 similar to the following:
PIID                                  NAME        STATE   NUMBER_OF_ACTIVITIES
-----------------------------------   ----------  ------  --------------------
x'9003011CE5DED75B3EFDEB538C02DAE4'   claimWork        6                147047
x'9003011E841DE9AF3EFDEB53045C4103'   claimWork        6                 96609
x'9003011E841DDEF13EFDEB53045C3DD9'   claimWork        6                 96462
...
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.
If you are interested in process instances that are in a particular state, use the following query:
select count(*) from process_instance_b_t where state =  processState with ur
where processState is one of the following integer values representing the state of the process instance:
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:
select count(*) from activity_instance_b_t where state =  activityState with ur
where activityState is one of the following integer values representing the state of the activity instance:
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_UNDO
Only activities that have the "business relevance" flag set are persisted in the activity instance table
If you want to investigate the activity instances belonging to a particular process instance ID, use a query like the following:
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
This query might result in output similar to the following:
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
...
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.
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. Verify 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:
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"
Increase the LOCKLIST value.
For example to set the value to 500, enter the following DB2 command:
db2 UPDATE DB CFG FOR BPEDB USING LOCKLIST 500
This can improve performance significantly.
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 are using a remote DB2 for z/OS database, and you get SQL code 30090N in the SystemOut.log file when the application server attempts to start the first XA transaction with the remote database, perform the following:

Verify that the instance configuration variable SPM_NAME points to the local server with a host name not longer than eight characters.
If the host name is longer than eight characters, define a short alias in the etc/hosts file.
Otherwise, you might have invalid sync point manager log entries in the sqllib/spmlog directory. Try clearing the entries in the sqllib/spmlog directory and restart.
Consider increasing the value of SPM_LOG_FILE_SZ.
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:

If a database schema qualifier is configured, check the following:

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 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 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.
Troubleshooting the Business Process Choreographer configuration