Hvrcompare

Enable context. This controls whether actions defined with parameter Context are effective or are ignored.

Defining an action with Context can have different uses. For example, if action Restrict /CompareCondition="{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. To supply variables for restrict condition use option -V.

Parameter /Context can also be defined on action ColumnProperties. This can be used to define /CaptureExpression parameters which are only activated if a certain context is supplied. For example, to define a context for case-sensitive compares.

Option -C corresponds to the Contexts tab in the HVR GUI.

-e
Since v5.5.5/6

Perform event-driven compare. In event-driven compare, the compare operation is managed using HVR's event system. For each compare operation performed, HVR maintains a record in the following catalog tables - hvr_event and hvr_event_result. In HVR GUI, the option to perform event-driven compare (Generate Compare Event) is available in the Scheduling tab.

HVR creates a compare job in the HVR Scheduler and the compare operation is started under a compare event. While performing event-driven compare, if a compare event with the same job name exists in a PENDING or RUNNING state then it is automatically canceled (FAILED) by the new compare event.

The results for event-driven compare are displayed in Insight Events.

In HVR GUI, by default, the event-driven compare is scheduled to Start Immediately. In CLI, to start the event-driven compare immediately, execute the command hvrstart immediately after executing the command for event-driven compare.

This option is also required to run the following compare operations:

1. Online compare. For more information, see option -obelow.
2. Direct file compare. For more information, see section Direct File Compare.

Granularity of compare operation in database locations.

Valid values of x are:

• b (default): Bulk compare using checksums.
• r : Row-wise compare of tables.

Retain and reuse intermediate files. This option allows you to retain the intermediate files that are generated during a direct file compare. It also allows you to reuse the retained intermediate files that were generated earlier by a similar compare. A compare is similar if the location, channel, tables, and Restrict /SliceCondition (if any) are identical.

In HVR GUI, the default value for this option (File Prereader Intermediate Files) is No reuse, delete afterwards.

This option is applicable only for direct file compare.

Valid values of x are:

• k: Keep/retain intermediate files generated during this compare. In HVR GUI, this option is displayed as No reuse, Keep afterwards.

• r: Reuse the retained intermediate files that were generated earlier by a similar compare and after the compare operation is completed, the reused intermediate files and the intermediate files generated during this compare are deleted. In HVR GUI, this option is displayed as Reuse old, delete afterwards.

• kr: Reuse retained intermediate files and retain the intermediate files generated during this compare. In HVR GUI, this option is displayed as Reuse old, keep afterwards.

Compare event only perform subset of slices implied by -S (table slices) option.

This option is only allowed with options -e and -S.

Value srange should be a comma-separated list of one of the following:

• N : Only perform 'sub slices' number N. Note that these slices are numbered starting from zero.
• N-M : Perform from slices from N to M inclusive.
• N- : Perform from slices from N onwards.
• -M : Perform from slices from the first slices until slice M.

Target location of compare. The other (read location) is specified with option -r. If this option is not supplied then all locations except the read location are targets.

Values of x maybe one of the following:

• loc : Only location loc.
• l1-l2 : All locations that fall alphabetically between l1 and l2 inclusive.
• !loc : All locations except loc.
• !l1-l2 : All locations except for those that fall alphabetically between l1 and l2 inclusive.

Mask (ignore) some differences between the tables that are being compared.

Valid values of mask can be:

• d : Mask out delete differences.
• i : Mask out insert differences.
• u : Mask out update differences.

Select data from each table of source from same consistent moment in time.

Value moment can be one of the following:

