Restrict

Action Restrict defines 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

This section describes the parameters available for action Restrict.

Following are the two tabs/ways, which you can use for defining action parameters in this dialog:

Regular: Allows you to define the required parameters by using the UI elements like checkbox and text field.
Text: Allows you to define the required parameters by specifying them in the text field. You can also copy-paste the action definitions from Fivetran HVR documentation, emails, or demo notes.

Parameter	Argument	Description
CaptureCondition	sql_expr	During Capture, only rows where the condition sql_expr is TRUE are captured. The SQL expression sql_expr can contain the following substitutions: {colname} is replaced with the value of current table's column colname. For Db2 for z/OS, you must cast the column values to the exact data type. For more information, see Column Substitution. {hvr_cap_loc} is replaced with the location name. {hvr_cap_tstamp} is replaced with the moment (time) that the change occurred in source location. {hvr_cap_user} is replaced with the name of the user who made the change. A subselect can be supplied. For example exists (select 1 from lookup where id={id}). The capture condition is embedded inside the trigger–based capture procedures. The trigger-based capture method (Capture_Method=DB_TRIGGER) has been deprecated since 6.2.0/0. This parameter does 'update conversion'. Update conversion is when (for example) an update changes a row which did satisfy a condition and makes it into a row that does not satisfy the condition; such an update would be converted to a delete. If however the update changes the row from not satisfying the condition to satisfying it, then the update is converted to an insert. Parameter IgnoreCondition in action Capture has a similar effect to this parameter but does not do update conversion.
IntegrateCondition	sql_expr	During Integrate, only rows where the condition sql_expr is TRUE are integrated. The SQL expression sql_expr can contain the following substitutions: {colname} is replaced with the value of current table's column colname. For Db2 for z/OS, you must cast the column values to the exact data type. For more information, see Column Substitution. {hvr_cap_loc} is replaced with the location where the capture was changed. {hvr_cap_tstamp} is replaced with the moment (time) that the change occurred in source location. {hvr_cap_user} is replaced with the name of the user who made the change. A subselect can be supplied. For example exists (select 1 from lookup where id={id}). This parameter is supported only for database locations. For Burst Integrate, this parameter acts as a filter that is applied on the integration. Note that if in a single integrate cycle a row was inserted and subsequently deleted then the row will not be integrated with BURST defined, regardless of the filter. For Continuous Integrate, this parameter performs 'update conversion.' Update conversion occurs when. For example, an update changes a row that previously satisfied a condition, making it into a row that no longer satisfies the condition. In such cases, the update is converted to a delete. However, if the update changes the row from not satisfying the condition to satisfying it, then the update is converted to an insert. If you are filtering a large number of rows, you may observe a spike in MAR. To address this situation, you can instead use CaptureCondition or AddressTo/AddressSubscribe.
RefreshCondition	sql_expr	During hvrefresh, only rows where the condition sql_expr evaluates as TRUE are refreshed. If parameter CompareCondition is not defined then during compare this parameter also affects which rows are compared. This parameter should not be defined with parameter SliceCondition. This parameter is supported only for database locations or for File locations with Hive External Tables. For Refresh, the effect of this parameter depends on whether it is defined on the source or on the target side. If defined on the source side, it affects which rows are selected for refreshing (*select from*sourcewhere*condition). If defined on the target side, during bulk refresh it protects non–matching rows from bulk delete (delete fromtargetwherecondition, instead of just truncatetarget). If defined for row–wise refresh, it prevents some rows from being selected for comparison with the source rows (*select from*targetwhere*condition). The SQL expression sql_expr can contain the following substitutions: {colname} is replaced with the value of current table's column colname. For Db2 for z/OS, you must cast the column values to the exact data type. For more information, see Column Substitution. {hvr_var_xxx} is replaced with value of 'context variable' xxx. The value of a context variable can be supplied using option –Vxxx=val to command hvrrefresh or hvrcompare. {hvr_local_loc} is replaced with the current location name. {hvr_schema} is replaced with the schema name of the table. This is only allowed if the channel is defined with the tables. This can only be used when action TableProperties with parameter Schema=my_schema is explicitly defined for these tables on the target file location. {hvr_tbl_base_name} is replaced with the base name of the current table. {hvr_opposite_loc} on the source database is replaced with the target location name and on the target database it is replaced with the source location name. This feature allows compare and refresh to be made aware of horizontal partitioning.
CompareCondition	sql_expr	During hvrcompare, only rows where the condition sql_expr evaluates as TRUE are compared. Only these rows are selected for comparison (it can be defined on both databases or just on one). If this parameter is not defined but parameter RefreshCondition is defined then hvrcompare will use RefreshCondition for comparing. Restricting compare based on a non-key column can lead to inaccurate results if an update to the restricted column has not yet been replicated to the target location before compare starts, or if the restricted column is updated while an online compare is running. This parameter should not be defined with parameter SliceCondition. The SQL expression can contain substitutions: {colname} is replaced with the value of current table's column colname. For Db2 for z/OS, you must cast the column values to the exact data type. For more information, see Column Substitution. {hvr_var_xxx} is replaced with value of 'context variable' xxx. The value of a context variable can be supplied using option –Vxxx=val to command hvrrefresh or hvrcompare. {hvr_local_loc} is replaced with the current location name. {hvr_opposite_loc} on the source database is replaced with the target location name and on the target database it is replaced with the source location name. This feature allows compare to be made aware of horizontal partitioning. {hvr_schema} is replaced with the schema name of the table. This is only allowed if the channel is defined with the tables. This can only be used when action TableProperties with parameter Schema=my_schema is explicitly defined for these tables on the target file location. {hvr_tbl_base_name} is replaced with the base name of the current table. {hvr_integ_seq}, {hvr_tx_seq}, {hvr_tx_scn} are replaced with values corresponding to SCN of the Oracle 'flashback moment' on the Oracle source database. These substitutions are only available on the target when option Select Moment (option -M) is supplied to hvrrefresh. For example, if the target is defined with action ColumnProperties (parameters Name=tkey and TimeKey and IntegrateExpression="{hvr_integ_seq}") and action Restrict (parameter CompareCondition="tkey<= {hvr_integ_seq}") then an "on-line compare" can be done by supplying a Select Moment (option -M) with time or SCN older than the current latency.
SliceCountCondition	sql_expr	During sliced (option -S) hvrrefresh or hvrcompare, only rows where the condition sql_expr evaluates as TRUE are affected. This parameter is allowed and required only for the Count (option num) type of slicing. When using this parameter, it must be defined on both source and target locations. It can be defined using single or multiple action definition. When the syntax/sql expression is same on source and target location, single action definition can be used, else multiple action definition is required. The effect of this parameter depends on whether it is defined on the source or on the target side. If defined on the source side, it affects which rows are selected for refreshing or comparing (*select from*sourcewhere*condition). If defined on the target side, during bulk refresh it protects non–matching rows from bulk delete (delete fromtargetwherecondition, instead of just truncatetarget). during row–wise refresh it prevents some rows from being selected for comparison with the source rows (*select from*targetwhere*condition). during compare it affects which rows are selected (*select from*sourcewhere*condition). The SQL expression sql_expr can contain the following substitutions: {hvr_slice_num} contains current slice number (starting from 0) if slicing is defined with a Count (option -Snum). {hvr_slice_total} contains total number of slices if slicing is defined with a Count (option -Snum). For example, see hvrrefresh or hvrcompare option -S. It is recommended to define parameter Context when using these substitutions so it can be easily disabled or enabled.
SliceSeriesCondition	sql_expr	During sliced (option -S) hvrrefresh or hvrcompare, only rows where the condition sql_expr evaluates as TRUE are affected. This parameter is allowed and required only for the Series (option val1[;val2]...) type of slicing. When using this parameter, it must be defined on both source and target locations. It can be defined using single or multiple action definition. When the syntax/sql expression is same on source and target location, single action definition can be used, else multiple action definition is required. The effect of this parameter depends on whether it is defined on the source or on the target side. If defined on the source side, it affects which rows are selected for refreshing or comparing (*select from*sourcewhere*condition). If defined on the target side, during bulk refresh it protects non–matching rows from bulk delete (delete fromtargetwherecondition, instead of just truncatetarget). during row–wise refresh it prevents some rows from being selected for comparison with the source rows (*select from*targetwhere*condition). during compare it affects which rows are selected (*select from*sourcewhere*condition). The SQL expression sql_expr can contain the following substitution: {hvr_slice_value} contains current slice value if slicing is defined with a Series of values (option -Sv1[;v2]...[;vN]). For example, see hvrrefresh or hvrcompare option -S. It is recommended to define parameter Context when using these substitutions so it can be easily disabled or enabled.
HorizColumn	col_name	Horizontal partitioning column. The contents of the column of the replicated table is used to determine the integrate address. If parameter HorizLookupTable is also defined then the capture will join using this column to that table. If it is not defined then the column's value will be used directly as an integrate address. An integrate address can be one of the following: An integrate location name. For example, dec01. A location group name containing integrate locations. For example, DECEN. An alias for an integrate location, defined with AddressSubscribe. For example, 22. A pattern to match one of the above. For example, dec. A list of the above, separated by a semicolon, colon, or comma. For example, dec01,22. This parameter must be defined for trigger-based capture (Capture_Method=DB_TRIGGER). When used with trigger–based capture, this parameter does 'update conversion'. Update conversion is when (for example) an update changes a row which did satisfy a condition and makes it into a row that does not satisfy the condition; such an update would be converted to a delete. If however the update changes the row from not satisfying the condition to satisfying it, then the update is converted to an insert. No update conversion is done if this parameter is used with log–based capture. The trigger-based capture method (Capture_Method=DB_TRIGGER*) has been deprecated since 6.2.0/0.
HorizLookupTable	tbl_name	Lookup table for value in column specified by parameter HorizColumn. The lookup table should have a column which has the name of the HorizColumn parameter. It should also have a column named hvr_address. The capture logic selects rows from the lookup table and for each row found stores the change (along with the corresponding hvr_address) into the capture table. If no rows match then no capture is done. And if multiple rows match then the row is captured multiple times (for different destination addresses). This parameter is supported only for trigger-based capture (Capture_Method=DB_TRIGGER). The trigger-based capture method (Capture_Method=DB_TRIGGER) has been deprecated since 6.2.0/0. A possible alternative for log–based capture channels is to define parameters AddressTo and AddressSubscribe.
DynamicHorizLookup		Dynamic replication of changes to lookup table. Normally only changes to the horizontally partitioned table are replicated. This parameter causes changes to the lookup table to also trigger capture. This is done by creating extra rules/triggers that fire when the lookup table is changed. These rules/triggers are name tbl__li, tbl__lu, tbl__ld. The trigger-based capture method (Capture_Method=DB_TRIGGER) has been deprecated since 6.2.0/0. Changes are replicated in their actual order, so for example if a transaction inserts a row to a lookup table and then a matching row to the main replicated table, then perhaps the lookup table's insert would not cause replication because it has no match (yet). But the other insert would trigger replication (because it now matches the lookup table row). This dynamic lookup table replication feature is suitable if the lookup table is dynamic and there are relatively few rows of the partitioned replicated table for each row of the lookup table. But if for example a huge table is partitioned into a few sections which each correspond to a row of a tiny lookup table then this dynamic feature could be expensive because an update of one row of the lookup table could mean millions of rows being inserted into the capture table. A more efficient alternative could be to perform an hvrrefresh whenever the lookup table is changed and use parameter RefreshCondition with pattern {hvr_opposite_loc} in the condition so that the refresh is aware of the partitioning. This parameter is supported only for trigger-based capture (Capture_Method=DB_TRIGGER). The trigger-based capture method (Capture_Method=DB_TRIGGER) has been deprecated since 6.2.0/0. A possible alternative for log–based capture channels is to define parameters AddressTo and AddressSubscribe.
AddressTo	addr	Captured changes should only be sent to integrate locations that match integrate address addr. The address can be one of the following: An integrate location name. For example, dec01. A location group name containing integrate locations. For example, DECEN. An alias for an integrate location, defined with parameter AddressSubscribe. For example 22 or Alias7. A pattern to match one of the above. For example, dec. A column name enclosed in braces like {column_name}. The contents of this column will be used as an integrate address. This is similar to parameter /HorizColumn. A column name enclosed in braces with %[literal]* argument like {column_name%[literal]}. The exact contents of this column will be used as an integrate address without parsing it into a pattern since the %[literal] is used. `Since v6.1.5/5` A list of the above, separated by a semicolon, colon, or comma. For example, dec01,{col3}. This parameter should be defined with Capture. This parameter does not do 'update conversion'.
AddressSubscribe	addr	This integrate location should be sent a copy of any changes that match integrate address addr. The address can be one of the following: A different integrate location name. For example, dec01. A location group name containing other integrate locations. For example, DECEN. A pattern to match one of the above. For example, dec. An alias to match an integrate address defined with parameter AddressTo* or HorizColumn or matched by {hvr_address} in parameter Pattern of action Capture. An alias can contain numbers, letters, and underscores. For example, 22 or Alias7. A list of the above, separated by a semicolon, colon, or comma. For example, dec, CEN. This parameter should be defined with Integrate*.
SelectDistinct		Filter/ignore duplicate records by performing select distinct instead of select during Hvrrefresh or Hvrcompare. This helps to avoid fatal errors caused by duplicate records during Hvrcompare (applicable only to S3 or HDFS with Hive external tables and failover). This parameter should be enabled only if duplicate records are not relevant.
Context	context	Ignore action unless hvrrefresh or hvrcomparecontext is enabled. The value should be the name of a context (a lowercase identifier). It can also have form !context, which means that the action is effective unless context is enabled. One or more contexts can be enabled for hvrcompare or hvrrefresh (on the command line with option -Ccontext). Defining an action which is only effective when a context is enabled can have different uses. For example, if action Restrict with parameter RefreshCondition="{id}>22" and Context=qqq is defined, then normally all data will be compared, but if context qqq is enabled (-Cqqq), then only rows where id>22 will be compared. Variables can also be used in the restrict condition. For example, "{id}>{hvr_var_min}". This means that hvrcompare -Cqqq -Vmin=99 will compare only rows with id>99.

