Manually Adapting a Channel for DDL Statements

Last updated on Jun 09, 2020

Contents

A channel needs to be adapted only when new table(s) are added to an existing channel, or when the definition of table(s) that were already being replicated have changed, or when tables are removed from a channel.

For certain databases, HVR continuously watch for Data Definition Language (DDL) statements (using action AdaptDDL) and automatically performs the required steps to adapt a channel. For the list of supported databases, see Log-based capture of DDL statements using action AdaptDDL in Capabilities.

For all other database sources that do not support capture of DDL statements using action AdaptDDL or if the action AdaptDDL is not defined, HVR only captures the DML statements - insert, update and delete as well as truncate table (modify to truncated) and replicates these to integrate database. But DDL statements, such as create, dropand alter table are not captured by HVR. In this scenario, to capture the DDL statements, the required steps to adapt the channel needs to be performed manually.

When DDL statements are used, the following must be considered:

  • These statements are not replicated by HVR, so they must be applied manually on both the capture and integrate databases.
  • The HVR channel which replicates the database must be changed ('adapted') so it contains the new list of tables and columns, and the enroll information contains the correct internal table id number.
  • For Ingres log–based capture, after an alter table statement an extra modify statement is needed to convert all the rows which are stored with the old column format. The statement is modify mytbl to reconstruct, or (assuming the old structure was a unique btreemodify to mytbl btree unique.

There are two ways to manually adapt a channel:

  1. Online Manual Adapt: This method is less disruptive because while performing this procedure users can still make changes to all tables. However this method requires you to perform more steps to adapt a channel.
  2. Offline Manual Adapt: This method is more disruptive because while performing this procedure users are not allowed to make changes to any of the replicated tables. However this method requires you to perform fewer steps to adapt a channel. This method is preferred when the application (e.g. SAP or Oracle eBusiness Suite or any other similar major application) is not making any changes to the database because of a planned downtime (e.g for application upgrade).

The steps mentioned in the following sections do not apply for trigger-based capture and bi–directional replication (changes travelling in both directions). For such situations, contact HVR Technical Support for minimal–impact adapt steps.

Online Manual Adapt

Perform the following steps to manually adapt a channel using Online Manual Adapt method:

  1. Suspend the capture jobs, wait until the integrate jobs have finished integrating all the changes (so no transaction files are left in the router directory) and then suspend them too.

    hvrsuspend hubdb chn-cap
    hvrstart -w hubdb chn-integ
    hvrsuspend hubdb chn-integ

  2. Run the SQL script with the DDL statements against both the source and target databases, so that database schemas become identical.
  3. Manually adapt the channel definition so it reflects the DDL changes. This can be done in the HVR GUI or on the command line. In the HVR GUI select option Table Explore and connect to the capture database to incorporate the changes in the channel definition.
    1. Use Add to add the tables that are Only in Database or In other Channel.
    2. Use Replace to change the definition of the tables that have Different Keys, Different Columns or Different Datatypes.
    3. Use Delete for tables that are Only in Channel.
  4. Use Table Explore to the integrate locations to check that all the tables have value Same in the Match column.
  5. Execute HVR Initialize to regenerate the Table Enrollment information, the replication Scripts and Jobs, and enable DBMS logging (Supplemental Logging) for the new tables in the capture database. 



    This can also be executed on the command line as follows:

    hvrinit -oelj hubdb chn

  6. Execute HVR Refresh to synchronize only the tables that are affected by DDL statements (except for tables that were only dropped) in the SQL script in step 2. Tables which were only affected by DML statements in this script do not need to be refreshed. It is also not necessary to refresh tables which have only had columns added or removed.

    • For the first target location, the Online Refresh option Skip Previous Capture and Integration (–qrw) should be used to instruct the capture job to skip changes which occurred before the refresh and the integrate job to apply any changes which occurred during the refresh using resilience.


      This can also be executed on the command line as follows:

      hvrrefresh -gb -qrw -r src -l tgt1 -t dm51_product -h oracle hvrhub/!{Qb/Q.KqR}! hvr_demo
    • For any extra target location(s), use the Online Refresh option Only Skip Previous Integration (–qwo) because the capture job should not skip any changes, but the integrate jobs should apply changes which occurred during the refresh using resilience.

      This can also be executed on the command line as follows:

      hvrrefresh -gb -qwo -r src -l tgt2 -t dm51_product -h oracle hvrhub/!{Qb/Q.KqR}! hvr_demo

    For an Ingres target database, performing bulk refresh (option -gb) will sometimes disable journaling on affected tables. If Hvrrefresh had displayed a warning about disabling journaling then it is necessary to execute the Ingres command ckpdb +j on each target database to re-enable journaling.

  7. If any fail tables exists in the integrate location(s) (/OnErrorSaveFailed) for the tables which have had columns added or dropped, then these fail tables must be dropped. For this, execute HVR Initialize with Change Tables option (-oc) selected.

    hvrinit -oc -l int1 -l int2 -t tbl1 -t tbl2 hubdb chn 
  8. Start the capture and integrate jobs:
  9. If the channel is replicating to a standby machine and that machine has its own hub with an identical channel running in the opposite direction, then that channel must also be adapted by repeating steps 3, 5 and 7 on the standby machine.

Offline Manual Adapt

Perform the following steps to manually adapt a channel using Offline Manual Adapt method:

  1. Start downtime. Ensure that users cannot make changes to any of the replicated tables.

    It is recommended to wait for the capture and integrate jobs to process all outstanding changes before performing hvrsuspend in the next step. If waiting is not feasible (in case long time is required for the capture and integrate jobs to process all outstanding changes), then any out of sync issues can be resolved with the HVR Refresh performed in step 8.

  2. Suspend all jobs in the HVR Scheduler.

    This can also be executed on the command line as follows:

    hvrsuspend hubdb chn
  3. Execute HVR Initialize to deactivate the replication. Select Drop Objects and then select all options in Object Types.

    This can also be executed on the command line as follows:

    hvrinit -d hubdb chn 
  4. Run the SQL script with the DDL or DML statements against both the source and target databases.
  5. Use the HVR GUI Table Explore connected to the captured database to incorporate the changes in the channel definition.
    1. Use Add to add the tables that are Only in Database or In other Channel.
    2. Use Replace to change the definition of the tables that have Different Keys, Different Columns or Different Datatypes.
    3. Use Delete for tables that have Only in Channel.
  6. Use Table Explore to the integrate locations to check that all the tables have value Same in the Match column.
  7. Execute HVR Initialize with all options in Object Types selected to reactivate the replication:

    This can also be executed on the command line as follows:

    hvrinit hubdb chn 
  8. Execute HVR Refresh to synchronize all tables that are affected by the SQL script in step 4 (except for the tables that were only dropped). This includes tables that were also affected by DML statements in this script.

    hvrrefresh -r src -t tbl1 -t tbl2 hubdb chn 

    The –t options can also just be omitted, which will cause all replicated tables to be refreshed.

    For an Ingres target database, performing bulk refresh (option -gb) will sometimes disable journaling on affected tables. If Hvrrefresh had displayed a warning about disabling journaling then it is necessary to execute the Ingres command ckpdb +j on each target database to re-enable journaling.

  9. Unsuspend the capture and integrate jobs:

    This can also be executed on the command line as follows:

    hvrsuspend -u hubdb chn 
  10. Finish downtime.
  11. If the channel is replicating to a standby machine and that machine has its own hub with an identical channel running in the opposite direction, then that channel must also be adapted by repeating steps 3, 5, 6, 8 and 9 on the standby machine.

Trigger-Based Capture

Action AdaptDDL cannot be applied with trigger-based capture. In this case, the channel needs to be manually configured to perform trigger-based capture involving DDL statements. The steps defined for the Offline Manual Adapt method above are also applicable to the trigger-based capture with DDL statements involved. Note that while performing the steps for the trigger-based capture, in steps 3 and 7, all options (in Object Types) should be selected in the HVR Initialize dialog.