Restrict

Contents

Description

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.

Parameter

Argument

Description

/CaptureCondition

sql_expr

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.

  • {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. 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 Capture /IgnoreCondition has a similar effect to this parameter but does not do update conversion.

Parameter /CaptureCondition can also be defined on a Salesforce location; in this case it should be an Salesforce Object Query Language (SOQL) expression which can be put into the WHERE clause of the SELECT. Brace substitutions (e.g. {prod_id} < 100) are here not performed, but columns can be specified without braces (e.g. Prod_id < 100).

/IntegrateCondition

sql_expr

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.

  • {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 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.

/RefreshCondition

sql_expr

During refresh, only rows where the condition sql_expr evaluates as TRUE are affected. If /CompareCondition is not defined then during compare this parameter also affects which rows are compared. This parameter should not be defined with /SliceCondition.

This parameter is only supported for DB 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 source where condition).
  • If defined on the target side, during bulk refresh it protects non–matching rows from bulk delete (delete from target where condition, instead of just truncate target).
  • If defined for row–wise refresh, it prevents some rows from being selected for comparison with the source rows (select * from target where condition).

The SQL expression sql_expr can contain the following substitutions:

  • {colname} is replaced with the value of current table's column colname.
  • {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.
  • {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.
  • {hvr_var_slice_condition} is used (mandatory) if slicing is defined with a boundary expression (option -Scol<b1[<b2]...[<bN]) or a modulo expression (option -Scol%N).   Since    v5.3.1/6  
  • {hvr_var_slice_num} contains current slice number (starting from 0), if slicing is defined (option -S).    Since    v5.3.1/6  
  • {hvr_var_slice_total} contains total number of slices, when slicing is defined (option -S).    Since    v5.3.1/6  
  • {hvr_var_slice_value} is used (mandatory) if slicing is defined with a list of values (option -Sv1[;v2]...[;vN]).    Since    v5.3.1/6  

/CompareCondition

sql_expr

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 /RefreshCondition is defined then Hvrcompare will use /RefreshCondition for comparing. This parameter should not be defined with /SliceCondition.

The SQL expression can contain substitutions:

  • {colname} is replaced with the value of current table's column colname.
  • {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.
  • {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 HVR Refresh. For example, if the target is defined with actions ColumnProperties /Name=tkey /TimeKey /IntegrateExpression="{hvr_integ_seq}" and Restrict /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.
  • {hvr_var_slice_condition} is used (mandatory) if slicing is defined with a boundary expression (option -Scol<b1[<b2]...[<bN]) or a modulo expression (option -Scol%N).   Since    v5.3.1/6  
  • {hvr_var_slice_num} contains current slice number (starting from 0), if slicing is defined (option -S).    Since    v5.3.1/6  
  • {hvr_var_slice_total} contains total number of slices, when slicing is defined (option -S).    Since    v5.3.1/6  
  • {hvr_var_slice_value} is used (mandatory) if slicing is defined with a list of values (option -Sv1[;v2]...[;vN]).    Since    v5.3.1/6  

For DB locations, more than one substitutions can be supplied using the logical operators AND, and OR. For example, {hvr_var_slice_condition} AND customer_name = 'Abc'.

For File locations, more than one substitutions can be supplied using the logical operator &&. The OR operation is not supported for file locations. For example, {hvr_var_slice_condition} && customer_name = 'Abc'.

/SliceCondition
  Since    v5.6.5/0  

sql_expr

During sliced (option -S) refresh or compare, only rows where the condition sql_expr evaluates as TRUE are affected. This parameter is allowed and required only for the Count (option num) and Series (option val1[;val2]...) type of slicing.

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 source where condition).

  • If defined on the target side, 

    • during bulk refresh it protects non–matching rows from bulk delete (delete from target where condition, instead of just truncate target). 

    • during row–wise refresh it prevents some rows from being selected for comparison with the source rows (select * from target where condition).  

    • during compare it affects which rows are selected (select * from source where 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).

  • {hvr_slice_value} contains current slice value if slicing is defined with a Series of values (option -Sv1[;v2]...[;vN]).

For examples, see hvrrefresh or hvrcompare option -S.

It is recommended to define /Context parameter when using these substitutions so it can be easily disabled or enabled.

/SliceCondition cannot be used if any of the following slicing substitution {hvr_var_slice_condition}, {hvr_var_slice_num}, {hvr_var_slice_total}, or {hvr_var_slice_value} is mentioned in any of the actions defined on the channel/table involved in the current compare/refresh. In this case, slicing can be achieved using /RefreshCondition and /CompareCondition using these four substitutions.

/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, such as dec01.
  • A location group name containing integrate locations, such as DECEN.
  • An alias for an integrate location, defined with /AddressSubscribe (see below), for example 22.
  • A pattern to match one of the above, such as dec*.
  • A list of the above, separated by a semicolon, colon or comma, such as cen,22.

This parameter should be defined with Capture /TriggerBased. 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.

/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 not supported if Capture /TriggerBased is not defined. A possible alternative for log–based capture channels is Restrict /AddressTo and Restrict /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.

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 HVR Refresh 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 not supported if Capture /TriggerBased is not defined. A possible alternative for log–based capture channels is Restrict /AddressTo and Restrict /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, such as dec01.
  • A location group name containing integrate locations, such as DECEN.
  • An alias for an integrate location, defined with /AddressSubscribe (see below), for example 22 or Alias7.
  • A pattern to match one of the above, such as dec*.
  • A column name enclosed in braces, such as {mycol}. The contents of this column will be used as an integrate address. This is similar to parameter /HorizColumn.
  • A list of the above, separated by a semicolon, colon or comma, such as cen,{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, such as dec01.
  • A location group name containing other integrate locations, such as DECEN.
  • A pattern to match one of the above, such as dec*.
  • An alias to match an integrate address defined with /AddressTo or /HorizColumn or matched by {hvr_address} in Capture /Pattern. 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, such as 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

ctx

Ignore action unless Hvrrefresh or Hvrcompare context ctx is enabled.

The value should be the name of a context (a lowercase identifier). It can also have form !ctx, which means that the action is effective unless context ctx is enabled. One or more contexts can be enabled for Hvrcompare or Hvrrefresh (on the command line with option –Cctx). Defining an action which is only effective when a context is enabled can have different uses. For example, if action Restrict /RefreshCondition="{id}>22" /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, such as "{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.

Examples

This section describes examples of using the following parameters of Restrict

  • /CaptureCondition and /RefreshCondition
  • /AddressTo and /AddressSubscribe

Using /CaptureCondition and /RefreshCondition

To replicate only rows of table product having id between 1000000 and 2000000, use parameters /CaptureCondition and /RefreshCondition. Also only rows of table order for products which are in state 16 need to be captured. This is implemented with another /CaptureCondition parameter.

Using /AddressTo and /AddressSubscribe

This section describes the examples of using the Restrict /AddressTo and Restrict /AddressSubscribe actions. 

The following replication configuration will be used:

  • location group SRCGRP having 1 source location src 
  • location group TGTGRP having 2 target locations tgt1 and tgt2

Create table (t_orderon source location:

create table t_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 Restrict /AddressTo field being the {address_to} column defined on source location src as shown in the screenshot below.  


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

SQL> insert into t_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.t_order;

        ID      SUBID NAME            STREET          ADDRESS_TO
---------- ---------- --------------- --------------- ----------
         1          1 Tester          Boardwalk       tgt1
SQL> select * from tgt2.t_order;
no rows selected

Scenario 2

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


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

SQL> insert into t_order values (5,5,'Tester','Boardwalk','a');
To verify that the change was replicated correctly, make a selection from both tgtand tgt2.
SQL> select * from tgt2.t_order where id = 8;

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

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

Using Subselect on Non-replicated Table in Refresh Condition

This is an example of using Restrict /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

  1. An Oracle-to-Oracle channel chn with the Product table on both source and target locations.
  2. 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 subquery 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

  1. In the HVR GUI, define the Capture action on the source table: right-click the source group, navigate to New Action and click Capture. Click OK.
  2. Define the Integrate action on the target table: click the target group, navigate to New Action and click Integrate. Click OK.
  3. Define the Restrict action on the source table: right-click the source group, navigate to New Action and click Restrict.
  4. In the New Action: Restrict dialog, select the /RefreshCondition checkbox 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.


  5. Right-click the channel chn and click Refresh. Select the Product table. 
  6. Under the Options tab, select the refresh method: either Bulk Granularity or Row by Row Granularity. Optionally, configure other parameters. Click OK.
  7. The Refresh Result dialog shows the summary statistics of the refresh operation. In this case, three rows were inserted into the target database.