JR46660: LONG SERVER STARTUP TIME DUE TO A LARGE NUMBER OF RECORDS IN THE WORK_ITEM_T TABLE WHEN GROUP WORK ITEM FEATURE IS NOT ENABLED.

Download Version 8.0.1 Fix Pack 2 for the IBM Business Process Manager products
Version 7.5.1 Fix Pack 2 for the IBM Business Process Manager products
Version 8.5 Refresh Pack 5 for the IBM Business Process Manager products

APAR status

• Closed as program error.

Error description

• Issue Detail:
  ----------------------------------------------------------------
  Server is taking over 1 hour to start due to 300,000,000 rows
  data stroed in DB.
  
  In customer environment, group work item feature is not enabled
  in human task manager. so during WPS startup, human task manager
  will use following sql to check if the group workitem is enabled
  or not.
  
  (select 'Y' from work_item_t where (group_name is not null)
  fetch first 1 rows only)
  
  and even there is index created on group_name column, the sql
  performs still very bad because indexing on a NULL column does
  not have much help on a table which has a huge volume data.
  
  one workaround customer used is to load the work_item table into
  buffer pool before starting wps. in this approach, since there
  is no physical read, the sql can return quickly.
  
  and User also tried to enable group workitem feature in human
  task container, then the sql will never be executed. but user
  is not sure what's the impact of turning on this flag, so in
  current production system, user is still running with the
  feature unchecked.  if user can confirm turning on this feature
  does not cause impact to existing system, this should be right
  solution for this probl otherwise, user still need to fix it at
  code level by either using faster sql statement or other
  alternative.
  ----------------------------------------------------------------
  

Local fix

• No IFix/Work Around provided.
  
  developer previous suggestion:
  --------------------------------------------------------------
  30 seconds is quite a long time to detect if a value does not
  exist in an index.
  Since the query is part of a simple check, an isolation level of
  UR could be considered.
  
  Suggest user to execute this SQL as below.
  
  (select ?Y? from work_item_t where ( auth_info = 3 ) fetch
  first 1 rows only with UR
  ---------------------------------------------------------------
  
  user testing result:
  --------------------------------------------------------------
  User ran the statement with UR. for this time, the sql execution
  only takes 1 second.
  ---------------------------------------------------------------
  

Problem summary

• Long server startup time due to a large number of records in
  table WORK_ITEM_T if the group work item feature is not enabled
  in human task manager.
  
  PROBLEM DETAILED DESCRIPTION:
  During server startup, if the group work item feature is
  disabled, human task manager will use the following SQL to
  check if a group work item is present in the database:
  
  (select 'Y' from work_item_t where (group_name is not null)
  fetch first 1 rows only)
  
  If there is a high volume of work items but no group work item
  in the database, the SQL might perform slow even though an
  index is present on the group_name column.
  

Problem conclusion

• The problem is solved by using a different SQL query that checks
  the value of a column, that is always not NULL.
  

Temporary fix

• Not applicable
  

Comments

APAR Information

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

  JR46656

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

Fix information

• Fixed component name

  BPM ADVANCED

• Fixed component ID

  5725C9400

Applicable component levels

• R850 PSN

     UP