Refreshing Data

Last updated on May 04, 2022

You can choose to repeat a previous slicing job or base it upon certain data

Contents

Refresh Data option allows you to load data selected from a source location to a target location. The source must be a database location, but the targets can be databases or file locations. For more information, see the Refresh concept page.

Refreshing from a source location is supported only on certain location types. For the list of supported source location types, see section Refresh and Compare in Capabilities.

Option Refresh Data is equivalent to the hvrrefresh CLI command

This option to Refresh data is available on the following pages:

  • Channel Details: the Refresh Data button at the top right of the page.
  • Locations: the Refresh Data option under the More Options menu  at the top right of the page.
  • Location Details: the Refresh Data option under the More Options menu  related to each channel in the Channel Membership pane. 
  • Tables: the Refresh Data option under the More Options menu  at the top right of the page.
  • Table Details: the Refresh Data option under the More Options menu  at the top right of the page. 
  • Event Details: (related to a refresh event): the Repeat Refresh button at the to right of the page.
  • Jobs: the New Refresh option under the More Options menu  related to a refresh job.

The option to Refresh data may appear disabled in certain cases, for example, on the Channel Details page, if no locations are added to a channel. On the Tables page, you need to select one or more tables to enable the option, etc. When you hover over the disabled option, a tooltip will appear with an appropriate explanation.

The Refresh Data dialog allows you to choose specific locations and tables to be refreshed and configure different options to customize the refresh operation. For detailed information about each of the options available in the dialog, see section Refresh Options below.

Refresh Options

OptionDescription

Locations

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

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

No Initial Creation or Alter of Target Tables

Do not create any target new tables and keep the existing structure of the target tables (if any).

Only Create Target Tables that are Missing

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

Create Missing Target Tables and Alter or Recreate Tables with Incorrect Layout

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






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.

No Indexes

Do not create 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.

Recreate All Tables

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 Existing Structure

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.

Keep Old Rows on Recreate

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 Load - Table Granularity

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.

Repair - Row by Row Granularity

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. 

Keep Difference Files

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 Row Granularity mode (not available when option Bulk Load - Table Granularity is selected).

Online refresh consistency when selecting tables which are being changed

Select moment with Oracle flashback query

Select data from each table of source from 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 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 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).
  • 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 controls to affect replication of changes that occurred before and during refresh

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.

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


Contexts

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

For more information, see the concept page - Refresh and Compare Contexts.

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

Variables


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

Parallel Sessions

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.


Slicing

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.

Choose Table

Table for which the slicing is to be configured.

Choose Type

Types of slicing that can be applied to the table:

  • Modulo
  • Boundary
  • Count
  • Series

Choose Column


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.

This option is available when Modulo or Boundary slicing type is chosen.

Number of Slices

Number of slices the table will be divided into.

Boundaries

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

This option is available when Boundary slicing type is chosen.


Data Type

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

This option is available when Boundary slicing type is chosen.


Series Values

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

This option is available when Series slicing type is chosen.

Slice Selection

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.

You can choose only second and third slices so that the Refresh job is performed on rows with values 1000 to 5999.

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.


Slicing Suggestions

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.

Tuning Preferences

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

Max slices per table – sets the maximum number of suggested slices per table. By default, the number is set to 5.

Refresh Task Name

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

Thedefault task name is refr, so without this option, the Refresh jobs and scripts are named chn-refr-l1-l2.

Advanced Refresh Options


Foreign Key Constraints

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 next cycle of integration.

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

This option is supported/available only for certain location types. By default, the foreign key constraints are ignored for the unsupported location types. For the list of supported location types, see Disable/enable constraints check during hvrrefresh and Disable/enable foreign keys during hvrrefresh (option -F) in Capabilities.

Disable Triggers

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.

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 Equivalent HVR Command Line

The equivalent command in the command line interface (CLI) to those options configured/selected in the dialog.

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

Viewing Refresh Results

Clicking the Refresh Data button in the Refresh Data dialog will start the refresh job. You will see the following notification at the top of the page.

Once the refresh job is started, the following notification will appear at the top of the page. Click the View Refresh event link to open the Event Details page displaying detailed information about the refresh event.

On the Channel Details page, the refresh job state is displayed on the Jobs pane. To open the Event Details, click the More Options icon  related to the refresh job and select Go To Event

The image below displays a sample Event Details page showing the details of a refresh event.

  • The top pane displays information about the channel and location(s), for which the refresh event was done, the event state, the job name associated with the event, and the start time of the event.
  • The middle pane displays additional information related to the options configured in the Refresh Data dialog, such as granularity mode, online refresh options, and other.
  • The Results pane displays the refresh statistics for each table involved in the refresh event. The View file link opens the View Diff File dialog that allows to inspect a diff file containing the list of differences detected. Section Analyzing Diff File explains how to view and interpret the contents of the diff file. For detailed description of each parameter in the Results pane, see section Refresh and Event-Driven Compare Results.