Communities
A collaborative platform to connect and grow with like-minded Informaticans across the globe
Product Communities
Connect and collaborate with Informatica experts and champions
Discussions
Have a question? Start a Discussion and get immediate answers you are looking for
User Groups
Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
Get Started
Community Guidelines
Knowledge Center
Troubleshooting documents, product guides, how to videos, best practices, and more
Knowledge Base
One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
Support TV
Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
Documentation
Information library of the latest product documents
Velocity (Best Practices)
Best practices and use cases from the Implementation team
Learn
Rich resources to help you leverage full capabilities of our products
Trainings
Role-based training programs for the best ROI
Certifications
Get certified on Informatica products. Free, Foundation, or Professional
Product Learning Paths
Free and unlimited modules based on your expertise level and journey
Resources
Library of content to help you leverage the best of Informatica products
Tech Tuesdays Webinars
Most popular webinars on product architecture, best practices, and more
Product Availability Matrix
Product Availability Matrix statements of Informatica products
SupportFlash
Monthly support newsletter
Support Documents
Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
Product Lifecycle
End of Life statements of Informatica products
Ideas
Events
Change Request Tracking
Marketplace
English
  • Data Replication 9.8.0
  • All Products

Table of Contents

Search

  1. Preface
  2. Data Replication Overview
  3. Understanding Data Replication
  4. Sources - Preparation and Replication Considerations
  5. Targets - Preparation and Replication Considerations
  6. Starting the Server Manager
  7. Getting Started with the Data Replication Console
  8. Defining and Managing Server Manager Main Servers and Subservers
  9. Creating and Managing User Accounts
  10. Creating and Managing Connections
  11. Creating Replication Configurations
  12. Materializing Targets with InitialSync
  13. Scheduling and Running Replication Tasks
  14. Implementing Advanced Replication Topologies
  15. Monitoring Data Replication
  16. Managing Replication Configurations
  17. Handling Replication Environment Changes and Failures
  18. Troubleshooting
  19. Data Replication Files and Subdirectories
  20. Data Replication Runtime Parameters
  21. Command Line Parameters for Data Replication Components
  22. Updating Configurations in the Replication Configuration CLI
  23. DDL Statements for Manually Creating Recovery Tables
  24. Sample Scripts for Enabling or Disabling SQL Server Change Data Capture
  25. Glossary

User Guide

User Guide

Back Next

Configuring Conflict Resolution

Configuring Conflict Resolution

