Load Ingest Data
Results 1 to 1 of 1

Thread: Updating Primary Keys

  1. #1
    JeffInFlorida's Avatar
    JeffInFlorida is offline US Technical Team
    Join Date
    Oct 2006
    Posts
    95
    Rep Power
    13

    Updating Primary Keys

    When a change is encountered for a primary key and the Apply Policy is set to defensive then you can end up with duplicate rows in the target database.
    To be able to update primary keys on a target database you have to delete the existing primary key and then insert the new primary key. In order to get the old primary key you need to have the Before Image of the change.
    In order to get the before images perform the following steps:
    1) Run the Support.bat file that is in the CDC directory of the solution.
    2) Export the machine configuration using the following command:
    NAV_UTIL EXPORT MACHINE MACHINE.XML
    3) Edit the MACHINE.XML file and add the getBeforeImage attribute to the CDC_AGENT adapter config.
    <adapter name='CDC_AGENT' type='sqlCDCA' definition='CDC_AGENT'>
    <config datasource='BULK' logLevel='none' checkTimeoutEveryNEvents='100'
    realTime='true' retryInterval='2' lgrAutoConfig='true' getBeforeImage='true'>
    4) Import the configuration .
    NAV_UTIL IMPORT SYS MACHINE.XML
    5) Stop and start the CDC solution using the Management Console.

    Make the following changes to CDC Package for each table.
    1) Add a Transformation Script component between the Change Source and Change Destination components.
    2) Connect the component to the Change Source.
    3) Edit the script component and make the following changes:
    a. On the Input Columns check the operation column and change the Usage Type to ‘ReadWrite’.
    b. Edit the script and add the following code in the ‘Input0_ProcessInputRow’ function.
    if (Row.operation.Trim() == "BEFOREIMAGE")
    Row.operation = "DELETE";
    if (Row.operation.Trim() == "UPDATE")
    Row.operation = "INSERT";
    4) Save the scipt.
    5) Connect the Script Component to the Destination Component.
    Last edited by JeffInFlorida; 01-26-2011 at 10:04 AM.
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •