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

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 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