Overview:
We have an action called Restrict which specifies that only rows that satisfy a certain condition should be replicated. The restriction logic is enforced during capture and integration and also during compare and refresh.
Scenario:
Parameters being demonstrated here will be
/AddressTo : Captured changes should only be sent to integrate locations that match integrate address addr.
/AddressSubscribe : This integrate location should be sent a copy of any changes that match integrate address addr.
For this test we are going to specify in the address_to field in the table the target location (AddressTo) we want to record to go.
location group SRC has 1 location called ora
location group TGT has 2 locations , 1 called targ1 , the other called targ2
source table has column called address_to
Based on the content of the column address_to in table t_order, the changed row will go to either target location targ1 or targ2 or to whole target location group TGT
Steps:
Example of /Restrict /AddressTo
DDL for the source table
create table t_order ( id number primary key, subid number, name varchar2(15), street varchar2(15), address_to varchar(20));
with the defined channel actions;
chn1 SRC * * Capture
chn1 SRC * * Restrict /AddressTo={address_to}
chn1 TGT * * Integrate
as you see in the HVR GUI
Location group SRC has 1 location called ora
Location group TGT has 2 locations , 1 called targ1 , the other called targ2
Source table has column called address_to
Based on the content of the column address_to in table t_order, the changed row will go to either target location targ1 or targ2, to whole target location group TGT or only specific location(s) in the TGT group.
1. TestCase 1 – Replicate insert into targ2 only
If I do an insert into t_order like this;
SQL> insert into t_order values (1,1,'Tester','Boardwalk','targ2'); SQL> commit;
you’ll see this in the channel output;
2019-03-14T14:50:22+02:00: chn1-integ-targ2: Integrate cycle 4 for 1 transaction file (294 bytes).
2019-03-14T14:50:22+02:00: chn1-integ-targ2: Integrated 1 change from 2 seconds ago for ‘t_order’ (1 ins). This took 0.000001 seconds.
2019-03-14T14:50:22+02:00: chn1-integ-targ2: Integrate cycle used 1 transaction from 2 seconds ago and took 0.000001 seconds.
So above only integrate with location targ2 picks up the change, and verified in the database
Targ1 no rows as expected
SQL> select * from kbresult.t_order; no rows selected
Targ2 – expect to see the rows & we do
SQL> select * from kbresult2.t_order;
ID SUBID NAME STREET ADDRESS_
---------- ---------- --------------- --------------- --------
1 1 Tester Boardwalk targ2
2. TestCase 2 – Replicate insert into targ1 only
If I do insert into t_order like this;
SQL> insert into t_order values (2,2,'Tester','Boardwalk','targ1'); SQL> commit;
you’ll see this in the channel output;
2019-03-14T14:51:45+02:00: chn1-integ-targ1: Integrate cycle 4 for 1 transaction file (293 bytes).
2019-03-14T14:51:45+02:00: chn1-integ-targ1: Integrated 1 change from 1 second ago for ‘t_order’ (1 ins). This took 0.016 seconds.
2019-03-14T14:51:45+02:00: chn1-integ-targ1: Integrate cycle used 1 transaction from 1 second ago and took 0.016 seconds.
So above only integrate with location targ1 picks up the change, and in the database we verified the results
Targ1 the row is found
SQL> select * from kbresult.t_order where id = 2;
ID SUBID NAME STREET ADDRESS_
---------- ---------- --------------- --------------- --------
2 2 Tester Boardwalk targ1
Targ2 no rows as expected
SQL> select * from kbresult2.t_order where id = 2; no rows selected
3. TestCase 3 – Replicate insert into both targ1 & targ2
If I do an insert into t_order like this;
SQL> insert into t_order values (3,3,'Tester','Boardwalk','targ1,targ2'); SQL> commit;
you’ll see this in channel output;
2019-03-14T14:53:07+02:00: chn1-integ-targ2: Integrate cycle 5 for 1 transaction file (299 bytes).
2019-03-14T14:53:07+02:00: chn1-integ-targ2: Integrated 1 change from 2 seconds ago for ‘t_order’ (1 ins). This took 0.016 seconds.
2019-03-14T14:53:07+02:00: chn1-integ-targ2: Integrate cycle used 1 transaction from 2 seconds ago and took 0.016 seconds.
2019-03-14T14:53:07+02:00: chn1-integ-targ1: Integrate cycle 5 for 1 transaction file (299 bytes).
2019-03-14T14:53:07+02:00: chn1-integ-targ2: Waiting…
2019-03-14T14:53:07+02:00: chn1-integ-targ1: Integrated 1 change from 2 seconds ago for ‘t_order’ (1 ins). This took 0.016 seconds.
2019-03-14T14:53:07+02:00: chn1-integ-targ1: Integrate cycle used 1 transaction from 2 seconds ago and took 0.016 seconds.
So above integrate with locations targ1/targ2 both pick up the change and this is verified in the targets
Targ1 – expect to see the rows in both targets, targ1 & targ2
SQL> select * from kbresult.t_order where id = 3;
ID SUBID NAME STREET ADDRESS_TO
---------- ---------- --------------- --------------- --------------------
3 3 Tester Boardwalk targ1,targ2
Targ2
SQL> select * from kbresult2.t_order where id = 3;
ID SUBID NAME STREET ADDRESS_TO
---------- ---------- --------------- --------------- --------------------
3 3 Tester Boardwalk targ1,targ2
4. TestCase 4 – Replicate insert into group TGT (which has both targ1 & targ2 locations)
If I do an insert into t_order like this;
SQL> insert into t_order values (4,4,'Tester','Boardwalk','TGT'); SQL> commit;
you’ll see this in the channel.out file;
2019-03-14T14:58:54+02:00: chn1-integ-targ2: Integrate cycle 6 for 1 transaction file (292 bytes).
2019-03-14T14:58:54+02:00: chn1-integ-targ2: Integrated 1 change from 1 second ago for ‘t_order’ (1 ins). This took 0.015 seconds.
2019-03-14T14:58:54+02:00: chn1-integ-targ2: Integrate cycle used 1 transaction from 1 second ago and took 0.015 seconds.
2019-03-14T14:58:54+02:00: chn1-integ-targ1: Integrate cycle 6 for 1 transaction file (292 bytes).
2019-03-14T14:58:54+02:00: chn1-integ-targ2: Waiting…
2019-03-14T14:58:54+02:00: chn1-integ-targ1: Integrated 1 change from 1 second ago for ‘t_order’ (1 ins). This took 0.016 seconds.
2019-03-14T14:58:54+02:00: chn1-integ-targ1: Integrate cycle used 1 transaction from 1 second ago and took 0.016 seconds.
So in above example all locations part of TGT will pickup the changes.
Targ1 – expect to see the rows in both targets, targ1 & targ2
SQL> select * from kbresult.t_order where id = 4;
ID SUBID NAME STREET ADDRESS_TO
---------- ---------- --------------- --------------- --------------------
4 4 Tester Boardwalk targ1,targ2
Targ2
SQL> select * from kbresult2.t_order where id = 3;
ID SUBID NAME STREET ADDRESS_TO
--------- ---------- --------------- --------------- --------------------
4 4 Tester Boardwalk targ1,targ2
5. TestCase 5 – Replicate insert into all targets in channel chn1
in this case location group TGT1 and TGT2 have been created and each have 1 location : TGT1 has targ1 , and TGT2 has targ2
If I do the following insert into t_order;
SQL> insert into t_order values (5,5,'Tester','Boardwalk','*'); SQL> commit;
2021-12-03T15:55:00+01:00: chn1-integ-targ1: Integrate cycle 67 for 1 transaction file (230 bytes).
2021-12-03T15:55:00+01:00: chn1-integ-targ1: Integrated 1 insert for table 't_order' from 5.52 minutes ago took 0.0010 seconds.
2021-12-03T15:55:00+01:00: chn1-integ-targ1: Integrate cycle for 1 table of 1 change from 5.52 minutes ago took 0.008 seconds.
2021-12-03T15:55:00+01:00: chn1-integ-targ2: Integrate cycle 64 for 1 transaction file (230 bytes).
2021-12-03T14:55:00+00:00: chn1-integ-targ2: Integrated 1 change from 5.52 minutes ago for 't_order' (1 ins). This took 0.000001 seconds.
2021-12-03T14:55:00+00:00: chn1-integ-targ2: Integrate cycle used 1 transaction from 5.52 minutes ago and took 0.000001 seconds.
So in above example all locations part of chn - even if they are in different target location groups - will pick up the changes.
Targ1 – expect to see the rows in both targets, targ1 & targ2
SQL> select * from kbresult.t_order where id = 5;
ID SUBID NAME STREET ADDRESS_TO
---------- ---------- --------------- --------------- --------------------
5 5 Tester Boardwalk *
Targ2
SQL> select * from kbresult2.t_order where id = 5;
ID SUBID NAME STREET ADDRESS_TO
--------- ---------- --------------- --------------- --------------------
5 5 Tester Boardwalk *
6. TestCase 6 – Replicate insert into group TGT but only to location targ1 (even though location targ2 is also a part of TGT)
If I do the following inset into t_order;
SQL> insert into t_order values (5,5,'Tester','Boardwalk','a'); SQL> commit;
nothing will be integrated as we see here checking the database
Targ1
SQL> select * from kbresult.t_order where id = 5; no rows selected
Targ2
SQL> select * from kbresult2.t_order where id = 5; no rows selected
unless you add action
chn1 TGT * targ1 Restrict /AddressSubscribe=a
as you see in the HVR GUI below
So above integrate into the group TGT, location targ1 but not targ2.
And this is verified in the targets
SQL> insert into t_order values (8,8,'Tester','Boardwalk','a'); 1 row created.
SQL> commit; Commit completed.
Targ1 – Row does get populated in Targ1 as directed by the location specified
SQL> select * from kbresult.t_order where id = 8;
ID SUBID NAME STREET ADDRESS_TO
---------- ---------- --------------- --------------- --------------------
8 6 Tester Boardwalk a
Targ2 – Row does not get replicated targ2 as specified
SQL> select * from kbresult2.t_order where id = 8; no rows selected
Now you’ll see below in ch1.out;
2019-03-14T14:57:15+02:00: chn1-integ-orah: Integrate cycle 2 for 1 transaction file (293 bytes).
2019-03-14T14:57:15+02:00: chn1-integ-orah: Integrated 1 change from 2 seconds ago for ‘t_order’ (1 ins). This took 0.016 seconds.
2019-03-14T14:57:15+02:00: chn1-integ-orah: Integrate cycle used 1 transaction from 2 seconds ago and took 0.016 seconds.
2019-03-14T14:57:15+02:00: chn1-integ-orah: Waiting…