EDIT: 2012/05/01 I changed the PL/SQL code below, as I found that creating a filter per column eventually exhausts the hardcoded limit on the number of filters you can have for a table (20). I changed it to do a count of records where each and every column’s before value matches each and every columns current value in the target. I am having issues with line length when I do this, so this may be updated again.
Out of the box, Golden Gate will not detect any conflicts in which the target data is different than what the source is prior to submission. If a row doesn’t exist that is sbumitted for update from the source, that will throw an ORA-01403. If you try to insert a row into the target for which the primary key value already exists in the target, and ORA-00001 will be thrown by the database, as you would expect.
However, assume you have a row in the source such as…
PRODUCT_ID 10
INVENTORY_QUANTITY 100
…that is then updated to…
PRODUCT_ID 10
INVENTORY_QUANTITY 90
…and the target has an update made directly to it like…
PRODUCT_ID 10
INVENTORY_QUANTITY 40
When the update to 90 is sent to the replicat on the target, GoldenGate will not throw any exceptions, and will update the INVENTORY_QUANTITY to 90.
Monday morning, the guys in the warehouse are trying to ship product that doesn’t exist in the inventory system. It might go something like, “hey, we are missing 50 units.” Ouch…
What is below will programmatically create filters for checking prior to update to ensure there wasn’t an update on the target about which we didn’t know. We didn’t want to implement all kinds of conflict handlers as we are in the midst of an ATG implementation, and don’t understand the data well enough to make those decisions in advance. This will simply trap an issue if it exists and abend. That is acceptable to us in the short run for two reasons:
What is below simply creates select of all columns other than the primary key where the value = what it was on the source before the update. If a matching “pre-version” row is not found on the target, an exception is thrown.
The beautiful thing about this is that GoldenGate does not execute the “filter” SQL for every update. It must be smart enough to check the incoming values and what it is in the target before updating. I know this because I ran a 10046 trace against the replicat process, and the select for the “filter” was only parsed, never executed during a test with thousands of updates. However, as soon as I forced in a conflicting update that was considered for processing by the replicat, the select filter was executed and the replicat process abended (because the check didn’t pass).
As such, you don’t have to worry about the overhead of the filter. I think that is what is meant by an extraction map, it just maintains it in memory.
SET serveroutput ON size unlimited
spool conflict.out
SET lines 1000 trims ON
SET echo off
SET feedback off
DECLARE
/*
Simple anonymous block that will read the dictionary for a given schema and output rules that will detect conflicts
if after an update all column values other than the primary key have changed since the update was issued on the
source.
*/
skip NUMBER := 0;
p_source VARCHAR2(30) := 'ATGDB_EXPRESS_CORE';
p_target VARCHAR2(30) := 'ATGDB_EXPRESS_CORE_REP';
TYPE t_columns IS TABLE OF VARCHAR2(30);
colns t_columns := t_columns();
TYPE t_keys IS TABLE OF VARCHAR2(30);
keys t_keys := t_keys();
PROCEDURE get_columns(p_table IN VARCHAR2) IS
i NUMBER := 0;
l_skip NUMBER := 0;
BEGIN
FOR col IN (SELECT column_name
FROM dba_cons_columns dcc
WHERE table_name = p_table
AND owner = p_source
AND EXISTS (SELECT 1
FROM dba_constraints dc
WHERE owner = p_source
AND dc.table_name = dcc.table_name
AND dc.constraint_name = dcc.constraint_name
AND dc.owner = dcc.owner
AND constraint_type = 'P')
ORDER BY position
) LOOP
keys.extend(1);
i := i + 1;
keys(i) := col.column_name;
END LOOP;
i := 0;
FOR col IN (SELECT column_name FROM dba_tab_columns WHERE table_name = p_table AND owner = p_source) LOOP
FOR j IN 1..keys.COUNT LOOP
IF keys(j) = col.column_name THEN
l_skip := 1;
EXIT;
END IF;
END LOOP;
IF l_skip = 0 THEN
colns.extend(1);
i := i + 1;
colns(i) := col.column_name;
END IF;
l_skip := 0;
END LOOP;
END;
BEGIN
FOR tab IN (SELECT table_name FROM dba_tables WHERE owner = p_source) LOOP
DBMS_OUTPUT.put_line('map ' || p_source || '.' || tab.table_name || ', target ' || p_target || '.' || tab.table_name || ', &');
DBMS_OUTPUT.put_line('sqlexec (id ' || tab.table_name || '_check, on update, beforefilter, & ');
get_columns(tab.table_name);
DBMS_OUTPUT.put('query "select count(*) cnt from ' || p_target || '.' || tab.table_name || ' where ');
FOR j IN 1..colns.COUNT LOOP
IF j = 1 THEN
DBMS_OUTPUT.put(colns(j) || ' = :p' || j);
ELSE
DBMS_OUTPUT.put(' and ' || colns(j) || ' = :p' || j);
END IF;
END LOOP;
FOR j IN 1..colns.COUNT LOOP
IF j = 1 THEN
DBMS_OUTPUT.put('", & ' || CHR(10) || 'params (p' || j || ' = ' || colns(j));
ELSE
DBMS_OUTPUT.put(',p' || j || ' = ' || colns(j));
END IF;
END LOOP;
DBMS_OUTPUT.put_line(')), &');
DBMS_OUTPUT.put_line('filter (on update, ' || tab.table_name || '_check.cnt > 0, raiseerror 9999);');
DBMS_OUTPUT.put_line(CHR(10));
keys.DELETE;
colns.DELETE;
END LOOP;
END;
/
2 comments for “Programmatically creating GoldenGate confliction detection filters”