How to Use AddressTo and AddressFrom Features
Question
How can I use HVR's AddressTo and AddressFrom features.
Environment
HVR 5
Answer
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.
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));
Actions 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;
You 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.
Only integrate with location targ2 picks up the change:
SQL> select * from kbresult2.t_order;
| 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
TestCase 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;
You 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.
Only integrate with location targ1 picks up the change:
SQL> select * from kbresult.t_order where id = 2;
| 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
TestCase 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;
You 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.
So, integrate with locations targ1/targ2 both pick up the change:
Targ1
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 |
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;
You 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.
So all locations in TGT picked up the changes.
Targ1
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 |
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;
You 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.
In 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;
| 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 | * |
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;
Nothing will be integrated:
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:
| 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.
Targ1
Row got 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 did not get replicated to targ2 as specified.
SQL> select * from kbresult2.t_order where id = 8;
no rows selected
Now, 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…