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.
Before you begin
Check whether any of your applications use the Business Flow
Manager or Human Task Manager's query or query table APIs. 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 that 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
- Change to the directory where the tuning script for your
database is located. Enter one of the commands:
cd install_root/BPM/dbscripts/database_type/Tuning
cd install_root\BPM\dbscripts\database_type\Tuning
Where database_type is the type of your database
system.
- Make a copy of the optionalUpgradeIndexes.sql script,
for example, named myOptionalUpgradeIndexes.sql.
- Edit your myOptionalUpgradeIndexes.sql script
copy.
- Replace all occurrences of the string @SCHEMA@ with
the name of your schema.
Important: If you
use an implicit schema, you must delete the string @SCHEMA@.,
including the trailing dot.
- If you are using a DB2 for z/OS database, replace all
occurrences of the string @STOGRP@ with the name
of your storage group.
- If you are using an Oracle database, replace all occurrences
of the string @INDEXTS@ with the name of the index
table space.
- Save your changes.
- At a time when there is either a low load on the Business
Process Choreographer database (BPEDB) or when
your cluster is stopped, run your edited myOptionalUpgradeIndexes.sql script. In your database client command-line processor, enter one of
the following commands:
- For DB2®: db2 -tf
myOptionalUpgradeIndexes.sql
- For Oracle: sqlplus dbUserID/dbPassword@database_name
@myOptionalUpgradeIndexes.sql
- For Microsoft SQL Server: sqlcmd
-U dbUserID -P dbPassword -e -i myOptionalUpgradeIndexes.sql
For more information about how to run scripts on your database,
see the product documentation for your database. If the
cluster is still running, there is an increased probability of deadlocks
occurring while the script is running.
- If any errors are displayed, complete the following steps:
- You can ignore any error messages that report 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 were
already removed.
- If there are any other types of errors, fix the problem,
then try running the script again.
- Optional: If you 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. If there are any problems, make sure that
the schema qualifier in the script matches that of the archive database.
Results
The redundant indexes were removed from the Business Process
Choreographer database, which means that many database statements
should have better performance.
