7.5.1.2 fixpack content

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} {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 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).
Tip: The generated SQL statement might be too long for the SQL command line processor if the system contains a very large number of searchable variables. You might see the SP2-0027: Input is too long (> 2499 characters) error. To solve the problem, split the statement across multiple lines of less than 2500 characters each.

If you are using the search accelerator table for search optimization with Oracle as the database management system, the create procedure SQL script cannot be executed successfully unless you enter / as the last line in the generated SQL script.

Note: 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 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.