How To: Replicate only a limited set of data from a table with restrict condition using 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:
Refreshing target using a date field on the source to restrict to only a specific year.
Step 1] 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
Step 2] Now perform a Refresh
Step 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
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
