JR39613: CMVC 210000 - ALLOW STAGINGPROP TO CONTINUE TO PROCESS RECORDS IN THE EVENT OF A UNIQUE CONSTRAINT FAILURE

7.0.0-WS-WCServer-FP009

APAR status

• Closed as new function.

Error description

• Invocations of the StagingProp utility may terminate with error
  if a database error is encountered. Depending on circumstances
  and environment, some users may choose to tolerate certain types
  of errors.
  
  Continue-on-error semantics are being provided for this purpose.
  These semantics entail toleration of the following errors during
  the record propagation process of StagingProp:
  - Unique key constraint violations
  - Foreign key constraint violations
  
  These semantics are enabled for Oracle and DB2 (Linux, Unix and
  Windows) databases by the "-actionOnError" command-line option,
  followed by a value of 1.
  
  For invocations of the StagingProp utility where batch size has
  been specified and a JDBC batch is in use, the following
  behavior will take place:
  - On an Oracle database, all records in the batch will be left
  unprocessed in the STAGLOG table, i.e., value of "STGPROCESSED"
  column of failing records in STAGLOG table will remain 0
  - On a DB2 (Linux, Unix or Windows) database, only failing
  records in the batch will be left unprocessed in the STAGLOG
  table. For each failing record, a diagnostic similar to the one
  below will be output:
  redoBatchGranularly: Proceeding record generated a unique-key OR
  foreign-key violation. SQLSTATE was: <sqlstate-value>
  <stgrfnbr>: <udi-operation> into table <stgtable> -
  <uk-col-name[i]><uk-col-value[i]>, ... <scope>
  where:
  <sqlstate-value> will be either 23503 or 23505,
  <stgrfnbr> will be the value in the "STGRFNBR" column from the
  failing record
  <udi-operation> will be UPDATE, DELETE or INSERT based on
  whether the value in the "STGOP" column from the failing record
  is 'U', 'D', or 'I' respectively
  <stgtable> will be the value in the "STGTABLE" column from the
  failing record
  <uk-col-name[i]> will be the value in the "STGKEY[i]NAME" column
  from the failing record, and 1 <= i <= 5
  <uk-col-value[i]> will be the value in the "STGOKEY[i]" column
  from the failing record, and 1 <= i <= 5
  <scope> will be <scope merchant> or <scope site> based on
  whether the table being processed is a Merchant or Site table
  
  For invocations where batch size has not been specified or a
  JDBC batch is not in use, only the current record will fail and
  the next record will be processed.
  
  If continue-on-error semantics are specified and error handling
  takes place, the StagingProp utility will provided this
  diagnostic near the end of program output:
  Last STAGLOG record (last of a failing batch if batch was used)
  to generate error for which an action was specified was:
  <stgrfnbr>
  where:
  <stgrfnbr> is the value in the "STGRFNBR" column from the last
  record added to the batch
  
  The value provided by <stgrfnbr> can be used as a high watermark
  for records in the STAGLOG table for which any problem
  determination may be required.
  
  Upon completion of the propagation step, values in the
  STGPROCESSED column of the STAGLOG table may be updated as
  follows:
  - DELETE record that modified no rows will have an STGPROCESSED
  column value of -1
  - UPDATE record that modified no rows will have an STGPROCESSED
  column value of -2
  - INSERT record that modified no rows will have an STGPROCESSED
  column value of -3
  
  A usage example follows:
  <stagingprop-executable> [<other-options>] -actionOnError 1
  [<other-options>]
  
  Additionally, error toleration semantics are being provided for
  when errors are encountered during the consolidation process of
  the staging record propagation storage table, i.e., the STAGLOG
  table. These semantics are enabled by the
  "-tolerateConsolidationErrors" command-line option, followed by
  a value of "true".
  
  When consolidation error toleration semantics are enabled and
  errors of the form:
  
  (<timestamp>) <Error> The sequence of the following operations
  in STAGLOG table is invalid: operation <STGOP_1> followed by
  <STGOP_2>.
  <stgrfnbr-1>: <udi-operation-1> into table <stgtable-1> -
  <uk-col-name[i]-1><uk-col-value[i]-1>, ... <scope-1>
  <stgrfnbr-2>: <udi-operation-2> into table <stgtable-2> -
  <uk-col-name[i]-2><uk-col-value[i]-2>, ... <scope-2>
  where:
  <timestamp> will be the timestamp value of the time at which the
  error was logged
  <STGOP_1> and <STGOP_2> will be one of these combinations: I and
  I, U and I, D and U, or D and D
  
  are encountered, consolidation will continue and any erroneous
  records in the STAGLOG table will be adjusted to enable proper
  consolidation. This implies that the operation combinations
  described above will get executed in the following manner:
  - I, I: processed as one INSERT
  - U, I: processed as one UPDATE
  - D, U: processed as a DELETE, followed by an INSERT; this is to
  ensure that no data-loss occurs between this and any subsequent
  iterations of StagingProp
  - D, D: processed as one DELETE
  
  Upon completion of the consolidation step, each adjusted record
  will be marked with a value of -4 in the STGPROCESSED column of
  the STAGLOG table. Additionally, records modified during
  consolidation may populate values inside the STGRESERVED1 column
  of the STAGLOG table.
  
  A usage example follows:
  <stagingprop-executable> [<other-options>]
  -tolerateConsolidationErrors true [<other-options>]
  

Local fix

• Although tedious, unique and foreign key constraint violations
  may be resolved by careful examination of records in the STAGLOG
  table and the impact of their propagation from the Staging
  database to the Production database. However, due to the
  sensitive nature of data and referential-integrity that exists
  between tables in a WebSphere Commerce instance and the care
  required to resolve such errors, user intervention is not
  recommended. WebSphere Commerce support should be contacted.
  

Problem summary

• USERS AFFECTED:
  All WebSphere Commerce users making use of a staging
  environment.
  
  PROBLEM ABSTRACT:
  Enablement of Continue-On-Error Semantics for the StagingProp
  Utility
  
  BUSINESS IMPACT:
  To enable users with an understanding of the impact of unique
  key constraint and foreign key constraint violations to ignore
  any such violations encountered by the StagingProp utility.
  
  RECOMMENDATION:
  

Problem conclusion

• Continue-on-error semantics is provided for users who have an
  understanding of the impact of such constraint violations and
  choose to tolerate them.
  -------------------------------------------------------------
  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 DEV ED W

• Fixed component ID

  5724I3900

Applicable component levels

• R700 PSY

     UP