IBM Support

JR51847: Full-select deletes in the dbclean utility can lead to excessive locking

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Although full-select deletes can perform better, they require
    additional locks which can lead to locking issues when the LOCK
    LIST is exhausted.
    

Local fix

Problem summary

  • USERS AFFECTED:
    WebSphere Commerce Version 7 customers on  Fixpack 7 or Fixpack
    8 who use DB2 and the dbclean utility
    
    PROBLEM ABSTRACT:
    Full-select deletes in the dbclean utility can lead to excessive
     locking
    
    BUSINESS IMPACT:
    Database contention issues on DB2 databases
    
    RECOMMENDATION:
    

Problem conclusion

  • The default behaviour of dbclean utility has been reverted to
    use a primary-key join to perform DELETE on tables with a row
    limit on DB2 databases.
    
    A new value (2 or fullselect) for the sqlmode parameter is
    provided to perform a direct delete on tables with a row limit
    for customers who are not adversely affected by excessive
    locking scenarios.
    
    The new sqlmode value (2 or fullselect) and existing values are
    described below.
    
    sqlmode
      Optional: You can set the following values for this parameter:
      0 (default)
        Default mode.
        The DELETE statement retrieved from the CLEANCONF table may
    be modified to delete the number of rows specified by the value
    of the commit parameter.
        A commit is issued after each invocation of the modified
    statement and it is executed repeatedly until one of the
    following is true:
        - The number of rows specified by the max parameter have
    been deleted
        - No additional rows are deleted by an invocation of the
    modified statement
    
      1 (direct)
        Direct mode.
        The DELETE statement retrieved from the CLEANCONF table is
    run without any modification by the utility.
        The value specified by the commit parameter is ignored and a
     commit is issued after each invocation of the statement.
        The statement is executed repeatedly until one of the
    following is true:
        - The number of rows specified by the max parameter have
    been deleted
        - No additional rows are deleted by an invocation of the
    statement
    
      2 (fullselect)
        On DB2 (LUW) databases, the DELETE statement may be modified
     to DELETE from fullselect limited by FETCH FIRST n ROWS, where
    n is the value of the commit parameter. This form of DELETE may
    perform better but may also acquire more locks during execution.
     If the LOCKLIST database configuration parameter is large
    enough, consider using this to increase performance.
    
    For databases other than DB2, this value is ignored and 0 is
    assumed.
    

Temporary fix

Comments

APAR Information

  • APAR number

    JR51847

  • Reported component name

    WC BUS EDITION

  • Reported component ID

    5724I3800

  • Reported release

    700

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2014-11-19

  • Closed date

    2015-01-20

  • Last modified date

    2015-01-20

  • 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

    WC BUS EDITION

  • Fixed component ID

    5724I3800

Applicable component levels

  • R700 PSY

       UP



Document information

More support for: WebSphere Commerce Enterprise

Software version: 7.0

Reference #: JR51847

Modified date: 20 January 2015


Translate this page: