Technical Blog Post
Suggestions for reducing assigned participant groups against MS SQL Server
The article will give your some suggestions for reducing assigned participant groups against MS SQL Server based on a real case.
When you are running Business Process Manager (BPM) V18.104.22.168, you might hit the following exception when searching instances by user through Process Inspector:
[10/13/15 16:14:04:184 CST] 00000313 RestHelper 3 Encountered exception has message: count query returned no rows
com.lombardisoftware.client.delegate.BusinessDelegateException: count query returned no rows
Caused by: com.lombardisoftware.core.TeamWorksException: count query returned no rows
The query with ifix will run correctly with DB2 and Oracle. However, if you are using Database MS SQL Server, you will still get the following exception:
nested exception is com.microsoft.sqlserver.jdbc.SQLServerException:
The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100.
The new exception shows a limitation for MS SQL Server to have a maximum of 2100 parameters: "nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100." So we need to reduce the parameter which is the assigned participant groups below 2100.
In order to reduce the number of assigned participant groups below 2100, some snapshots which are no longer required can be cleaned up. The background on this step is that each snapshot has it's assigned participant groups. When removing the snapshots, the assigned groups will also be removed. Thus it can reduce the number of groups that are included for the SQL statement being executed.
For the snapshot cleanup, it is advisable to keep the number as low as possible, as the snapshot information is included in a number of queries. Thus less data will result in better performance. Of course one needs to keep in mind what is required from a business perspective, e.g. if there are still active process instances. However archiving of completed work should be done outside of the product database, to keep the runtime database slim.
Concerning the database entries, the developer just sent some SQL statements to get an idea what data is stored in the database. You can find the MS SQL Server version here, these need to be run against the BPMDB database. The SQL statements are provided as a txt zip file by email@example.com and he welcomes any feedback: SQLServer_ProcessInstances_Tasks.txt.zip
So what you need to do in order to reduce the assigned participant groups below 2100:
1. Check whether you have installed the mandatory ifixes for snapshot deletion.
There are some mandatory ifixes for snapshot deletion, please check whether they are installed before doing delete operation.
2. Run the SQL in the above attachment to have an overall idea about the data in your database.
3. Delete the snapshot.
In the BPMDB database, the snapshot data is stored in table LSW_SNAPSHOT. You can simply get the total number of snapshots by using the sql statement: select count(snapshot_id) from LSW_SNAPSHOT.
Please note that you may have to determine which ones need to be deleted, but the above SQL statements (the ones provided by developer in the attachment) should list the snapshots that have process instance, thus the ones not listed there could be good candidates. Also cleanup is roughly covered in article Purging data in IBM Business Process Manager.
If you are not using MS SQL server, you can also modify your specific SQL by referring to the above SQL statement to get an idea what data is stored in the other type of database.