Hvrrefresh

Instruct hvrrefresh to create new tables. Only 'basic' tables are created, based on the information in the channel. A basic table just has the correct column names and data types without any extra indexes, constraints, triggers, or tables spaces. Value S can be one of the following:

• b : Create basic (absent) tables only (mandatory).

• e : Keep existing structure.

• f : Force recreation (always recreate) of tables.

• k : Create an index (unique key or non-unique index). If the original table does not have a unique key, then a non-unique index is created instead of a unique key.

• o : Only create tables, do not refresh any data into them.

• p : Preserve (keep) existing data on recreate.

• r : Recreate (drop and create) if the column names do not match.

• u : Use Unicode data types such as nvarchar instead of varchar.

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

Defining an action with parameter Context can have different uses. For example, if action Restrict /RefreshCondition="{id}>22" /Context=qqq is defined, then normally all data will be refreshed, but if context qqq is enabled (-Cqqq), then only rows where id>22 will be refreshed. Variables can also be used in the restrict condition, such as "{id}>{hvr_var_min}". This means that hvrrefresh -Cqqq -Vmin=99 will only refresh rows with id>99.

Fire database triggers/rules while applying SQL changes for refresh.

Behavior for foreign key constraint in the target database which either reference or are referenced by a table which should be refreshed. Value for k is one or more of these letters:

• i : Ignore foreign key constraints. Normally this would cause foreign key constraint errors. This cannot be combined with other letters.

• x : Disable all such constraints before refresh and re-enable them at the end. If the DBMS does not support disable/re-enable syntax (e.g. Ingres) then constraints are instead dropped before refresh and recreated at the end. Note that for on-line refresh (option -q) without a select moment supplied (option -M) the actual re-enabling of disabled foreign key constraints is not done by the refresh itself but is instead delayed until the end of the next cycle of integration.

• d : For a target with 'deferred' or 'deferrable' foreign key constraints, perform entire refresh in a single (very large) transaction which is committed right at the end of refresh. This can use a lot of DBMS resources. It is also slower because HVR uses SQL delete and insert statements, because both truncate and 'direct path load' both imply a commit. This is only supported for Oracle (because other DBMSs do not support deferring of foreign key constraints). If this letter is combined with letter x (disable) then only non-deferrable constraints are disabled and then re-enabled. Deferred constraint handling cannot be used with table parallelism (option -P)

  If this option (-F) is not supplied then all foreign-key constraints will be disabled before refresh and re-enabled afterwards (letter x), unless HVR has no capability for foreign keys at all (letter i).

Granularity of refresh in database locations.

Valid values of x are:

• b (default): Bulk refresh using bulk data load.

• r : Row-wise refresh of tables.

Target location of refresh. 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 refreshed.

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

  If a symbolic time like -Mnow is supplied then a new "SCN time" will be retrieved each time the refresh job is run (not only when the hvrrefresh command is called. So if hvrrefresh-Mnow is run on Monday, and the refresh job it creates starts running at 10:00 Tuesday and runs again 10:00 on Wednesday, then the first refresh 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).

• serializable : Select all data from source database using a single transaction (therefore a single session) which has SQL isolation level serializable. This cannot be used with table parallelism (option -P).

• snapshot : Select all data from source database using a single transaction (therefore a single session) which has SQL isolation level snapshot (SQL Server only). Using snapshot isolation level requires enabling ALLOW_SNAPSHOT_ISOLATION database option in SQL Server. This cannot be used with table parallelism (option -P).
  

Create 'sub-jobs' which each refresh 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 refr (although the task name can always be overridden using option -T). Normally the first slice's job is named chn-refr0-x-y but numbers are left-padded with zeros, so if 10 slices are needed the first is named chn-refr00-x-y instead.

One technique is to generate lots of jobs for refresh of big channel (using this option and option -s) and add 'scheduler attribute' quota_run to the job group (named CHN-REFR) 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.

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' hvrrefresh commands;

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

hvrrefresh -rcen -O -n2 myhub/pwd mychn

will only generate this output;

hvrrefresh -rcen -Trefr0 -ttab1 -ttab2 myhub/pwd mychn
hvrrefresh -rcen -Trefr1 -ttab3 -ttab4 myhub/pwd mychn

Online refresh of data from a database that is continuously being changed. This requires that capture is enabled on the source database. The integration jobs are automatically suspended while the online refresh is running, and restarted afterwards. The target database is not yet consistent after the online refresh has finished. Instead, it leaves instructions so that when the replication jobs are restarted, they skip all changes that occurred before the refresh and perform special handling for changes that occurred during the refresh. This means that after the next replication cycle consistency is restored in the target database. If the target database had foreign key constraints, then these will also be restored.

Valid values for d are:

• wo : Write only. Changes before the online refresh should only be skipped on the write side (by the integrate job), not on the read side (by the capture job). If changes are being replicated from the read location to multiple targets, then this value will avoid skipping changes that are still needed by the other targets.

