1 Reply Latest reply: Jan 27, 2013 6:04 PM by stevencallan RSS

    Resolveconflict and updaterowsexists

      Hi experts.

      Using 11.2 GG un-directional setup.

      I'm testing out the new cdr functionality using comparecols and resolveconflict.

      The business rules are as follows:

      If updating a table's row in source with a value, and that value already has been updated in target manually, the replicat should abend.
      E.g. :
      source: update emp set salary = 100 where emp_id = 10;
      If that emp_id already has been updated to 100 in the target processing should stop.

      As I can see from the documentation for resolveconflict there are options to use "usemax, usemin, usedelta, discard, overwrite" or "ignore" on UPDATEROWSEXISTS.
      But my goals is to stop all processing by actually abending the replicat.

      Please give some advice on how this can be accomplished - if possible.

        • 1. Re: Resolveconflict and updaterowsexists
          You could use SQL to do a lookup/comparison, then raise an error, and specify an action for that error (ABEND).

          Example from the Apress GoldenGate book:
          REPERROR (90000, DISCARD), &
          SQLEXEC (ID checkemployee, ON UPDATE, &
          QUERY "select count(*) empduplicate from HR.EMPLOYEES where employee_id = ? and &
          employee_timestamp > ?", &
          PARAMS (p1 = employee_id, p2 = employee_timestamp), BEFOREFILTER, ERROR REPORT, &
          TRACE ALL),&
          FILTER (checkemployee.empduplicate = 0, ON UPDATE, RAISEERROR 90000);
          There are other things going here than what you are looking for, but the idea is that you can raise your own exception and specify what to do when it happens.

          You could also make a ghost/copy table and convert the original update into an insert, and if there is a collision on the insert (because the value already exists), let GoldenGate abend on its own. Of course, you have the overhead of a duplicate table and having to code the test, but that's not much different than having to do a lookup on the target in the first place.

          Then again, if it can't happen on the target, why was it allowed to happen on the source?