JR42758: CMVC 218975 - IMPROVE THE ACTIVITYCLEANUP SCHEDULER JOB TO BE ABLE TO REMOVE MILLIONS OF RECORDS WITHOUT DB LOCKING

7.0.0.9 for Developer Express
7.0.0.9 for Developer Professional
7.0.0.9 for Developer Enterprise
7.0.0-WS-WCServer-FP009
JR42758: CMVC 218975 - Improve performance of ActivityCleanUp scheduler job
WebSphere Commerce Version 7.0.0.7 Fix Pack
WebSphere Commerce Version 7.0.0.9 Fix Pack

APAR status

• Closed as program error.

Error description

• The ActivityCleanUp Job is an out-of-the-box scheduler job that
  runs daily, and its purpose is to invalidate and delete
  Activities. It is documented in detail in the following
  document:
  
  ActivityCleanUp URL
  http://publib.boulder.ibm.com/infocenter/wchelp/v7r0m0/index.jsp
  ?topic=%2Fcom.ibm.commerce.developer.doc%2Frefs%2Frjsactivitycle
  anupl.htm
  
  If the job is disabled, or Activities created at high frequency,
  the number of records in the Business Context tables (CTXMGMT
  and CTXDATA) may grow rapidly. In such cases, processing and
  subsequently removing data may result in a high number of locks
  being held and for some database types, for either table to be
  locked pending completion of the job. As a result, contention
  issues may occur on concurrent access of these tables.
  
  This fix provides alleviation for such cases by changing default
  behavior as follows:
  - Changing number of records to be processed and removed
  at-a-time to 10000, instead of all
  - Invoking a commit on database after each batch of 10000
  records has been processed
  
  Additionally, an optional switch is being provided that may be
  used to limit the total number of rows to process and remove.
  This switch is disabled by default.
  
  Overrides are available and can be instrumented as follows
  (refer to the ActivityCleanUp URL document referenced above for
  more details on how to instrument these overrides):
  - commitCount: specifies number of records to be updated or
  deleted at a time, followed by a commit; default is 10000; value
  less than 1 implies one transaction to update and one
  transaction to delete
  - maxRows: specifies maximum number of records to be updated or
  deleted; default is unlimited (-1 or any value than 1); value
  higher than 0 implies no more than that number of records will
  be processed and deleted
  
  As an example, the default specification of the job with these 2
  additional switches would look like this:
  <url-type>://<hostname>/<path><existing-specification>&commitCou
  nt=10000&maxRows=-1
  

Local fix

• The DBClean utility can be used to maintain the Business Context
  tables as described in the following technote:
  
  Maintaining the Business Context tables using DBClean
  http://www.ibm.com/support/docview.wss?uid=swg21378083
  

Problem summary

• USERS AFFECTED:
  WebSphere Commerce users on v7.0 who do not use the DBClean
  utility to maintain the Business Context tables
  
  PROBLEM ABSTRACT:
  Improve performance of ActivityCleanUp Scheduler Job by reducing
  database contention
  
  BUSINESS IMPACT:
  If there is a significantly large amount of data to be
  cleaned-up in the Business Context tables, the ActivityCleanUp
  job may fail due to contention issues on these database tables
  
  RECOMMENDATION:
  

Problem conclusion

• Implementation of the ActivityCleanUp Job was adjusted to reduce
  contention and increase concurrent database access on tables
  being processed by the job
  -------------------------------------------------------------
  The latest available maintenance information can be obtained
  from the Recommended Fixes for WebSphere Commerce technote:
  http://www.ibm.com/support/docview.wss?rs=3046&uid=swg21261296
  

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