Restrict

From HVR
Jump to: navigation, search
Actions
Previous: ColumnProperties
Next: AdaptDDL
Action Reference

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

Parameter Argument Description
/CaptureCondition sql_expr Only rows where this condition is TRUE are captured.

The SQL expression on a database location can contain these substitutions:

  • Pattern {col_name} is replaced with the actual value from that column of the row. The name used should be the column's 'base name' and not the 'HVR column name'.
  • {hvr_cap_loc} is replaced with the location name.
  • {hvr_cap_tstamp} is replaced with the moment that the change occurred.
  • {hvr_cap_user} is replaced with the name of the user which 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 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 this condition is TRUE are integrated.

The SQL expression can contain substitutions:

  • Pattern {col_name} is replaced with the actual value from that column of the row. The name used should be the column's 'HVR column name' and not the 'base name'.
  • {hvr_cap_loc} is replaced with the location where the capture was changed.
  • {hvr_cap_tstamp} is replaced with the moment that the change occurred.
  • {hvr_cap_user} is replaced with the name of the user which 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 this condition evaluates as TRUE are affected. If /CompareCondition is not defined then during compare this parameter also affects which rows are compared.

For refresh 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 can contain substitutions:

  • Pattern {col_name} is replaced with the actual value from that column of the row. The name used should be the column's 'base name' and not the 'HVR column name'.
  • Pattern {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.
  • Pattern {hvr_local_loc} is replaced with the current location name.
  • Pattern {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.
  • Pattern {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  
  • Pattern {hvr_var_slice_num} contains current slice number (starting from 0), if slicing is defined (option -S).  Since    v5.3.1/6  
  • Pattern {hvr_var_slice_total} contains total number of slices, when slicing is defined (option -S).  Since    v5.3.1/6  
  • Pattern {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 this condition 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

The SQL expression can contain substitutions:

  • Pattern {col_name} is replaced with the actual value from that column of the row. The name used should be the column's 'base name' and not the 'HVR column name'.
  • Pattern {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.
  • Pattern {hvr_local_loc} is replaced with the current location name.
  • Pattern {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.
  • Patterns {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 (-M) is supplied to HVR Refresh. 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.
  • Pattern {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  
  • Pattern {hvr_var_slice_num} contains current slice number (starting from 0), if slicing is defined (option -S).  Since    v5.3.1/6  
  • Pattern {hvr_var_slice_total} contains total number of slices, when slicing is defined (option -S).  Since    v5.3.1/6  
  • Pattern {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  
/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 without Capture /TriggerBased. 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 without Capture /TriggerBased. 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 duplicate records during Hvrrefresh or Hvrcompare. Enable this parameter to ignore duplicate records during Hvrrefresh or Hvrcompare. HVR performs 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).

You should enable this parameter 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

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.

SC-Hvr-Restrict demo01 cap refr range.png

SC-Hvr-Restrict demo01 cap subselect.png

See hvr_demo26 in $HVR_HOME/demo.