Tuning Process Portal searches

Edit online

In non-federated Business Automation Workflow environments, you can optimize saved searches and control search results by using acceleration tools or setting configuration properties.

When you set configuration property values in .xml files, the rules that are described in The 100Custom.xml file and configuration apply.

Important: The following information applies to both Heritage Process Portal (deprecated) and Process Portal.

Saved search count performance optimization

The process-search-engine-count-optimization property is used to determine the displayed values for the task summary and task statistics counts (On Track, At Risk, Overdue) in the Process Portal Work and saved search dashboards. For example, with the default property value of 500, if you save a search that returns 600 tasks, the summary count shows 500+. The task statistics include only the first 500 tasks in the task list, so Process Portal users might see the following counts for the task categories: 100+ On Track, 200+ At Risk, 200+ Overdue.

If you expect more than 500 results entries for a search, for example, if you are using the TWSearch function or saved search APIs, or if your Process Portal users usually have more than 500 tasks, you can increase this number.

To retrieve a different number of entries, complete the following steps.
  1. Insert the following section into the 100custom.xml file: 
    <properties>
    <server merge="mergeChildren">
  <process-search-engine-count-optimization merge="replace">500</process-search-engine-count-optimization>
       </server>
</properties>
  2. Adjust the 500 value to meet your needs. The larger the value, the more memory and time are needed to retrieve the entries.
  3. Save the changes to the 100custom.xml file, and restart the server.

Optimize saved searches with business data variables

You can optimize your process searches by using the saved search acceleration tools.

For systems with a large amount of business data that is used in searches (for example 10+), the Process Portal tasks and instance queries might operate slower than expected. When you use the saved search acceleration tools to optimize your process searches, Process Portal searches that filter on business data are faster.

Tip: You optimize a process search only on runtime or production systems. It is suggested that you do not optimize process searches on systems that are used for development, or for systems where changes to deployed applications happen frequently.
Use the following tools to optimize a process search:
  • SchemaGenerator tool - This tool generates two new tables: LSW_BPD_INSTANCE_VAR_NAMES (variables table), and LSW_BPD_INSTANCE_VARS_PIVOT (pivot table). It also generates the schema for each table.
  • DataLoad tool - This tool populates the variables table with data from the BPD instances that are currently in progress.
The pivot and variables tables
The pivot and variables tables ensure that your query runs efficiently after your process search is optimized. The tables present data in the following format, with each item in its own column.
  • The pivot table
    • Instance ID
    • Every searchable business data variable that is defined in all currently deployed BPDs
    • Every business data variable that is defined in LSW_BPD_INSTANCE_VARIABLES
  • The variables table
    • Variable name
    • Column name
    • Data type
Process search optimization overview
A process search optimization can consist of three unique processes:
  • Enabling an optimization
  • Disabling an optimization
  • Enabling an optimization again
To enable process search optimization, you must run the optimization tools:

To disable optimization, complete the following steps:

  1. Shut down the server.
  2. Remove the pivot and variables tables from your database.
  3. Restart the server.
To re-enable optimization, complete the following steps:
  1. Deploy all BPD updates.
  2. Stop the server.
  3. Remove the pivot and variables tables from your database.
  4. Rerun the SchemaGenerator and DataLoad tools.
  5. Restart the server.
System requirements and restrictions

