JR42110: LOW PERFORMANCE OF SAVED SEARCH FUNCTIONALITY IN PORTAL

Subscribe

You can track all active APARs for this component.

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 number

    JR42110

  • Reported component name

    BPM ADVANCED

  • Reported component ID

    5725C9400

  • Reported release

    751

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-02-28

  • Closed date

    2012-11-01

  • Last modified date

    2012-11-06

  • 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



Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

IBM Business Process Manager Advanced

Software version:

7.5.1

Reference #:

JR42110

Modified date:

2012-11-06

Translate my page

Machine Translation

Content navigation