Fixes are available
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. FIX AVAILABILITY: iFix for 7.5.1.1 is available on Fix Central, search for APAR JR46656 at http://www.ibm.com/support/fixcentral/ Fix is also targetted for inclusion in next fixpack for BPM V7.5.1, BPM 8.0.1, BPM 8.5.0 When obtaining any of the above fixes, be sure to download the accompanying readme, for itself, and any prerequisite fixes, and review them thorougly.
Temporary fix
Comments
APAR Information
APAR number
JR46656
Reported component name
BPM ADVANCED
Reported component ID
5725C9400
Reported release
751
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2013-05-16
Closed date
2013-10-28
Last modified date
2013-10-28
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
R751 PSY
UP
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSFTN5","label":"IBM Business Process Manager Advanced"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"7.5.1","Line of Business":{"code":"LOB45","label":"Automation"}}]
Document Information
Modified date:
08 January 2022