Direct links to fixes
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 number
JR44694
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
2012-11-09
Closed date
2013-01-09
Last modified date
2013-01-09
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"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"7.0","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
09 January 2013