CDR Example 3: UPDATEROWEXISTS with USEDELTA, USEMAX, and IGNORE

This example resolves the conflict where a target row exists on UPDATE but non-key columns are different, and it uses three different resolution types to handle this condition based on the affected column.

10.5.1 Table Used in this Example

The examples assume identical Oracle databases.

CREATE TABLE tgt(

name varchar2(30) primary key, phone varchar2(10),

address varchar2(100), salary number,

balance number, comment varchar2(100), last_mod_time timestamp);

At the source database, all columns are supplementally logged:

ADD TRANDATA scott.src, COLS (name, phone, address, salary, balance, comment, last_mod_time);

Initial UPDATE applied by Replicat that detects the conflict

This SQL returns a no-data-found error because the values for the salary and last_mod_time are different. (The values for comment and balance are also different, but these columns are not compared.)

UPDATE applied by Replicat to resolve the conflict for salary, using USEDELTA.

SQL bind variables:

1)200 2)100 3)'Mary'

Per USEDELTA, the difference between the after image of salary (200) in the trail and the before image of salary (100) in the trail is added to the current value of salary in the target (600). The result is 700.

600 + (200 - 100) = 700 UPDATE applied by Replicat

to resolve the conflict for the default columns, using

Per USEMAX, because the after value of last_mod_time in the trail record is greater than the current value in the database, the row is updated with the after values from the trail record.

Note that the salary column is not set here, because it is resolved with the UPDATE from the USEDELTA resolution.

Table 10–6 (Cont.) UPDATEROWEXISTS with USEDELTA and USEMAX

Image SQL Comments

10.5.2 MAP Statement

MAP fin.src, TARGET fin.tgt, COMPARECOLS

(ON UPDATE ALLEXCLUDING (comment)), RESOLVECONFLICT (

UPDATEROWEXISTS,

(delta_res_method, USEDELTA, COLS (salary, balance)),

(max_res_method, USEMAX (last_mod_time), COLS (address, last_mod_time)), (DEFAULT, IGNORE));

10.5.3 Description of MAP Statement

■ For an UPDATEROWEXISTS conflict, where a target row exists on UPDATE but non-key columns are different, use two different resolutions depending on the column:

– Per the delta_res_method resolution, use the USEDELTA resolution logic for the salary and balance columns so that the change in each value will be added to the current value of each column.

– Per the max_res_method resolution, use the USEMAX resolution logic for the address and last_mod_time columns. The last_mod_time column is the resolution column. This column is updated with the current time whenever the row is modified; the value of this column in the trail is compared to the value in the target. If the value of last_mod_time in the trail record is greater than the current value of last_mod_time in the target database, the changes to address and last_mod_time are applied to the target; otherwise, they are ignored in favor of the target values.

– Per DEFAULT, use the IGNORE resolution logic for the remaining columns (phone and comment) in the table (the default column group). Changes to these columns will always be ignored by Replicat.

■ Per COMPARECOLS, use all columns except the comment column as the comparison columns for conflict detection for UPDATE operations. Comment will not be used in the WHERE clause for updates, but all other columns that have a before image in the trail record will be used.

10.5.4 Error Handling

For an example of error handling to an exceptions table, see Section 10.2.3,

"Configuring the Oracle GoldenGate Parameter Files for Error Handling".

Note: As an alternative to USEMAX, you can use the USEMAXEQ

resolution to apply a >= condition. For more information, see Reference for Oracle GoldenGate for Windows and UNIX.

Table 10–7 UPDATEROWEXISTS with USEDELTA, USEMAX, and IGNORE

Image SQL Comments

Before image in trail name='Mary' phone='1234567890'

last_mod_time='9/1/10 3:00 is the before image of the resolution column for the USEMAX resolution.

salary=100 and balance=100 are the before images for the USEDELTA resolution.

After image in trail phone='222222' address='Holly' salary=200 comment='new'

last_mod_time='9/1/10 5:00'

last_mod_time='9/1/10 5:00 is the after image of the resolution column for USEMAX. Since there is an after image, this will be used to determine the resolution.

salary=200 is the only after image available for the USEDELTA resolution.

For balance, the before image will be used in the calculation.

last_mod_time='9/1/10 4:00 is the current image of the resolution column in the target against which the

resolution column value in the trail is compared for USEMAX.

salary=600 and balance=600 are the current images of the target columns for USEDELTA.

Initial UPDATE applied by Replicat that detects the conflict

This SQL returns a no-data-found error because the values for the address, salary, balance and last_mod_time columns are different.

UPDATE applied by Replicat to resolve the conflict for salary, using USEDELTA.

SQL bind variables:

1)200 2)100 3)'Mary'

For salary, there is a difference of 100, but there was no change in value for balance, so it is not needed in the update SQL. Per USEDELTA, the

difference (delta) between the after (200) image and the before image (100) of salary in the trail is added to the current value of salary in the target (600). The result is 700.

UPDATE applied by Replicat to resolve the conflict for USEMAX.

SQL bind variables:

1)'Holly' 2)'9/1/10 5:00' 3)'Mary' 4)'9/1/10 5:00'

Because the after value of last_mod_

time in the trail record is greater than the current value in the database, that column plus the address column are updated with the after values from the trail record.

Note that the salary column is not set here, because it is resolved with the UPDATE from the USEDELTA resolution.

UPDATE applied by Replicat for IGNORE.

SQL bind variables:

1)'222222' 2)'new' 3)'Mary'

IGNORE is specified for the DEFAULT column group (phone and comment), so no resolution SQL is applied.

Table 10–7 (Cont.) UPDATEROWEXISTS with USEDELTA, USEMAX, and IGNORE

Image SQL Comments