How To:  Easy to follow example of using the address to and address from feature to help define a channel using multiple integrate jobs.

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #18466
    Donna Zehl
    Keymaster

    How To:  Easy to follow example of using the address to and address from feature to help define a channel using multiple integrate jobs.

     

     

    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 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…

     

Viewing 1 post (of 1 total)
  • The forum ‘How to’ is closed to new topics and replies.

© 2020 HVR

Test drive Contact us