If you replicate change data to a target database on which other change data activity occurs, you can define conflict resolution rules for the target tables to handle conflicts that might occur during Apply processing.
Configure conflict resolution rules if you perform bidirectional replication or replication from multiple sources to a single target in SQL Apply mode. You cannot configure conflict resolution rules for mapped tables that are replicated in Merge Apply or Audit Apply mode.
  1. On the
    Map Tables
     tab, select the target table for which to define a conflict resolution rule.
  2. Click the
    Resolve Conflicts
     icon button above the source tables filter field, or right-click the mapped table on the target and select
    Resolve Conflicts
    .
    The
    Resolve Conflicts
     dialog box appears.
  3. To define a conflict resolution rule, click the
    New Conflict Resolution
     button to the right of the
    Resolve Conflicts
     field.
    A dialog box displays the default conflict resolution name New_Conflict_Resolution.
  4. In the
    Conflict resolution name
     field, edit the default conflict resolution rule name. Then click
    OK
    .
    Conflict resolution rule names can contain the digits 0-9, Latin letters A-Z and a-z, and the underscore (_) character. The Data Replication Console truncates conflict resolution rule names that are longer than 100 characters.
    The following image shows the
    Resolve Conflicts
     dialog box with the default conflict resolution name in the
    Resolve Conflicts
     field:
    Conflict Resolution dialog box
  5. In the
    Conflict
     list, select the conflict type for which you are defining the rule.
    The following table describes the types of conflicts that Data Replication handles:
    Conflict Type
    Description
    Delete
    Occurs when a row that is referenced in an SQL Delete operation does not exist in the target table.
    Uniqueness
    Occurs when the Applier applies change data that violates a uniqueness integrity constraint such as primary key or unique index.
    Update
    Occurs when the row that is referenced in an SQL Update operation has different values on the source and target. When you configure a replication job, you must select the columns for which Data Replication compares source and target values.
    For these columns, the Data Replication Console enables supplemental logging so that the Applier can get the before-image and after-image values to compare the source and target rows.
  6. In the
    Resolution
     list, select a resolution strategy.
    The following table describes the conflict resolution strategies that are available by conflict type:
    Conflict Resolution Strategy
    Conflict Types
    Description
    Custom
    All
    Calls a stored procedure that you previously defined to handle conflicts. You can define a stored procedure by using commands that the SQL Script Engine provides. When an SQL operation on the target table causes conflict, the Applier calls the stored procedure and passes operation metadata and the before and after values for all of the columns for which supplemental logging is enabled as procedure parameters.
    These parameters are:
    • OP_XID.
       Transaction ID.
    • OP_CODE.
       A code for the operation type, which can be D for deletes, I for inserts, and U for updates.
    • OP_TIME.
       The date and time of the operation.
    • OP_CMT_SCN.
       The SCN value for the operation commit.
    • OP_CMT_TIME.
       The commit time for the operation.
    • OP_NUM_IN_TX.
       The sequence number of the operation in the transaction.
    • column_name
      _OLD,
      column_name
      _NEW.
       The before and after values for the column <
      column_name
      >. These parameters repeat for each column for which you enabled additional logging.
    For more information about the SQL Script Engine commands, see the
    Informatica Data Replication Scripting Guide
    .
    Discard
    All
    Discards the source values and retains the existing values in the target table.
    Maximum, Minimum
    Update
    For these resolution strategies, you must select a resolution column. For conflicting rows, Data Replication compares source and target values in the resolution column.
    • For the Maximum strategy, if the target value is greater than the source value, Data Replication uses the Discard strategy. Otherwise, Data Replication uses the Overwrite strategy.
    • For the Minimum strategy, if the target value is less than the source value, Data Replication uses the Discard strategy. Otherwise, Data Replication uses the Overwrite strategy.
    Overwrite
    • Update
    • Uniqueness
    Overwrites records in the target table with the values from the source table.
  7. For the UPDATE conflict type, in the
    Resolve Conflicts for Columns
     box, select the columns for which the Applier compares the source and target values to detect conflicts.
    To select or clear all of the columns, click
    Select All
     or
    Clear All
     on the left side of the dialog box.
    The Data Replication Console enables supplemental logging for the selected columns.
  8. For the CUSTOM resolution strategy, create a stored procedure that the Applier uses to handle conflict resolution operations:
    1. In the
      Supplemental Logging Columns
       box, select the columns for which to enable supplemental logging and make the before and after images available to the stored procedure.
      To select or clear all of the columns for supplemental logging, click
      Select All
       or
      Clear All
       on the right side of the dialog box.
    2. Click
      Create Procedure
      . Then enter the procedure name and click
      OK
      .
      Use the following naming pattern for procedure names:
      • For DB2 and Oracle targets, use
        "
        schema_name
        "."
        procedure_name
        ".
      • For Microsoft SQL Server targets, use
        owner_name
        .
        procedure_name
        .
      For Oracle targets, the procedure name cannot exceed 30 characters. For
      DB2 and
      Microsoft SQL Server targets, the procedure name cannot exceed 128 characters.
    3. In the
      Generate Procedure
       dialog box, create the stored procedure.
      The Data Replication Console provides a stored procedure template for the selected conflict type. Add information in the procedure body.
    4. Click
      Execute
       to run the generated procedure script immediately, or click
      Save
       to save the procedure to a file on the file system so that you can run the procedure later manually.
  9. For the MAXIMUM and MINIMUM resolution strategies, select a resolution column in the
    Resolution column
     list.
  10. To enable logging for conflicts of the specified type, select
    Use logging
    .
    The Applier logs information about these conflicts to the log file that is specified in the
    Conflict Logging
     box on the
    Runtime Settings
     tab >
    General
     view.
  11. Click
    OK
     to save the rule.
  12. To handle Update conflicts for Microsoft SQL Server 2008 R2 sources, verify that the extract.mssql.process_updates_as_updates parameter is set to the default setting of 1 on the
    Runtime Settings
     tab >
    Advanced Settings
     view.
0 COMMENTS
We’d like to hear from you!