IBM Support

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

Direct link to fix

Subscribe

You can track all active APARs for this component.

 

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 number

    JR39613

  • Reported component name

    WC BUS DEV ED W

  • Reported component ID

    5724I3900

  • Reported release

    700

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    YesSpecatt / New Function

  • Submitted date

    2011-05-18

  • Closed date

    2011-10-31

  • Last modified date

    2011-10-31

  • 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

[{"Business Unit":{"code":"BU055","label":"Cognitive Applications"},"Product":{"code":"SSYT2H","label":"WebSphere Commerce Developer Enterprise"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"7.0","Line of Business":{"code":"LOB31","label":"WCE Watson Marketing and Commerce"}}]

Document Information

Modified date:
07 December 2021