The following requirements and restrictions apply:

  • You use this process only for production deployments, or for systems that simulate production deployments.
  • When using a pivot table, you must ensure that you do not use more than 1000 searchable variables. If you need to exceed this limit, you must drop the pivot table and use the default search function instead. This will result in performance that is inferior to that of a pivot table. (The default search function searches the variable values using the LSW_BPD_INSTANCE_VARIABLES table.)
  • If you have deployed a new snapshot that contains changes to business data variable definitions in BPDs (such as adding new variables, renaming any existing variable, or modifying the type of any existing variable) or business data alias changes, you must rerun the tools to rebuild the pivot table.
    Note: Define business data by selecting the Visible in Process Portal option to make the corresponding variable available on the BPD property sheet. See Making business data available in searches and views.
    Attention: If you do not rerun the optimization tools when changes to business data occur, your process searches might generate failures. For example, if you changed the type of business data and do not rerun the optimization tools, the old business data type persists in the pivot table, which results in potential SQL exceptions. Similarly, if you added or changed a business data alias and do not rerun the optimization tools to regenerate the pivot table, SQL exceptions might also occur.
    Restriction:
    DB2
    The following restrictions apply to a DB2 database.
    • The copies of all string values in the pivot table are truncated if they exceed 128 bytes.
    • The sizes of all unique business data variables, when added together, must be less than 32767 bytes. This means that if all your business data variables were strings, you would be limited to 32767/128 = 255. This restriction applies to all unique business data variables that are defined across all your deployed BPDs. For example, if you had three BPDs deployed that each had six business data variables, but one of them was defined in exactly the same way in all three BPDs, then you would have a total of 16 unique business data variables.
    Oracle
    The following restrictions apply to an Oracle database.
    • When you query business data by using the DB-based search, the search is typically accessed through the JavaScript API and several REST APIs. If you pass a decimal value or an empty string value to a process and the value is stored in a variable that is exposed to searches, the APIs might return a search result for Oracle that differs from the search result that is returned for other database products. Generally, this behavior causes a problem only if you are using both Oracle and another database product, such as DB2.
    • If you pass a decimal value that has zeros as fractions to a process and the decimal value is stored in a variable that is exposed to searches (such as the Visible in Process Portal variable), then the APIs return the value as it is formatted by the database. For example, if you pass a value of 150.00 to the process, the APIs would return a result of 150 for Oracle and a result of 150.00 for DB2. Any problems that result from this discrepancy can generally be fixed at the application level. For example, in a Java application, you can use the java.text.NumberFormator java.text.DecimalFormat class to help resolve the problems.
    • If you pass an empty string to a process and the empty string is stored in a variable that is exposed to searches (such as the Visible in Process Portal variable), then the APIs return a result of null for Oracle and a result of empty string for DB2.
    All
    The following restriction applies to the databases of all database products.
    • The string values for business data are truncated to 512 characters.

Improve performance of the process search engine

When you are working on z/OS, the following properties affect process engine performance.
use-concatenation-optimization
This property controls the performance of SQL statements. When it is set to true (its default value), it replaces specific union all operations with or operators, which increases the CPU and memory consumption.

When you run a DB2 query for process instances by using Process Inspector in the Process Admin Console, the query might fail and return the SQLCODE=-129 (too many tablename in query) error message in the browser if a large number of process instances is returned. If this error occurs, set the use-concatenation-optimization property to false for the query to succeed.

use-concatenation-optimization-for-authorization-lobes
When the use-concatenation-optimization property is kept to true, the use-concatenation-optimization-for-authorization-lobes property, which is also set to true by default, reduces the number of table joins in queries to DB2 or Oracle databases.

Control task visibility in dashboards

With the enable-group-constraint-for-claimed-tasks property, you can control how tasks are visible in Process Portal dashboards. By default, this property is set to false so that users can see only the tasks that they are working on or which they can start. The tasks that other users of the same user group have claimed are not listed. To allow all users in a user group to see all the claimed tasks for the users in the group, set this property to true.

Make search on user names case-sensitive

The case-insensitive-security-cache configuration property gives you control over case sensitivity for searches on user names. By default, this property is set to true. This setting means that you can find task assignees by their name in the database regardless of how you capitalize the user name in the search string. If you want the search to be case-sensitive, you can set this property to false in the 60Database.xml file. For more information, see Configuring IBM Business Automation Workflow to handle white space and letter case variations in the LDAP server.

Show or hide unexposed processes

In saved searches, you can configure how the Process Definition filter shows business processes. By default, the process-definition-search-all-bpds property is set to false and shows only exposed processes. To show both exposed and unexposed processes, set the property to true.