Refreshing Data

Locations section lists:

• source location from which the table structure and data will be copied.
• target location(s) into which the table structure and data will be loaded.

Tables section lists the table(s) that are to be refreshed.

Create tables that are absent on a target location using the table definitions in a channel.

Create tables that are absent on a target location based on the table definitions in a channel, and alter or recreate tables if their layout does not match the table layout defined in a channel.

This option is enabled only when Only Create Target Tables that are Missing or Create Missing Target Tables and Alter or Recreate Tables with Incorrect Layout is selected.

Do not create an index (unique key or non-unique index).

When this option is not selected and if the original table does not have a unique key, then a non-unique index is created instead of a unique key.

This option is available only when option Only Create Target Tables that are Missing or Create Missing Target Tables and Alter or Recreate Tables with Incorrect Layout is selected.

Drop all target tables and create new ones based on the table layout in a channel.

This option is available only when option Create Missing Target Tables and Alter or Recreate Tables with Incorrect Layout is selected.

Keep the existing structure of all target tables. If a target table contains a column or index that is not included in the channel, HVR will not remove it. Also, columns that are too wide will not be shrunk.

This option is available only when option Create Missing Target Tables and Alter or Recreate Tables with Incorrect Layout is selected.

Preserve the existing data in a target table when recreating it. This option is available only when option Create Missing Target Tables and Alter or Recreate Tables with Incorrect Layout is selected.

Bulk Refresh means that the target object is truncated, and then the bulk copy is used to refresh the data from the read location. On certain locations, during Bulk Refresh table indexes and constraints will be temporarily dropped or disabled and will be reset after the refresh is complete.

During Bulk Refresh, HVR typically streams data directly over the network into a bulk loading interface (e.g. direct path load in Oracle) of the target database. For DBMSs that do not support a bulk loading interface, HVR streams data into intermediate temporary staging files (in a staging directory) from where the data is loaded into the target database. For more information about staging files/directory, see section "Burst Integrate and Bulk Refresh" in the respective Source and Target Requirements.

This option is commonly referred to as Bulk Refresh in this documentation.

In the Command Line Interface, this option corresponds to hvrrefresh -gb.

Perform upsert refresh. This option merges the source rows into the target without initially deleting or truncating the target rows. Similar to Burst Integrate, the data is initially loaded into burst tables named tbl_ _rb and then the burst table is merged with the base table. Changes are applied as inserts or updates, similar to Resilient Integrate.

Deletes or key updates are not covered by this type of refresh. Rows that are deleted on the source will still exist in the target. Rows for which the key has been updated in the source will exist with both the old and new key in the target.

This option is only available for target locations that support the Burst integrate method.

This option cannot be used for tables with a TimeKey column.

In the Command Line Interface, this option corresponds to hvrrefresh -u.

Row-by-Row Refresh, also referred to as Row-wise Refresh, compares data on read and write locations and produces a 'diff' result based on which only rows that differ are updated on the write location, each row is refreshed individually. This results in a list of a minimal number of inserts, updates or deletes needed to re-synchronize the tables.

For column-oriented databases (e.g., Redshift, Snowflake, Google BigQuery), Row-wise Refresh is best used on small amount of data, e.g., on tables with a small amount of changed data or on small tables. In other cases Row-wise Refresh on column-oriented databases takes a lot of time.

Verbose. This option creates binary diff files containing individual differences detected.

Section Analyzing Diff File explains how to view and interpret the contents of a diff file.

This option is only displayed for the Repair - Row by RowGranularity mode (not available when option Bulk Load - Table Granularity is selected).

In the Command Line Interface, this option corresponds to hvrrefresh -v.

Select data from each table of the source from the same consistent moment in time. Options can be one of the following:

• Now: If this option is selected, then a new "SCN time" will be retrieved each time the refresh job is run. So if Now is run on Monday, and the refresh job it creates starts running at 10:00 Tuesday and runs again at 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.

• Specific Time: Flashback query with select … as of timestamp.
• hvr_tx_seq: Value from 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).
• Oracle SCN: 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).

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

Available options are:

• Changes before refresh are skipped by both capture and integrate jobs

  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 this option, otherwise, the original targets will need yet another refresh.

• Only integrate job skips changes before refresh

  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.

• No changes are skipped, but changes before end of refresh are integrated with resilience

  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 the online refresh of a context-sensitive restriction of data (hvrrefersh-Ccontext and action Restrict with parameters RefreshCondition and Context).

This option controls whether actions defined with parameter Context are effective or are ignored. For more information, see the Refresh and Compare Contexts concept page.

Defining an action with parameter Context can have different uses. For example, if action Restrict with parameters 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.

Action ColumnProperties with parameter Context can also be defined. This can be used to define CaptureExpression parameters which are only activated if a certain context is supplied. For example, to define a Bulk Refresh context where SQL expressions are performed on the source database (which would slow down capture) instead of the target database (which would slow down Bulk Refresh).

