Introduction

The HVR Initialize dialog by default performs its steps for all locations, and by default all advanced options that are relevant for the channel. These defaults are perfect for the first implementation/initialization of the channel but rarely are these the right defaults to use when the channel was initialized before. This note explains every option in HVR Initialize and when it should be used.

Any objects hvrinit (previously hvrload) creates (e.g. state tables) will be created in the default schema for the user used to connect to the location.

Please note that HVR Initialize in version 5 was renamed from HVR Load in earlier versions. The command hvrinit in HVR 5, mentioned in this post, used to be hvrload.

"Create or Replace Objects" or "Drop Objects"

The default selection for hvrinit is to create or replace objects generally resulting in objects to be created or recreated and the system to be initialized.

However, objects can be dropped using the radio button Drop Objects. Use the option Drop Objects to remove a channel or a location out of an environment knowing that objects will be dropped only based on the current definition of the channel. For example, if history tables were created due to the CollisionDetect action then these tables will only be dropped if the CollisionDetect action is still part of the current channel definition. It is always possible to manually clean up an environment if a channel should be removed based on generated database object names and folder structures. For the object names, also see Naming of HVR Objects

Inside Database Locations in the User Manual.

Please note that supplemental log groups on an Oracle capture location will not be dropped because HVR makes no assumptions about whether other tools may be taking advantage of the supplemental logging that it may or may not have put in place. On a SQL Server capture location CDC tables that were created by

HVR will be dropped.

Tab Locations

By default all locations are checked, so by default, the selected advanced options apply to all locations. Many of the advanced options can be re-run without affect on the replication setup but hvrinit will finish faster if it only runs for a limited set of locations.

The recommendation is to check only the location(s) that are affected by a change to the channel. E.g. for a change to the group representing the capture side, only select the source location(s). For a change only affecting the integrate side, only check the target location(s). If a new location was added then only run hvrinit for the new location.

Tab Advanced Options

Depending on the actions in the channel only a few of the advanced options are applicable. HVR automatically disables options that are not relevant to the channel based on the channel definition. For example Database Triggers is disabled on channels that use log-based capture.

Below are the individual advanced options, their meaning, and whether or not they should be checked when running HVR Initialize.

State Tables

State Tables are tables HVR uses for processing purposes and are only relevant for database locations.

In a source database location, state tables are only created for trigger-based capture scenarios. One toggle table per channel is created called hvr_togchn, and one sequence table called hvr_seq_chn. Log-based change data capture does not use state tables on the source database.

In the target database location, the table names are hvr_stbu_chn_loc, hvr_stin_chn_loc and hvr_stis_chn_loc (with the channel name substituted for chn and the location name for loc). The state tables contain commit time and transaction information and get updated every time HVR applies transactions to this location, to ensure no transactions are lost but none are applied more than once. Generally, once state tables have been created they don't have to be re-created, but if the checkbox is checked then they will be re-created, and the state data kept in the tables is lost. If it is important to keep the state of the integrate jobs in the database. Do not recreate the state tables if an error occurred such as the network connection between the hub and the destination was temporarily lost, or if a capture rewind was performed yet transactions should not be applied again to the target.

Change Tables

The option for Change Tables is available if a channel uses trigger-based capture on the source, if a database target uses Integrate /OnErrorSaveFailed (previously DbIntegrate /OnErrorSaveFailed), or if the action CollisionDetect is enabled. Depending on the use case the option Change Tables may only source database or target database locations.

In the source database location(s) the option Change Tables creates two tables per table in the channel. Log-based change data capture does not use change tables on the source.

In a target database location if error tables exist (as a result of Integrate /OnErrorSaveFailed) then the error tables will be dropped with the option Create or Replace Objects selected (filtered based on the tables checked in the list). Error tables are only created when the first error occurs, and not during hvrinit. If the

