JR42110: LOW PERFORMANCE OF SAVED SEARCH FUNCTIONALITY IN PORTAL

Version 7.5.1 Fix Pack 1 for the IBM Business Process Manager products

APAR status

• Closed as program error.

Error description

• If there are too many searchable variables in a BPD instance,
  the response time for saved searches functionality in portal
  may be slow. This can be improved by using accelerator tables.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:  Users of BPM Express, BPM Standard and BPM  *
  *                  Advanced.                                   *
  ****************************************************************
  * PROBLEM DESCRIPTION: Slow response time for saved searches   *
  *                      functionality in portal when too many   *
  *                      search variables in BPD instance.       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  If there are too many searchable
  variables in BPD instance, the
  response time for saved searches
  functionality may be slow,
  which could be improved by
  accelorator table.
  

Problem conclusion

• The fix is available as a part of the JR42012 ifix package
  (http://www-01.ibm.com/support/docview.wss?uid=swg1JR42012)
  and needs to be installed as follows:
  - Install JR42012 on both Process Designer and Process
  Center/Server using IM
  - On Process Center/Server run bpmprofileUpgrade as outlined in
  http://www-01.ibm.com/support/docview.wss?uid=swg27022245#recove
  rFailure
  
  Once the fix has been installed you can use the
  SchemaGenerator and DataLoad Tools to optimize a Process
  Search. The steps for the same are:
  
  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
  [IBM_BPM_home]/BPM/Lombardi/tools/search-optimizer enter the
  following command:
  ./SchemaGenerator.{sh|bat} -profileName <PROFILENAME> -OPTION
  The following parameters are available for use with the
  SchemaGenerator tool:
  
  -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 <FILENAME> - Instead of immediately creating the
  schema, the tool writes all the SQL statements the database
  file defined by FILENAME.
  
  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).
  
  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 on a
  profile named StandAloneProfile:
  
  >./SchemaGenerator.sh -profileName StandAloneProfile -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 -profileName StandAloneProfile -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
  [IBM_BPM_home]/BPM/Lombardi/tools/search-optimizer and enter
  the following command:
  ./DataLoad.{sh|bat} -profileName <PROFILENAME>
  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.
  
  3. Updating the tables
  
  During normal operation, the new tables will be kept in sync.
  However, any time you deploy a new version of a Process
  Application, you will need to
      (a) shut down the server (this should be done after
  deploying the new version of the Process Application)
      (b) drop the two tables (LSW_BPD_INSTANCE_VAR_NAMES and
  LSW_BPD_INSTANCE_VARS_PIVOT),
      (c) run the SchemaGenerator and DataLoad tools again,
      (d) restart the server
  
  The reason for this is to make sure that the pivot table
  columns are in sync with all the Process Applications that are
  deployed. No data will be lost if you follow this procedure.
  If you forget, however, you may see warnings such as this in
  your system log: "Field foo with type INTEGER not found in
  pivot table. Field will be ignored." If you see this message,
  data is not being lost, but some Business Data variables will
  not properly display in the inbox. If you follow steps (a)
  through (d) above, you will fully recover with no loss of data.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  BPM ADVANCED

• Fixed component ID

  5725C9400

Applicable component levels

• R750 PSY

     UP