How To Use Address to and Address From Features
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:
- /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 column in the table the target location (/AddressTo) to replicate the record to.
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));
content_copyActions defined on the channel:
| CHANNEL | LOCATION GROUP | LOCATION | TABLE | ACTION |
|---|---|---|---|---|
| chn1 | SRC | * | * | Capture |
| chn1 | SRC | * | * | Restrict /AddressTo={address_to} |
| chn1 | TGT | * | * | Integrate |
- Location group SRC has 1 location called ora.
- Location group TGT has 2 locations called targ1 and targ2.
- Source table has column called address_to.
Based on the content of the column address_to in table t_order, the changed row is replicated to either target location targ1 or targ2, to the target location group TGT or only to specific location(s) in the TGT group.
Test Case 1 – Replicate insert into targ2 only
If you do an insert into t_order like this:
SQL> insert into t_order values (1,1,'Tester','Boardwalk','targ2');
SQL> commit;
content_copyYou will 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.
content_copyOnly integrate with location targ2 picks up the change:
SQL> select * from kbresult2.t_order;
content_copy| ID | SUBID | NAME | STREET | ADDRESS_TO |
|---|---|---|---|---|
| 1 | 1 | Tester | Boardwalk | targ2 |
No rows in targ1, as expected:
SQL> select * from kbresult.t_order;
no rows selected
content_copyTestCase 2 – Replicate insert into targ1 only
If you insert values into t_order:
SQL> insert into t_order values (2,2,'Tester','Boardwalk','targ1');
SQL> commit;
content_copyYou will 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.
content_copyOnly integrate with location targ1 picks up the change:
SQL> select * from kbresult.t_order where id = 2;
content_copy| ID | SUBID | NAME | STREET | ADDRESS_TO |
|---|---|---|---|---|
| 2 | 2 | Tester | Boardwalk | targ1 |
No rows in targ2, as expected:
SQL> select * from kbresult2.t_order where id = 2;
no rows selected
content_copyTestCase 3 – Replicate insert into both targ1 & targ2
If you insert the following values into t_order:
SQL> insert into t_order values (3,3,'Tester','Boardwalk','targ1,targ2');
SQL> commit;
content_copyYou will 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.
content_copySo, integrate with locations targ1/targ2 both pick up the change:
Targ1
SQL> select * from kbresult.t_order where id = 3;
content_copy| ID | SUBID | NAME | STREET | ADDRESS_TO |
|---|---|---|---|---|
| 3 | 3 | Tester | Boardwalk | targ1,targ2 |
Targ2
SQL> select * from kbresult2.t_order where id = 3;
content_copy| ID | SUBID | NAME | STREET | ADDRESS_TO |
|---|---|---|---|---|
| 3 | 3 | Tester | Boardwalk | targ1,targ2 |
TestCase 4 – Replicate insert into group TGT (which has both targ1 & targ2 locations)
If you insert the following values into t_order:
SQL> insert into t_order values (4,4,'Tester','Boardwalk','TGT');
SQL> commit;
content_copyYou will see this in the channel output:
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.
content_copySo all locations in TGT picked up the changes.
Targ1
SQL> select * from kbresult.t_order where id = 4;
content_copy| ID | SUBID | NAME | STREET | ADDRESS_TO |
|---|---|---|---|---|
| 4 | 4 | Tester | Boardwalk | targ1,targ2 |
Targ2
SQL> select * from kbresult2.t_order where id = 3;
content_copy| ID | SUBID | NAME | STREET | ADDRESS_TO |
|---|---|---|---|---|
| 4 | 4 | Tester | Boardwalk | targ1,targ2 |
TestCase 5 - Replicate insert into all targets in channel chn1
Location group TGT1 and TGT2 were created, each having 1 location: TGT1 has targ1, and TGT2 has targ2.
If you insert the following values into t_order:
SQL> insert into t_order values (5,5,'Tester','Boardwalk','*');
SQL> commit;
content_copyYou will see this in the channel output:
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.
content_copyIn the above example, all locations in the channel, even if they are in different target location groups, picked up the changes.
Targ1
SQL> select * from kbresult.t_order where id = 5;
content_copy| ID | SUBID | NAME | STREET | ADDRESS_TO |
|---|---|---|---|---|
| 5 | 5 | Tester | Boardwalk | * |
Targ2
SQL> select * from kbresult2.t_order where id = 5;
content_copy| ID | SUBID | NAME | STREET | ADDRESS_TO |
|---|---|---|---|---|
| 5 | 5 | Tester | Boardwalk | * |
TestCase 6 – Replicate insert into group TGT but only to location targ1 (even though location targ2 is also a part of TGT)
If you insert the following values into t_order:
SQL> insert into t_order values (5,5,'Tester','Boardwalk','a');
SQL> commit;
content_copyNothing will be integrated:
Targ1
SQL> select * from kbresult.t_order where id = 5;
no rows selected
content_copyTarg2
SQL> select * from kbresult2.t_order where id = 5;
no rows selected
content_copyUnless you add action:
| CHANNEL | LOCATION GROUP | LOCATION | TABLE | ACTION |
|---|---|---|---|---|
| chn1 | TGT | * | targ1 | Restrict /AddressSubscribe=a |
As displayed in the HVR GUI below.
So, the values were integrated into the TGT group, location targ1, but not targ2.
This can be verified in the targets:
SQL> insert into t_order values (8,8,'Tester','Boardwalk','a');
1 row created.
SQL> commit;
Commit completed.
content_copyTarg1
Row got populated in targ1 as directed by the location specified.
SQL> select * from kbresult.t_order where id = 8;
content_copy| ID | SUBID | NAME | STREET | ADDRESS_TO |
|---|---|---|---|---|
| 8 | 6 | Tester | Boardwalk | a |
Targ2
Row did not get replicated to targ2 as specified.
SQL> select * from kbresult2.t_order where id = 8;
no rows selected
content_copyNow, you can see in the channel output:
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…
content_copy
Comments
0 comments
Please sign in to leave a comment.