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.
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} {db2, oracle, mssql} -profileName PROFILE_NAME -OPTION
The following parameters are available for use with the SchemaGenerator tool:
Parameter | Description |
---|---|
-profileName | The Process Server node profile. This can be Node01Profile or Process01Node, depending on the name used during installation. The -profileName parameter is optional; use it if you want to run the tool against a profile that is different from the default profile for your environment. |
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.
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 using the DataLoad tool. The tool loads all open (active) BPD instances that are currently on the server. It does not load data for closed instances. Loading the data allows the search queries to use the pivot tables and find the data. When new instances are created, the data will be automatically loaded into the pivot tables.
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.