IBM BPM, V8.0.1, All platforms > Administer the IT infrastructure > Administer Business Process Choreographer

Remove redundant indexes

If you migrated your system from WebSphere Process Server Version 7.0.x or earlier, your Business Process Choreographer database contains some indexes that speed up specific types of API queries. However, if you do not use any of those queries, the indexes are redundant. Removing the redundant indexes reduces the size of the database, eliminates the processing necessary to maintain the indexes, and therefore helps improve the performance of your database.

Check whether any of your applications use the Business Flow Manager or Human Task Manager's query or query table APIs. You should only perform this procedure if one of the following conditions is true:

• No applications use the query or query table APIs.
• Some applications use the query or query table APIs, but it does not matter if the applications get slower response times from the database for those queries.

Because the script can take a long time to run and increases the load on the database, choose a time to run the script when your Business Process Choreographer database system is under a low load.

Procedure

1. Change to the directory where the script for your database is located. Enter the command:

   cd  profile_root/dbscripts/ProcessChoreographer/DB2zOS

   Enter one of the commands:
   • 

     cd profile_root/dbscripts/ProcessChoreographer/ database_type

   • cd profile_root\dbscripts\ProcessChoreographer\ database_type

   Where database_type is the type of your database system. During migration, an optionalUpgradeIndexes.sql script is generated for each Business Process Choreographer configuration, based on the corresponding upgradeSchema.properties file.

2. At a time when there is either a low load on the Business Process Choreographer database (BPEDB) or when your server or cluster is stopped, run the optionalUpgradeIndexes.sql script. In the DB2 command-line processor, enter the command:

   db2 -tf optionalUpgradeIndexes.sql

   In a database client command-line processor, enter one of the following commands:

   • For DB2: db2 -tf optionalUpgradeIndexes.sql

   • For Oracle: sqlplus dbUserID/dbPassword@database_name @optionalUpgradeIndexes.sql

   • For Microsoft SQL Server: sqlcmd -U dbUserID -P dbPassword -e -i optionalUpgradeIndexes.sql

   For more information about how to run scripts on your database, refer to the product documentation for your database. If you have not stopped the server or cluster, there is an increased probability that deadlocks will occur while the script is running.

3. If there are any errors displayed, perform the following:

   1. You can ignore any error messages reporting that certain indexes do not exist. This is either because you customized the schema so that the indexes were never created, or some of the indexes have already been removed.

   2. If there are any other types of errors, fix the problem, then try running the script again.

4. If you have configured a Business Process Archive Manager, you can run the script against the archive database (BPARCDB) to remove the same redundant indexes from the archive database. In case of problems, make sure that the schema qualifier in the script matches that of the archive database.

Results

The redundant indexes have been removed from the Business Process Choreographer database, which means that many database statements will have better performance.

Administer Business Process Choreographer

Related tasks:
Adding support for shared work items