Restrict

Last updated on Dec 15, 2021

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

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.

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

This parameter 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

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.

  • {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 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 only supported for DB locations or for File locations with Hive External Tables.

For hvrefresh, 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. 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.

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

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 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 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, such as dec01.
  • A location group name containing integrate locations, such as DECEN.
  • An alias for an integrate location, defined with AddressSubscribe, 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 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.

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). 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.

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). 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, such as dec01.
  • A location group name containing integrate locations, such as DECEN.
  • An alias for an integrate location, defined with parameter AddressSubscribe, 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 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, 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

context

Ignore action unless hvrrefresh or hrvrcompare context 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, 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.