Horizontal Partitioning

Horizontal partitioning means that different parts of a table should be replicated into different directions. Logic is added inside capture to calculate the destination address for each change, based on the row's column values. The destination is put in a special column of the capture table named hvr_address. Normally during routing each capture change is sent to all other locations which have a Integrate action defined for that row, but this hvr_address column overrides this. The change is sent instead to only the destinations specified.

Column hvr_address can contain a location name (lowercase), a location group name (UPPERCASE) or an asterisk (*). An asterisk means send to all locations with Integrate defined. It can also contain a comma-separated list of the above.

Column Substitution

For DB2 for z/OS, when using the column substitution ({colname}) you must cast the column values to the exact data type, especially for string types like Char, Varchar, and CLOB.

For example, if the column (e.g., column1) has a type varchar(10), using the substitution {column1}='ABC' may result in the F_JD0274 error during capture. The correct substitution that you need to use is {column1}=varchar('ABC', 10). This ensures the expected behavior without encountering errors.

Examples

This section describes examples of using the following parameters of Restrict:

CaptureCondition and RefreshCondition
AddressTo and AddressSubscribe

Example 1: Using CaptureCondition and RefreshCondition

To replicate only rows of table product having id between 1000000 and 2000000, use parameters CaptureCondition and RefreshCondition.