This option is displayed only if either of the action (CollisionDetect, ColumnProperties, Environment, FileFormat, Integrate, Restrict, TableProperties, Transform) is defined with parameter Context.

In the Command Line Interface, this option corresponds to hvrrefresh -C.

Variables

Supply value for refresh restrict condition or add a new variable.

Parallelism for sessions. Perform refresh for different tables in parallel using sub-processes. The job will start processing tables in parallel; when the first of these is finished the next table will be processed, and so on.

The slicing section allows you to configure the slicing options. For more information, see the Slicing concept page.

Table Slicing dialog can be invoked by clicking the Add Table button. In this dialog, you can choose the slicing separately for each table in your source.

Types of slicing that can be applied to the table:

• Modulo
• Boundary
• Count
• Series

The table will be sliced by the chosen column's data.

Slicing on a non-key column can cause errors if an update moves a row between slices.

The Distinct Values field is shown if dbms_stats gathering is enabled for the database. The Distinct Values column is only available for Oracle.

Set the boundaries for each slice. To set them, you have to know the data in your table.

Choose the data type for slicing. To set it, you have to know the data in your table.

Set values for each slice. To set them, you have to know the data in your table.

Choose which slices you want to perform the job on.

For example, you have chosen Boundary slicing and have set the boundaries to 1000, 3500, and 6000. In this case, you will have 5 slices: with values 0 to 999, 1000 to 3499, 3500 to 5999, and 6000 to the end of the table.

Slicing Suggestions dialog can be invoked by:

• clicking the Suggest Slicing button in the Slicing section;
• or by clicking the Suggest button in the Table Slicing dialog.

In the Slicing Suggestions dialog, you can configure the slicing settings for the current job.

Repeat a previous slicing job or base it upon certain data:

• Repeat slicing from last refresh. Repeats slicing from the last Refresh event.
• Repeat slicing from last compare. Repeats slicing from the last Compare event.
• Based on row-count from last refresh. Inspects previous Refresh events and suggests the new slicing based on its result.
• Based on row-count from last compare. Inspects previous Compare events and suggests the new slicing based on its result.
• Based on row-counts from DB statistics. Gets the row count from the Oracle package.

Rows per slice – sets the number of rows per slice. Set to 10 million by default.

Specify an alternative name for a refresh task to be used for naming scripts and jobs. The task name must begin with an 'r'.

Enable table isolation during refresh. Isolated table refresh allows integrate jobs to continue for tables that are not part of the isolated refresh. The integrate jobs for the isolated tables will resume once the refresh is complete. You can specify when to start isolation:

• Now: Start isolation immediately.
• At Refresh Start: Start isolation when the refresh starts. This is helpful for scheduled refreshes that will not start immediately.

Behavior for foreign key constraint in the target database which either reference or are referenced by a table which should be refreshed.

Available option is:

• Disable Before Refresh: 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 Online Refresh without a select moment supplied (option Select moment with Oracle flashback query) 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.

If this option is unselected, the foreign key constraints are ignored. Normally this would cause foreign key constraint errors.

Disable database triggers during integrate. This option is enabled only when Repair - Row by Row Granularity is selected.

This option is supported only for certain location types. For the list of supported location types, see Disable/enable database triggers during integrate (NoTriggerFiring) in Capabilities.

For Ingres, this parameter disables the firing of all database rules during integration. This is done by performing SQL statement set norules at connection startup.

For SQL Server, this parameter disables the firing of database triggers, foreign key constraints and check constraints during integration if those objects were defined with not for replication. This is done by connecting to the database with the SQL Server Replication connection capability. A disadvantage of this connection type is that the database connection string must have form host,port instead of form \\host\instance. This port needs to be configured in the Network Configuration section of the SQL Server Configuration Manager. Another limitation is that encryption of the ODBC connection is not supported if this parameter is used for SQL Server.

For Oracle and SQL Server, HVR will automatically disable triggers on target tables before the Refresh and re-enable them afterwards, unless option Online Refresh is defined.

Other ways to control trigger firing are described in Managing Recapturing Using Session Names.

Add the timekey truncate record as the first record of each table that is refreshed from the source location. This implies all previous timekey records for this table can be ignored by the system or application that consumes the data in the target location.

This option is only supported if the target is a file or Kafka location.

This option can only be used for tables with a TimeKey column.

Scheduling Options

Schedule the time to run the refresh job. Available options are:

• Start Immediately: Invoke the refresh job immediately after clicking the Refresh Data button.
• Schedule Once at: Schedule the refresh job to run at a specific time once.
• Schedule Repeatedly at: Schedule the refresh job to run at specific times repeatedly.
• Delay Running Refresh Job: Schedule invocation of the refresh job by leaving it in the SUSPEND state.

Show the CLI command equvalent to the UI options selected in the dialog. You can use (copy and paste) the equivalent line to manually repeat or perform this operation later on.

In cases when the command line equivalents are different for Linux/Unix and Windows, both options are shown.