CollisionDetect action is defined then the history tables will be created or recreated if they already exist. If it is important to keep old error rows or history of changes for active/active environments then make sure to uncheck the option Change Tables when running hvrinit.

This option will also drop burst tables (tables that end with __b) in the target database that were created as a result of using Integrate /Burst. If the integration is in the middle of a burst cycle, i.e. changes were moved into the burst tables, but changes were not yet applied to the target tables, then the option Change Tables will drop data that impacts HVR's ability to recover.

Database Triggers

Database Triggers is only relevant for database capture scenarios that do have the option Capture /TriggerBased checked (or, in earlier releases DbCapture without /LogBased checked), and only affects the source database locations. Note that the triggers call database stored procedures, created by the respective option.

Database triggers have to be recreated every time the definition of a table changed. Re-creating triggers is never a problem but only time-consuming if otherwise unnecessary. Filter the list of tables down to only the table(s) that require an updated trigger definition when (re)creating triggers for trigger-based capture.

If the option for Database Triggers is enabled but the intent is to perform log-based capture then revisit the channel definition because there may be an error in the capture action.

Trigger-based capture is only available on Oracle, SQL Server and Ingres.

Database Procedures

The option Database Procedures is relevant for the source if trigger-based capture is used, and on the target, if the option Integrate /DbProc (prior to version 5 DbIntegrate /DbProc) is enabled. HVR (re)generates the programming code for the stored procedures that are called by triggers for trigger-based capture, and by the integrate job for scenarios using Integrate /DbProc. Database procedures must be (re)created every time a table definition changes. Filter for only the table(s) changed to speed up the creation/compilation of the stored procedures. Note that for trigger-based capture the database procedures require the state tables on the source database, and if the database is Oracle then the user for the location must have execute privileges on DBMS_ALERT. Without these related objects and privilege the generated programming code will fail to compile resulting in error messages.

Integrate /DbProc is only available for targets Oracle, SQL Server and Ingres. The procedures rely on the table to exist (with the correct definition in sync with the table definition in the channel) in order to compile successfully. For every table, three database procedures are created for insert, update, and delete unless the table has no primary key in which case only delete and insert procedures are created (and updates are executed as a delete of the old row followed by an insert of the new row).

Transaction Files and Capture Time

For a source database location, the option Transaction Files and Capture Time will (re)create the capture state file in $HVR_CONFIG/router/hub/chn/loc_loc. HVR will start capturing transactions that modify tables in the channel after the initial capture time. By default the capture time is current.

Use the Rewind option to go back to an earlier point in time if the table definitions at the earlier point in time were identical to the current table definitions (matching the definitions in the channel) and if supplemental logging was enabled on the tables at the earlier point in time. Also, old transaction log files must still be available to rewind back to an earlier point in time. Capture rewind is not available for trigger-based capture.

Starting with HVR 5 there are new options to capture rewind to start sending changes to the destination locations. The options are:

• Rewind time i.e. immediately.
• A specific time that is relevant e.g. based on when a backup was captured.
• The hvr_tx_seq for advanced users who can find the original representation of the database system commit number in the hvr_tx_seq (which is a hex string).
• The Oracle SCN, for scenarios in which an RMAN backup (consistent as of an SCN) or an export using a flashback query as of an SCN was used to perform the initial load.

Resetting the capture time is often not desirable because any open transactions that HVR may have been tracking will be lost. If the capture is reset then typically a database refresh has to be run in order to re-synchronize tables if the capture time was reset. HVR will give a warning if the capture time is reset. Uncheck the option Transaction Files and Capture Time to prevent the capture time being reset.

The option Transaction Files and Capture Time does not affect a database target location.

Table Enrollment

The option Table Enrollment is only relevant for source database locations that use log-based capture. HVR generates an enroll file in $HVR_CONFIG/router/hub/chn/loc_loc listing all tables in the channel, their database object identifiers, and column information in order to perform log-based change data capture. The information to generate this file is queried out of the database dictionary. Check the option Table Enrollment every time table definitions in the channel definition have changed to ensure the enroll information is up to date.

