IBM BPM, V8.0.1, All platforms > Tuning > Advanced tuning > Process search optimization
Saved search acceleration tools
You use the SchemaGenerator and DataLoad tools to optimize a process search.
Optimizing Process Portal saved searches involves creating two new tables: LSW_BPD_INSTANCE_VAR_NAMES (variables table), and LSW_BPD_INSTANCE_VARS_PIVOT (pivot table). These tools create the schema needed to optimize the performance of Process Portal.
Attention: You need to stop the server before doing this process.
1. Generating a schema
To optimize saved searches, first you need to generate a schema for the new tables. Change directory to INSTALL_ROOT/BPM/Lombardi/tools/search-optimizer enter the following command:
./SchemaGenerator.{sh|bat} -profileName PROFILE_NAME -OPTION
The following parameters are available for use with the SchemaGenerator tool:
Parameter Description -profileName This parameter is optional and should be used if you want to run the tool against a profile that is different from the default profile for your environment. Specify the name of the profile that you want to use. The OPTION argument defines one of the following:
- execute - The tool immediately creates the entire schema.
- output FILE_NAME - Instead of immediately creating the schema, the tool writes all the SQL statements the database file defined by FILE_NAME.
- screen - Instead of immediately creating the schema, the tool writes all the SQL statements to the command window.
When you choose the output or screen option, you can review the changes the tool will make, and then make them manually. If you choose anything other than the -execute option, you must manually run the SQL statement before continuing. This lets you review the changes that the tool wants to perform, and lets you make any changes necessary for your environment before executing them (for example, providing SQL hints or adding indices).
If you manually run the SQL statement, consult IBM Support before you change any column names or data types.
The following shows an example using the execute option:
>./SchemaGenerator.sh oracle -execute INFO SchemaGenerator - SchemaGenerator starting INFO SchemaGenerator - dbdriver : oracle.jdbc.driver.OracleDriver INFO SchemaGenerator - dburl : jdbc:oracle:thin:@utica.lombardiqa.com:1521:uticasid INFO SchemaGenerator - dbuser : adminusr INFO SchemaGenerator - Processing BPD ID 784afc31-e47b-4e83-b596-20b3cee2b422 INFO SchemaGenerator - Processing BPD ID c904b3b1-afc1-4698-bf5a-a20892c20275 INFO SchemaGenerator - Processing BPD ID e39cc53a-b75c-498c-8c28-43761fb73f2d INFO SchemaGenerator - Finished processing BPDs INFO SchemaGenerator - Executing Pivot Table DDL INFO SchemaGenerator - Executing Variable Names Table DDL INFO SchemaGenerator - Executing insert statements for Variable Names Table INFO SchemaGenerator - SchemaGenerator completed successfully.The following shows an example using the output option:
>./SchemaGenerator.sh oracle -output pivot.sql INFO SchemaGenerator - SchemaGenerator starting INFO SchemaGenerator - dbdriver : oracle.jdbc.driver.OracleDriver INFO SchemaGenerator - dburl : jdbc:oracle:thin:@utica.lombardiqa.com:1521:uticasid INFO SchemaGenerator - dbuser : adminusr INFO SchemaGenerator - Processing BPD ID 784afc31-e47b-4e83-b596-20b3cee2b422 INFO SchemaGenerator - Processing BPD ID c904b3b1-afc1-4698-bf5a-a20892c20275 INFO SchemaGenerator - Processing BPD ID e39cc53a-b75c-498c-8c28-43761fb73f2d INFO SchemaGenerator - Finished processing BPDs INFO SchemaGenerator - Writing DDL to pivot.sql INFO SchemaGenerator - Finished writing DDL to pivot.sql INFO SchemaGenerator - SchemaGenerator completed successfully.
2. Loading data
After the tables are created and all variables are loaded into the variables table, you must load the LSW_BPD_INSTANCE_VARS_PIVOT table with data from all BPD instances that are already in progress. This ensures that the search queries will see the existing instances, as well as any new instances that may be created.
Change directory to INSTALL_ROOT/BPM/Lombardi/tools/search-optimizer and enter the following command:
./DataLoad.{sh|bat} -profileName PROFILE_NAME
The DataLoad tool populates the pivot table with data from every currently deployed BPD instance in your database. This tool provides status messages to keep you informed of its progress.
The following shows an example of the DataLoad output:
INFO DataLoad - DataLoad starting INFO DataLoad - dbdriver : oracle.jdbc.driver.OracleDriver INFO DataLoad - dburl : jdbc:oracle:thin:@utica.lombardiqa.com:1521:uticasid INFO DataLoad - dbuser : adminusr INFO DataLoad - Started at: 2011-09-06 16:26:11.26 INFO DataLoad - Count obtained INFO DataLoad - Creating values Processed 1000 records so far plus 1 instances with no variables, current block took 16.97 seconds, 0.95% complete, 0.28 minutes elapsed, 29.55 minutes remaining Processed 2000 records so far plus 1 instances with no variables, current block took 16.42 seconds, 1.90% complete, 0.56 minutes elapsed, 28.80 minutes remaining ... Processed 104000 records so far plus 4 instances with no variables, current block took 16.67 seconds, 98.58% complete, 29.11 minutes elapsed, 0.42 minutes remaining Processed 105000 records so far plus 4 instances with no variables, current block took 16.19 seconds, 99.53% complete, 29.38 minutes elapsed, 0.14 minutes remaining INFO DataLoad - Total instances copied into pivot table: 105500 INFO DataLoad - Total instances with no variable data copied into pivot table: 5 INFO DataLoad - Finished at: 2011-09-06 16:58:00.891 INFO DataLoad - DataLoad completed successfully.