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
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSYSYL","label":"WebSphere Commerce Enterprise"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"7.0","Line of Business":{"code":"LOB31","label":"WCE Watson Marketing and Commerce"}}]
Document Information
Modified date:
11 December 2021