• time : Flashback query with select … as of timestamp. Valid formats are YYYY-MM-DD [HH:MM:SS] (in local time) or YYYY-MM-DDTHH:MM:SS+TZD or YYYY-MM-DDTHH:MM:SSZ or today or now[[+|-]SECS] or an integer (seconds since 1970-01-01 00:00:00 UTC). Note that if a symbolic time like -Mnow is supplied then a new "SCN time" will be retrieved each time the compare job is run (not only when the hvrcompare command is called. So if hvrcompare-Mnow is run on Monday, and the compare job it creates starts running at 10:00 Tuesday and runs again 10:00 on Wednesday, then the first compare will do a flashback query (for all tables) with an SCN corresponding to Tuesday at 10:00 and the second job run will use flashback query with an SCN corresponding to Wednesday at 10:00.
  
• scn=val : Flashback query with select … as of scn. Value is an Oracle SCN number, either in decimal or in hex (when it starts with 0x or contains hex digits).
  
• hvr_tx_seq=val : Value from HVR column hvr_tx_seq is converted back to an Oracle SCN number (by dividing by 65536) and used for flashback query with select … as of scn. Value is either in decimal or in hex (when it starts with 0x or contains hex digits).

Create 'sub-jobs' which each compare a bundle of no more than numtabs tables. In HVR GUI, this option is displayed as Limit Tables per Job in the Scheduling tab.

For example, if a channel contains 6 tables then option -n1 will create 6 jobs whereas were option -n4 to be used on the same channel then only 2 jobs will be created (the first with 4 tables, the last with just 2). If tables are excluded (using option -t) then these will not count for the bundling.

Jobs are named by adding a number (starting at 0) to the task name which defaults cmp (although the task name can always be overridden using option -T). Normally the first slice's job is named chn-cmp0-x-y but numbers are left-padded with zeros, so if 10 slices are needed the first is named chn-cmp00-x-y instead.

One technique is to generate lots of jobs for compare of a big channel (using this option and option -s) and add 'scheduler attribute' quota_run to the job group (named CHN-CMP) so that only a few (say 3) can run simultaneously. Scheduler attributes can be added by right-clicking on the job group and selecting Add Attribute.

Another technique to manage the compare of a channel with thousands of tables is to use this option along with options -R (ranges) and -T (task name) to do 'power of ten' naming and bundling, in case a single table encounters a problem. The following illustrates this technique;

First use [-n100] so each job tries to compare 100 tables.

If one of these jobs fails (say job chn-cmp03-x-y) then use options [-n10 -R30-39 -Tcmp03] to replace it with 10 jobs which each do 10 tables.

Compare tables twice with a delay in between. In CLI, this option can only be used along with option -o diff_diff. Capture and Integrate jobs are not required for performing this mode of online compare.

This online compare mode is similar to Compare tables twice with a Capture and Integrate flush in between (option -o diff_diff) however, with one difference. HVR performs a regular compare which produces a result (also known as diff). HVR then waits for secs seconds after which it again performs the regular compare which produces a second result (diff). The compare results generated in the first and second compare are combined to produce a final compare result.

-omode
Since v5.5.5/8

Online compare. Performs live compare between locations where data is rapidly changing. This option can only be used if Row by Row Granularity (option -gr) and Generate Compare Event (option -e) is selected (or supplied).

The results of the online compare are displayed in Insight Events.

Value mode can be either:

• diff_cap (default in HVR GUI): Compare tables once and combine differences with captured transactions. Performs a regular compare which produces a result (also known as diff). This result (diff) is compared with the transaction files (which are continuously created by the capture job) to identify and remove the pending transaction from result (diff) for producing the final compare result. This compare mode is supported for comparing databases and files.

• diff_diff: Compare tables twice with a Capture and Integrate flush in between. Performs a regular compare which produces a result (also known as diff). HVR then waits for the completion of a full Capture and Integrate cycle after which it again performs the regular compare which produces a second result (diff). The compare results generated in the first and second compare are combined to produce a final compare result. This compare mode is only supported for comparing databases.
  

  If Compare tables twice with a delay in between (option -Nsecs) is selected (or supplied with diff_diff), HVR waits for a fixed amount of time (seconds) defined, instead of waiting for the completion of Capture and Integrate cycle in between compares. For more information, see option -Nsecs above. Capture and Integrate jobs are not required for performing this mode of online compare.

  If a running diff_cap online compare job is suspended/deleted, HVR will continue to accumulate tx files with each capture cycle, which will lead to higher disk space usage. For the list of pending events associated with the compare job, use hvreventview. To cancel the job, use hvreventtool. Scheduling a new event driven compare with the same task name will also cancel the previous event automatically.

Only show OS command implied by options -n (jobs for bundles of tables) or -S (table slices), instead of executing them. This can be used to generate a shell script of 'simpler' hvrcompare commands;

For example, if a channel only contains tables tab1, tab2, tab3, and tab4 then this command;

hvrcompare -rcen -O -n2 myhub/pwd mychn

hvrcompare -rcen -Tcmp0 -ttab1 -ttab2 myhub/pwd mychn
hvrcompare -rcen -Tcmp1 -ttab3 -ttab4 myhub/pwd mychn

Only perform certain 'sub jobs' implied by either options -N (job for bundles of tables) or -S (table slices). This option cannot be used without one of those options.

Value rangeexpr should be a comma-separated list of one of the following:

• N : Only perform 'sub job' number N. Note that these jobs are numbered starting from zero (e.g the first is chn-cmp0-rloc-wloc).
• N-M : Perform from jobs from N to M inclusive.
• N- : Perform from jobs from N onwards.
• -M : Perform from jobs from the first job until job M.

Schedule invocation of compare scripts using the HVR Scheduler. In HVR GUI, this option is displayed as Schedule Classic Job in the Scheduling tab.

Without this option the default behavior is to perform the compare immediately (in HVR GUI, Run Interactively).

This option creates compare job for comparing the tables. By default, this compare job is created in SUSPEND state and they are named chn-cmp-source-target. This compare job can be invoked using command Hvrstart as in the following example:

hvrstart -u -w hubdb chn-cmp

Once a compare job has been created with option -s then it can also be run manually on the command line (without using HVR Scheduler) as follows:

hvrstart -i hubdb chn-cmp-source-target

-Ssliceexpr
Since v5.3.1/6

Compare large tables using slicing. Value sliceexpr can be used to split table into multiple slices. In HVR GUI, this option is displayed as Slice Table in the Scheduling tab.

• Since HVR 5.6.5/0, it is NOT required to define Restrict/CompareCondition to use modulo or boundary slicing. However, for numbered slices and a list of values slicing, it is recommended to define Restrict /SliceCondition with {hvr_slice_*} substitution instead.
• Prior to HVR 5.6.5/0, the value sliceexpr affects action Restrict/CompareCondition, so it is required to define this action for using slicing in HVR. This action must be defined on the table (at least on the read location) and must contain a relevant {hvr_var_slice_*} substitution. It is recommended that any Restrict/CompareCondition defined for slicing is also given a /Context parameter so it can be easily disabled or enabled.

If performing Schedule Classic Job (option -s), per slice a compare job is created for comparing only rows contained in the slice. These compare jobs can be run in parallel to improve the overall speed of the compare. Slicing can only be used for a single table (defined with option -t).

If performing an event driven compare - Generate Compare Event (option -e), only a single compare job is created for all slices.

If performing event-driven compare (option -e), multiple tables can be sliced simultaneously (same job) by supplying the table name with the sliceexpr. The format is tablename.sliceexpr. Since v5.5.5/6

As with option -n (bundles of tables), jobs are named by adding a number (starting at 0) to the task name which defaults cmp (although this task name can always be overridden using option -T). Normally the first slice's job is named chn-cmp0-source-target but numbers are left-padded with zeros, so if 10 slices are needed the first is named chn-cmp00-source-target instead.

The column used to slice a table must be 'stable', because if it is updated then a row could 'move' from one slice to another while the compare is running. The row could be compared in two slices (which will cause errors) or no slices (data-loss). If the source database is Oracle then this problem can be avoided using a common Select Moment (option -M).

For more information on slicing limitations, see section Slicing Limitations below.

• col%num
  More details

  Slicing using modulo of numbers. In HVR GUI, this option is displayed as Modulo.

  • Since HVR 5.6.5/0, it is NOT required to define Restrict/CompareCondition to use this type of slicing.
  • Prior to HVR 5.6.5/0, this slicing form affects the substitution {hvr_var_slice_condition}, which must be defined for the slice table using Restrict/CompareCondition.

  If -Sabc%3 is supplied then the conditions for the three slices are:

  
  mod(round(abs(coalesce(abc, 0)), 0), 3)= 0
  mod(round(abs(coalesce(abc, 0)), 0), 3)= 1
  mod(round(abs(coalesce(abc, 0)), 0), 3)= 2
  

  Note that the use of extra SQL functions (e.g. round(), abs() and coalesce()) ensure that slicing affect fractions, negative numbers and NULL too. Modulo slicing can only be used on a column with a numeric data type.

• col<b1[<b2]… [<bN]
  More details

  Slicing using boundaries. In HVR GUI, this option is displayed as Boundaries.

  If N boundaries are defined then N+1 slices are implied.

  • Since HVR 5.6.5/0, it is NOT required to define Restrict/CompareCondition to use this type of slicing.
  • Prior to HVR 5.6.5/0, this slicing form affects the substitution {hvr_var_slice_condition}, which must be defined for the slice table using Restrict/CompareCondition.

  If -Sabc<10<20<30 is supplied then the conditions for the four slices are:

  
  abc <= 10
  abc > 10 and abc <= 20
  abc > 20 and abc <= 30
  abc > 30 or abc is null
  

  Note that strings can be supplied by adding quotes around boundaries, i.e. -Sabc<'x'<'y'<'z'.

  For very large tables consider the DBMS query execution plan. If the DBMS decides to 'walk' an index (with a lookup for each matched row) but this is not optimal (i.e. a 'serial-scan' of the table would be faster) then either use DBMS techniques ($HVR_SQL_SELECT_HINT allows Oracle optimizer hints) or consider modulo slicing (col%num) instead.

  For this type of slicing, HVR can suggest boundaries by using Oracle's dbms_stats package. Click the browse ("...") button for Boundaries type of slicing and then click Suggest Values in Boundaries for Table dialog. The number of slices can be also specified.

  Gathering column histogram statistics is required for this functionality to work. This can be done by calling the dbms_stats.gather_table_stats stored procedure.

  Examples:

  1. Gathers statistics including column histograms, for table 'table_name', using all table rows, for all columns, and a maximum of 254 histogram buckets (therefore up to 254 slice boundaries can be suggested).

     exec dbms_stats.gather_table_stats('schema_name', 'table_name', estimate_percent=>100, method_opt=>'for all columns size 254');
     

  2. Gathers statistics including column histograms, for table 'table_name', using all table rows, for all indexed columns, and default number of histogram buckets.

     exec dbms_stats.gather_table_stats('schema_name', 'table_name', estimate_percent=>100, method_opt=>'for all indexed columns');
     

  3. Gathers statistics including column histograms, for table 'table_name', using 70% of table rows, for column 'table_column', and a maximum of 150 histogram buckets (therefore up to 150 slice boundaries can be suggested).

     exec dbms_stats.gather_table_stats('schema_name', 'table_name', estimate_percent=>70, method_opt=>'for columns table_column size 150');
     

  4. Gathers statistics including column histograms, for table 'table_name', for all columns, and a maximum of 254 histogram buckets. This is an obsolete way to generate statistics and there are much fewer options supported.

     analyze table table_name compute statistics for all columns size 254;
     

• num
  More details

  Numbered slices. In HVR GUI, this option is displayed as Count.

  • Since HVR 5.6.5/0, the number of each slice is assigned to substitution {hvr_slice_num}, which must be defined for the slice table using Restrict /SliceCondition. Substitution {hvr_slice_total} is also assigned to the total number of slices.
  • Prior to HVR 5.6.5/0, the substitution {hvr_var_slice_num} must be defined for the slice table using Restrict/CompareCondition . Substitution {hvr_var_slice_total} is also assigned to the total number of slices.

  In heterogeneous environments doing normal modulo, slicing is not always possible because of the different syntax of the modulo operation. For example, in Oracle modulo operation is mod(x,y) and in Teradata, it is x mod y. Also, negative numbers are handled differently on these two databases. For this scenario, two Restrict actions can be defined, one for the capture location (Oracle) and the other for the integrate location (Teradata):

  Location	Action
  ora	Restrict/SliceCondition="abs(mod({hvr_var_slice_col}, {hvr_slice_total})) = {hvr_slice_num}" /Context=slice
  terad	Restrict/SliceCondition="abs({hvr_var_slice_col} mod {hvr_slice_total}) = {hvr_slice_num}" /Context=slice

  Prior to HVR 5.6.5/0, using Restrict/CompareCondition:

  Location	Action
  ora	Restrict/CompareCondition="abs(mod({hvr_var_slice_col}, {hvr_var_slice_total})) = {hvr_var_slice_num}" /Context=slice
  terad	Restrict/CompareCondition="abs({hvr_var_slice_col} mod {hvr_var_slice_total}) = {hvr_var_slice_num}" /Context=slice

  If options -S3 -Vslice_col=abc are supplied then the conditions for the three slices are:

  • Capture Location

    
    abs(mod(abc, 3))= 0
    abs(mod(abc, 3))= 1
    abs(mod(abc, 3))= 2
    

  • Integrate Location

    
    abs(abc mod 3)= 0
    abs(abc mod 3)= 1
    abs(abc mod 3)= 2
    

• val1[;val2]…
  More details

  Slicing using a list of values. In HVR GUI, this option is displayed as Series. Values are separated by semicolons.

  • Since HVR 5.6.5/0, each slice has its value assigned directly into substitution {hvr_slice_value}, which must be defined for the sliced table using Restrict /SliceCondition.
  • Prior to HVR 5.6.5/0, the substitution {hvr_var_slice_value} must be defined for the slice table using Restrict/CompareCondition.

  Example:

  A large table with column country having values US, UK, and NL can be sliced in the following manner:

  • If option -S "'US';'UK'" is supplied with action Restrict/CompareCondition="country ={hvr_var_slice_value}" /Context=slice then HVR Compare creates 2 slices (2 compare jobs) - 1 for US and 1 for UK.
  • If option -S "'US';'UK'" is supplied with action Restrict/CompareCondition="country IN {hvr_var_slice_value}" /Context=slice then HVR Compare creates 2 slices (2 compare jobs) - 1 for US and 1 for UK.
  • If option -S "('US','UK');('NL')" is supplied with action Restrict/CompareCondition="country IN {hvr_var_slice_value}" /Context=slice then HVR Compare creates 2 slices (2 compare jobs) - 1 for US, UK and 1 for NL.

  The double quotes (") supplied with option -S is not required in HVR GUI.

Only compare tables specified by y.

Values of y may be one of the following:

• tbl : Only compare table name tbl.
• t1-t2 : Compare all table codes that fall alphabetically between t1 and t2 inclusive.
• !tbl : Compare all table names except tbl.
• !t1-t2 : Compare all table codes except for those that fall alphabetically between t1 and t2 inclusive.

Specify an alternative name for a compare task used for naming scripts and jobs. The task name must start with a 'c'.

-v

Verbose. This causes row-wise compare to display each difference detected. Differences are presented as SQL statements. This option requires that option -gr (row-wise granularity) is supplied.

Supply variable for restrict condition. This should be supplied if a Restrict /CompareCondition parameter contains string {hvr_var_name}. This string is replaced with val.