JR44694: CMVC 224865 - IMPROVE PERFORMANCE OF CASCADED DELETES ON SOME WEBSPHERE COMMERCE TABLES

7.0.0-WS-WCServer-FP009
WebSphere Commerce Version 7.0.0.8 Fix Pack
WebSphere Commerce Version 7.0.0.9 Fix Pack

APAR status

• Closed as program error.

Error description

• Deletes invoked on records of some tables that eventually
  cascade to child tables in a WebSphere Commerce database may
  take longer than expected to complete. This can occur if the
  size of the child table is significantly large and there isn't
  an index on the child table whose leading column is the same as
  the leading column of the foreign-key constraint that connects
  the child to the parent table.
  
  Supplementary database index(es) on affected child tables that
  satisfy this condition, i.e., have the leading column of the
  foreign-key constraint as the leading column in the index, are
  being provided to alleviate performance issues that can occur
  due to the scenario previously described.
  
  Specifically, the child tables being addressed are:
  CATENTRYATTR
  ATTRDESC
  ATTRVALDESC
  

Local fix

• If performance issue has been diagnosed as being the result of
  delay in completion of cascaded delete, identify affected child
  tables in the hierarchy of the parent table on which initial
  delete was invoked. Once identified, ensure that at the very
  least, the leading column in the foreign-key constraint in the
  child table is present as the leading column in at least one
  index defined on the child table.
  

Problem summary

• USERS AFFECTED:
  WebSphere Commerce users on Version 7.0 experiencing delays in
  completion of processing after having invoked a cleanup
  operation that resolves to a table in the WebSphere Commerce
  database that has one or more child tables.
  
  PROBLEM ABSTRACT:
  Improve performance of cascaded deletes on some WebSphere
  Commerce tables
  
  BUSINESS IMPACT:
  Delete operations on the database that have been invoked from
  either the application or directly on the database, that cascade
  down to child tables, may take longer than expected to finish.
  
  RECOMMENDATION:
  Refer to the Workaround section to attempt resolution.
  Otherwise, contact WebSphere Commerce Support for assistance
  

Problem conclusion

• A delete invoked on a parent table that cascades to the some
  child tables may take longer than expected to finish due to the
  absence of an appropriate index. Specifically, absence of an
  index with a leading column which corresponds to the foreign-key
  constraint defined on the child table will cause the cascaded
  delete to take longer than expected to finish. This has been
  addressed by creating supplementary database indexes on affected
  child tables with the leading column set to that which is used
  in the problematic foreign-key constraint that caused the
  cascade.
  
  The child tables being addressed with this APAR are:
  CATENTRYATTR
  ATTRDESC
  ATTRVALDESC
  
  -------------------------------------------------------------
  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