Regenerating table enrollment is always done for the entire channel and not just for one table, so if no table definitions changed then uncheck this option, especially if the channel includes many tables in which case obtaining the enroll information may take some time (also depending on the database speed).

Supplemental Logging

Supplemental logging is required to ensure table updates can be replicated correctly using SQL statements on the target database. Supplemental logging ensures that for every update to a row the database includes (at least) the primary key column data in the log. Different databases use different mechanisms and terms to enable supplemental logging.

The option Supplemental Logging is only relevant for source database locations when log-based capture is used. This option will enable supplemental logging on source tables as needed depending on the channel definition. HVR will implement as granular supplemental logging as possible but many options in the channel, as well as the features of the database, determine whether full supplemental logging on all columns is required, or only for a subset of the columns e.g. the primary/unique key.

Note that on Oracle the option Supplemental Logging in conjunction with Drop Objects will not drop the supplemental logging since HVR does not know if other software relies on the supplemental logging HVR may or may not have created.

Validating whether the correct supplemental logging is in place can take a significant amount of time – proportional to the number of tables in the channel – so if no tables have been added to the channel and no tables where dropped and recreated in the source database then uncheck this option when running hvrinit. If only a few tables were added and others already had supplemental logging added then consider filtering the list of tables to only the tables that still need supplemental logging to speed up running hvrinit.

Scripts and Jobs

The option Scripts and Jobs registers the jobs under the HVR scheduler (and in the respective database tables in the HVR hub database). A job file is generated for a source database location in $HVR_CONFIG/job/hub/chn. For a target database location a .cache file is generated in $HVR_CONFIG/router/hub/chn/catalog.

Scripts and Jobs always have to be recreated after a change was made to the channel.

File Location State

The option File Location State is only relevant for file system locations. This option resets the directory _hvr_state in a file location.

Additional Notes

Can you explain start emitting time? You say you should never rewind. What scenarios could you rewind and not have to reset? Is there a way to use Initialize to get things back on track without requiring a Refresh, after the system sequence number (DB2) gets reset when the server is restarted?.

Answer : Emit time refers to when changes will be sent from capture to integrate. Emit time could be different than capture start time for a system with long-running transactions. HVR will only start capturing changes against tables in the channel from the capture time forward, but of course in order to capture long-running transactions in the system in their entirety you may want to start capture earlier, and emit only from some point forward. ERP systems on the Oracle Database often have long-running transactions in the database, but long-running transactions are not common on many other databases.

There are some scenarios when a capture rewind is checked as part of initialize, but a "reset" (i.e. refresh) is not required. For example, if the source system database is upgraded from one version to another whilst the application is down. A database upgrade often results in a lot of transaction log changes and irrespective of whether HVR could read through these changes without any issues it may just be cleaner to suspend the capture during the upgrade. Also, it is not unlikely that the database will be restarted as part of the upgrade, possibly more than once. Then post upgrade you would perform a capture rewind to current (or to the point in time post upgrade but before the application starts) to skip over the transaction log changes during the upgrade. In such a scenario you would know that you didn't miss any application changes. Another example would be an application upgrade with downtime during which, depending on the upgrade, there would also be no changes getting applied to the tables you want to replicate. Of course in some cases during an application upgrade table changes are performed (DML and/or DDL) that may or may not need to be reflected as part of the replication.

If old transaction logs are still accessible on the server post a restart then I would expect HVR to be able to continue to run through the old sequence of transaction logs until it needs to resume back at the reset point. I suspect that as it stands we require a re-initialize when getting to the point when the reset happens to be able to continue. If transaction logs from before the server restart are no longer available then you will likely require a refresh to get the data back in sync. You could always use compare to identify the damage which, depending on the change rate on the application, may be more or less difficult to achieve.