IBM Support

JR46656: 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.

Subscribe

You can track all active APARs for this component.

 

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:

    JR46659 JR46660

Fix information

  • Fixed component name

    BPM ADVANCED

  • Fixed component ID

    5725C9400

Applicable component levels

  • R751 PSY

       UP

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSFTN5","label":"IBM Business Process Manager Advanced"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"7.5.1","Edition":"","Line of Business":{"code":"LOB36","label":"IBM Automation"}}]

Document Information

Modified date:
28 October 2013