How To: Replicate only a limited set of data from a table with restrict condition and context

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

    How To:  Replicate only a limited set of data from a table with restrict condition and context

     

    Scenario:

    There are times you want to extract only a subset of rows from a table to replicate maybe only transactions for a specific year or a set of data where the id’s are great than or equal to a value

     

    Overview:

    In this example I have an orders table that I only want orders for a specific year (2017) sent to the target with an Oracle source & target.

    I also included some other Oracle/Oracle examples including

    • Refreshing only a specific set of rows of a table with a date type column based on a date range
    • Restrict action on ID column (number) for a range of values
    • Restrict action on ID column (number) greater than or equal to a value

    Finally I’ve included an example with a heterogeneous environment Oracle to SqlServer

     

    Some of these references may be helpful

          https://www.hvr-software.com/docs/Restrict

          https://www.hvr-software.com/docs/Hvrrefresh

     

    Steps:

    1. Refreshing target using a date field on the source to restrict to only a specific year.

     

    Define a /Restrict action on the table with the /RefreshCondition of where order_dt =  {hvr_var_year}. Note the curly braces will be the input variable that you specify when you perform the Refresh on the table as well the /Context = year_only

    Restrict

       /RefreshCondition="to_char(order_dt,'YYYY') = {hvr_var_year}" /Context=year_only

     

    2. Now perform a Refresh

     

    3.  Next first select the table and optionally create if it doesn’t exist on the target and if desired Always Recreate.

    Then select the Context tab next, and choose the context we defined above ‘year_only’ and input the parameter from the /Restrict clause ‘year’ -> ‘2017’ . Note the single quotes on both sides of the year value are needed too.

    Note the command shown at the bottom of the screen could be used in a script for automating without the GUI.

       hvrrefresh -gb -qno -cbkf -Cyear_only -Vmaxid='2017' -r sora -l otgt -t orders -h oracle context0/!{apoE9qG

     

    Then click on Context Tab and select ‘year_only’ for the Context you have defined

     

    4. Other few additional examples included for reference are

    a. Refreshing only a specific set of rows of a table (orders) with a date type column based on a date range

    • Restrict Action
       /RefreshCondition="order_dt between to_date({hvr_var_min_dt},'DD-MON-YYYY') and to_date({hvr_var_max_dt},'DD-MON-YYYY')" /Context=range_dt
    • Refresh
       hvrrefresh -gb -qno -cbkf -Crange_dt -Vmin_dt='11-APR-2019' -Vmax_dt='14-APR-2019' -r sora -l otgt -t orders -h oracle context0/!{apoE9qGZ}!@XE chan

     

    Then click on Context Tab and select ‘range_dt’ for the Context you have defined

     

    b. Restrict action on ID column (number) of the table context_tmp for a range of values

    • Restrict Action
          /RefreshCondition="id between {hvr_var_minid} and {hvr_var_maxid}" /Context=range
    • Refresh
          hvrrefresh -gb -qno -cbkf -Crange -Vminid=5 -Vmaxid=15 -r sora -l otgt -t context_tmp -h oracle context0/!{apoE9qGZ}!@XE chan

     

    Then click on Context Tab and select ’range’ for the Context you have defined

     

    c. Limit replication on ID column (number) of the table context_tmp with an ID greater than or equal to a value

    • Restrict Action
          /RefreshCondition="id >= {hvr_var_gtequal}" /Context=gt
    • Refresh
          hvrrefresh -gb -qno -cbkf -Cgt -Vminid=30 -r sora -l otgt -t context_tmp -h oracle context0/!{apoE9qGZ}!@XE chan

    Then click on Context Tab and select ‘gt’ for the Context you have defined

     

     

    5.  Example of Oracle to SqlServer that have different date formats and want to only copy orders table data that are between 2 order dates from the source to the heterogenous target. This requires 2 /Restrict actions, 1 on the Oracle source & the other on the Sqlserver target. It is best practice to specify same context name for source and target, but if you don’t make sure both contexts are selected in refresh screen on context tab.

    • Refresh Action
      • Restrict: Defined on table & source group for your channel (Oracle)
        /RefreshCondition="o_entry_dt between to_date({hvr_var_min_dt},'DD-MON-YYYY') and to_date({hvr_var_max_dt},'DD-MON-YYYY')" /Context=date_range
    • Restrict: Defined on table & target group for your channel (SqlServer)
        /RefreshCondition="cast(o_entry_d as date) between {hvr_var_min_dt} and {hvr_var_max_dt} " /Context=date_range
    • Refresh
        hvrrefresh -gb -qno -Cdate_range -Vmin_dt='01-APR-2019' -Vmax_dt='01-MAY-2019' -r ora -l mstgt -t orders -h oracle ora/!{yOg.inY1}! chn1

     

     

    /Restrict Action on the Oracle source table orders between two dates, date_range.

    /Restrict Action on the heterogenous SqlServer Target table orders between the same two dates, date_range.

    Refresh: Click on Context Tab and select ‘date_range’ for the Context you have defined and fill in the dates, ie ’01-APR-2019’ and ’01-MAY-2019’ for the variables

     

     

     

     

     

     

     

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

© 2020 HVR

Test drive Contact us