• rw : Read/Write. Changes before the online refresh should be skipped both on the read side (by the capture job) and on the write side (by the integrate job). There are two advantages to skipping changes on the capture side; performance (those changes will not be sent over the network) and avoiding some replication errors (i.e. those caused by an alter table statement). The disadvantage of skipping changes on the capture side is that these changes may be needed by other replication targets. If they were needed, then these other integration locations need a new 'online' refresh, but without -qrw, otherwise the original targets will need yet another refresh.

• no : No skipping. Changes that occurred before the refresh are not skipped, only special handling is activated for changes that occurred during the refresh. This is useful for online refresh of a context-sensitive restriction of data (-Ccontext and Restrict /RefreshCondition /Context).

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-refr0-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 refresh 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 refresh immediately (in HVR GUI, Run Interactively).

This option creates refresh job for performing the refresh of tables in a channel from a source location to target location. By default, this refresh job is created in SUSPEND state and are named chn-refr-source-target. This refresh job can be invoked using command Hvrstart as in the following example:

hvrstart -u -w hubdb chn-refr

Executing the above command unsuspends (moves to PENDING state) the jobs and instructs the scheduler to run them. Output from the jobs is copied to the hvrstart command's stdout and the command finishes when all jobs have finished. Jobs created are cyclic which means that after they have run they go back to PENDING state again. They are not generated by a trig_delay attribute which means that once they complete they will stay in PENDING state without getting retriggered.

hvrstart -i hubdb [object Object]-refr-[object Object]-[object Object]

-Ssliceexpr
Since v5.3.1/6

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

A refresh job is created per slice for refreshing only rows contained in the slice. These refresh jobs can be run in parallel to improve the overall speed of the refresh. Slicing can only be used for a single table (defined with option -t).

• Since HVR 5.6.5/0, it is NOT required to define Restrict/RefreshCondition to use modulo or boundary slicing. However, for numbered slices and 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/RefreshCondition, 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 recommend that any Restrict/RefreshCondition defined for slicing is also given a /Context parameter so it can be easily disabled or enabled.

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

Note that if an on-line refresh is done (option -q) and no Select Moment is specified (option -M) then only value no (resilience) is allowed, not rw (skip during capture and integrate) or wo (skip during integrate only).

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 refresh is running. The row could be refreshed 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).

Running bulk refresh (option -gb) for multiple slices in parallel is not supported for relational database targets. Run them one at the time instead and use Restrict /RefreshCondition with a filter such as {hvr_var_slice_condition} to protect rows on the target that will not be refreshed.

For Oracle, Microsoft SQL Server, and Redshift, running Refresh jobs in parallel for the same table is not recommended due to potential performance issues.

For Greenplum, BigQuery, and Hive ACID, running Refresh jobs in parallel for the same table must not be performed to avoid risk of corruption on the target location.

To schedule the Refresh jobs instead of running them in parallel, use option -j.

Value sliceexpr must have one of the following forms:

• 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/RefreshCondition 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/RefreshCondition.

  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/RefreshCondition 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/RefreshCondition.

  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 the Oracle's dbms_stats package. Click the browse ("...") button for Boundaries type of slicing and then click Suggest Values in Boundaries for Table dialog. 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. Gather statistics including column histograms, for table 'table_name', using all table rows, for all columns, and 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. Gather 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. Gather statistics including column histograms, for table 'table_name', using 70% of table rows, for column 'table_column', and 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. Gather statistics including column histograms, for table 'table_name', for all columns, and maximum 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/RefreshCondition . 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/RefreshCondition:

  Location	Action
  ora	Restrict/RefreshCondition="abs(mod({hvr_var_slice_col}, {hvr_var_slice_total})) = {hvr_var_slice_num}"/Context=slice
  terad	Restrict/RefreshCondition="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/RefreshCondition.

  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/RefreshCondition="country ={hvr_var_slice_value}" /Context=slice then HVR Refresh creates 2 slices (2 refresh jobs) - 1 for US and 1 for UK.
  • If option -S "'US';'UK'" is supplied with action Restrict/RefreshCondition="country IN {hvr_var_slice_value}" /Context=slice then HVR Refresh creates 2 slices (2 refresh jobs) - 1 for US and 1 for UK.
  • If option -S "('US','UK');('NL')" is supplied with action Restrict/RefreshCondition="country IN {hvr_var_slice_value}" /Context=slice then HVR Refresh creates 2 slices (2 refresh jobs) - 1 for US, UK and 1 for NL.

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

Only refresh objects referring to table codes specified by y. Values of y may be one of the following:

• tbl : Only refresh table name tbl.

• t1-t2 : Refresh all table codes that fall alphabetically between t1 and t2 inclusive.

• !tbl : Refresh all table names except tbl.

• !t1-t2 : Refresh all table codes except for those that fall alphabetically between t1 and t2 inclusive.

Specify alternative task for naming scripts and jobs.