SC-Hvr-Actions-Restrict_CaptureCondition_RefreshCondition.png

Also, only rows of table order for products which are in state 16 need to be captured. This is implemented with another CaptureCondition parameter.

Example 2: Using AddressTo and AddressSubscribe

This section describes the examples of using action Restrict with parameters AddressTo and AddressSubscribe.

The following replication configuration is used:

location group SOURCE having 1 source location src
location group TARGET having 2 target locations tgt1 and tgt2

Create table (order) on the source location:

create table order (
id number primary key,
subid number,
name varchar2(15),
street varchar2(15),
address_to varchar(20));

The address_to column will serve as a field for enforcing the restriction logic during replication, i.e. captured changes will be replicated to one of the target locations based on the values inserted in this column.

Scenario 1

This example requires changes captured from location src to be replicated only to location tgt1. In this case, the integrate address is restricted by the content of the AddressTo parameter set to the {address_to} column defined on the source location src as shown in the screenshot below.

When value 'tgt1' is inserted in the address_to column on the source location, the change should be replicated only to target location tgt1.

SQL> insert into order values (1,1,'Tester','Boardwalk','tgt1');

To verify that the change was replicated correctly, make a selection from both tgt1 and tgt2.

SQL> select * from tgt1.order;

        ID      SUBID NAME            STREET          ADDRESS_TO
