Restrict
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:
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:
A subselect can be supplied, for example exists (select 1 from lookup where id={id}). For Burst Integrate (when Integrate /Burst is defined), 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 (when Integrate /Burst is NOT defined), 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. However, if 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.
The SQL expression sql_expr can contain the following substitutions:
|
| /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:
|
/SliceConditionSince 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. 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 * fromsourcewherecondition). If defined on the target side,
The SQL expression sql_expr can contain the following substitutions:
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.
|
| /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:
|
| /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:
|
| /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:
|
| /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_order) on 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 tgt1 and 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
- An Oracle-to-Oracle channel chn 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 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
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.
Define the Integrate action on the target table: click the target group, navigate to New Action and click Integrate. Click OK.
Define the Restrict action on the source table: right-click the source group, navigate to New Action and click Restrict.
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.
Right-click the channel chn and click Refresh. Select the Product table.
Under the Options tab, select the refresh method: either Bulk Granularity or Row by Row Granularity. Optionally, configure other parameters. Click OK.
The Refresh Result dialog shows the summary statistics of the refresh operation. In this case, three rows were inserted into the target database.
