Using Contexts Variables for Comparing Data Based on Datetime Column
This section describes the steps of setting up a channel for comparing a manageable subset of data in two Oracle tables based on a DateTime column. HVR allows you to implement this behavior using action Restrict with the /CompareCondition and /Context parameters using context variables.
For the initial steps to set up a channel, refer to Quick Start for HVR - Oracle.
In this example, a channel is configured with source and target locations residing on Oracle databases.
To set up HVR Compare based on a DateTime column using context variables, perform the following actions:
Define action Restrict with parameters /CompareCondition and /Context for both source and target locations. The compare condition allows to compare only rows that satisfy a certain condition. The condition may be defined using the following pattern {hvr_var_xxx}, where xxx is a value of the context variable. The /Context parameter allows to activate the Restrict action only if the context is enabled. For more information, refer to sections /CompareCondition and /Context on the Restrict page.
a. In the HVR GUI, right-click Location Groups under the channel chn node, navigate to New Action and select Restrict.
b. Since the compare condition is defined for both source and target location groups, select '*' in the Group field. Then select table 'product' in the Table field.
c. Specify the following condition in the CompareCondition field: last_update>{hvr_var_last_modified}, where last_modified is a variable, the value for which can be defined in the Contexts tab of the HVR Compare dialog (see step 8 below). By defining different values/expressions for the variable, you can manage the subsets of data to be compared.
d. Enter the context name, e.g. 'update_date' in the /Context field. Click OK. HVR Compare is effective only when the context is enabled. The context can be enabled in the Contexts tab of the HVR Compare dialog (see step 7 below).
Additionally, define action Capture on source location group SRC and action Integrate on target location group TGT, which are mandatory for performing compare. For this, right-click source group SRC, navigate to New Action and select Capture. For location group TGT, select Integrate.
The resulting channel chn configuration will be as follows:
Set up HVR Compare: right-click channel chn and select HVR Compare. Select location ora1 in the left Location pane and location ora2 in the right Location pane.
Select table 'product' in the tree of tables below.
Under the Options tab, select the Row by Row Granularity compare method. Alternatively, you can select the Bulk Granularity compare method. For more information on the difference between the two compare methods, refer to section Hvrcompare.
Click the Contexts tab. In the Context pane, select context 'update_date' that was defined earlier in the Restrict dialog for the /Context parameter.
In the Variables pane, specify value sysdate-4 for variable last_modified defined on the source and target locations. Expression sysdate-4 selects only data which is 4 days old. Click Compare.
SYSDATE is an Oracle function that returns the current date and time set for the operating system on which the database resides. For other DBMSs, the appropriate date/time functions should be used.
After the compare event is complete, the Compare Result dialog appears showing the comparison details.
You can change the date range for which you want to compare data in locations by specifying different values/expressions for the context variable. For example, if you want to compare data modified on a particular date, you can define the following compare condition for source and target:
In this case, the context expressions are defined as follows:
