JR46056: CMVC 227926 - ENSURE RELEVANT RECORDS IN STAGLOG CORRECTLY HAVE THEIR STGPROCESSED COLUMNS UPDATED AFTER PROPAGATION COMPLETES

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

• Records in the STAGLOG table that have already been propagated
  by the StagingProp utility may not be marked as processed.
  
  This can occur if:
  - two or more concurrent transactions are writing to the STAGLOG
  table
  - the transaction that started the earliest writes to the
  STAGLOG table after one or more of the other transactions
  

Local fix

• To determine if any STAGLOG records exist that qualify for
  conditions listed in the "Problem Description" section, invoke
  this SELECT statement:
  
  SELECT A.STGRFNBR "TO_UPDATE", MIN(B.STGRFNBR) "TS_VALUE",
  A.STGTABLE "TABLE", A.STGOP "OPERATION"
  FROM STAGLOG A, STAGLOG B
  WHERE A.STGPROCESSED = B.STGPROCESSED
  AND A.STGPROCESSED = 0
  AND A.STGOP = B.STGOP
  AND A.STGTABLE = B.STGTABLE
  AND A.STGRFNBR > B.STGRFNBR
  AND A.STGSTMP < B.STGSTMP
  GROUP BY A.STGTABLE, A.STGRFNBR, A.STGOP
  ORDER BY A.STGTABLE, A.STGRFNBR, A.STGOP
  
  If any records are returned, the TO_UPDATE column values specify
  all STAGLOG table records whose STGSTMP column values require
  updating.
  To perform this update, invoke this UPDATE statement once for
  each "TABLE" and "OPERATION" column combination in the previous
  query:
  
  UPDATE STAGLOG
  SET STGSTMP = (SELECT STGSTMP FROM STAGLOG WHERE STGRFNBR =
  <value from TS_VALUE column>)
  WHERE STGRFNBR IN (<comma separated list of values from
  TO_UPDATE column>)
  AND STGTABLE = '<value from TABLE column>'
  AND STGOP = '<value from OPERATION column>'
  
  If the StagingProp utility has already been run and there exist
  propagated STAGLOG table records which haven't been marked as
  processed, invoke this UPDATE statement:
  UPDATE STAGLOG SET STGPROCESSED = 1 WHERE STGPROCESSED = 0 AND
  STGRFNBR IN (<comma separated list of STGRFNBR column values of
  propagated STAGLOG records>)
  

Problem summary

• USERS AFFECTED:
  WebSphere Commerce users on v7.0 who have installed APAR
  JR42158. Note that this APAR is included in FixPack 6.
  
  PROBLEM ABSTRACT:
  StagingProp may not mark some propagated STAGLOG records as
  processed due to database timestamp discrepancies
  
  BUSINESS IMPACT:
  StagingProp utility may not mark all propagated STAGLOG records
  as processed. Subsequent invocations of the StagingProp utility
  may attempt propagation of records that have already been
  propagated, which could potentially cause unique (primary) key
  or foreign key violations.
  
  RECOMMENDATION:
  Follow the local fix specified in the "Workaround" section or
  contact WebSphere Commerce Support for assistance
  

Problem conclusion

• Logic in the StagingProp utility assumed that values in both the
   STGRFNBR and STGSTMP would be increasing and uses values from
  these columns (among others) to select the set of records to
  mark as processed.
  
  In some databases, long-running transactions use timestamps from
  when query execution first begins. This can cause concurrent
  transactions that start later, to potentially insert records in
  the STAGLOG table that have lower STGRFNBR values but higher
  STGSTMP values, which violates the assumption previously stated.
  As a result, no records are selected which can then be marked as
  processed.
  
  To rectify, the utility no longer makes assumptions about
  monotonicity between the two columns.
  
  -------------------------------------------------------------
  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