---------- ---------- --------------- --------------- ----------
         1          1 Tester          Boardwalk       tgt1

SQL> select * from tgt2.order;
no rows selected

Scenario 2

This example requires changes captured from location src to be replicated to target group TARGET, but only to target location tgt2, even though location tgt1 is also a part of TARGET. In this case, action Restrict with parameter AddressTo should be defined on SOURCE with value set to {address_to} and the integrate address is restricted by the content of the AddressSubscribe parameter set to alias a defined on the target location tgt2 as shown in the screenshot below.

When value a is inserted in the address_to column on the source location, the change should be replicated only to target location tgt2, omitting tgt1:

SQL> insert into order values (5,5,'Tester','Boardwalk','a');

To verify that the change was replicated correctly, make a selection from both tgt1 and tgt2.

SQL> select * from tgt2.order where id = 8;

        ID      SUBID NAME            STREET          ADDRESS_TO
---------- ---------- --------------- --------------- ----------
         8          6 Tester          Boardwalk       a

SQL> select * from tgt1.order where id = 8;
no rows selected

Example 3: Using Subselect on Non-replicated Table in Refresh Condition

This is an example of using action Restrict with parameter RefreshCondition for updating key values from a source table to a target table based on a subset of values from a table in the source that is not included in the channel.

Prerequisites

An Oracle-to-Oracle channel mychannel with the product table on both source and target locations.
The orders table on the source location that is not in the channel.

Scenario

Suppose we need to update the values of the prod_id column in the product table with only values falling under a certain subset, such as 5, 10, 15, 20, and 25, from another non-replicated table orders. To achieve this, we will define a sub-query expression for the RefreshCondition parameter, that will select specific values from the orders table on the source even though this table is not in the channel definition.

Steps

In the UI, define action Restrict on the source table:
a. On the Channel Details page, click the More Options icon in the top right menu and select View Actions.
b. In the Actions panel, click Add Action in the top-right menu and select Restrict.
c. In the New Action: Restrict dialog, select RefreshCondition and type in the following expression: {prod_id} in (select prod_id from source.orders corr where corr.prod_id in (5, 10, 15, 20, 25)). Click OK.
In the top-right menu of the Channel Details page, click Refresh Data.
In the Refresh Data dialog, click the Tables tab and select the product table.
Click the Refresh Data button at the bottom of the Refresh Data dialog.
When the refresh job starts, a notification appears at the top of the page. Click the View Refresh event link in the notification to open the Event Details page displaying detailed information about the refresh event.