How to Replicate Single Table to Two Tables on Target

Question

How can I create a channel to replicate a single table to two tables on the target, one for periodic refresh-only and the other for continuous integration?

Environment

HVR 5

Answer

This article describes the detailed steps to create a channel that replicates a single source table to two different target tables using 2 targets pointing to the same location. You need this kind of setup if you want to have multiple copies of the same table in one location. We have already defined two orcl locations (Oracle database on Windows) below which is our source, and orlx1 and orlx2 are the target locations.

Overview

You can achieve this by having 2 targets (For example, TGT1 and TGT2) pointing to the same target location. You can also rename a table using the base name and integrated and refreshed to the same location.

Prerequisite

We assume in this example that the following steps have already been completed:

1. The following three locations have been defined in this example:
   • orcl serves as the source location.
   • orlx serves as the target location pointing to the target database for integration.
   • orlx2 serves as the target location pointing to the target database for refreshing.
2. A channel has already been created and chn1 serves as the name of the channel in this example.
3. A sample user table has already been created on the source, but HVR will create the two target tables for you as part of the refresh job.
   • Table a1 serves as the sample source table which you can substitute with your own table name.
4. You can add Integrate actions to the channel for both the target groups TGT1 and TGT2. Even if TGT2 is solely responsible for refreshing the table on the target, you require integrate action on the group.

Steps

1. Create source locations target 1 and target 2 under Location Groups.

   1. Right-click Location Groups and select New Group.

      

   2. In the New Group window, select the location pointing to the source location and enter SRC as the Group Name.

      

   3. Click OK. This location shows up under the location groups.

2. Create the target locations TGT1 and TGT2 for the channel.

   1. Right-click Location Groups and select New Group.

      

   2. In the New Group window, select the location pointing to the target location (orlx) and name the Group as TGT1 and then click OK. This location shows up under the location groups.

      

   3. In the New Group window, select the location pointing to the target location (orlx2) and name the Group as TGT2 and then click OK. This location shows up under the location groups.

      

3. Create Capture action on source group, SRC. Source will have action Capture which looks like the following.

   Channel	Location Group	Table	Location	Action
   chn1	SRC	*	*	Capture

4. Create the TableProperties action for TGT1 group.

   Apart from Integrate action on TGT1 group, we define action TableProperties with /BaseName option to indicate the name table a1 has at the target. In this case, we denote it as a1_tgt1 by the user. The action looks like the following:

   Channel	Location Group	Table	Location	Action
   chn1	TGT1	a1	orlx	TableProperties /BaseName=a1_tgt1

   

5. Create the TableProperties action on the TGT2 group.

   Apart from integrate action on tgt2 group, we define action TableProperties with /BaseName option to indicate the name table a1 has at the target location. In this case, we denote it as a1_tgt2 by the user. The action looks like the following.

   Channel	Location Group	Table	Location	Action
   chn1	TGT2	a1	orlx2	TableProperties /BaseName=a1_tgt2

   

6. Create Scheduling action on TGT2 to schedule a refresh job so that there is no need to start the refresh job manually.

   1. Select Scheduling under New action.

      

   2. Check the RefreshStartTimes box and click the expand button next to it.

      

      

   3. Select the intervals at which you want the refresh to take place. For example, refresh is scheduled to run once every hour every day. The action for that is a follows.

      Channel	Location Group	Table	Location	Action
      chn1	TGT2	*	*	Scheduling /RefreshStartTimes="0 * * * *"

7. Perform Initialize. Right-click on the channel and select Initialize with the default settings.
   This will create 3 replication jobs one for capturing from source database SRC and two for integrating into target database TGT1 and TGT2 groups. We keep the integrate job for TGT1 and delete the integrate job in TGT2 because our use case needs to only refresh table in TGT2.

   

8. Run HVR Refresh for the target group TGT1. Refresh for the target group TGT1.

   1. Right-click channel chn1 and select HVR Refresh, then select orlx as the location.

   2. Select Create Absent Tables and then select Always Recreate.

   3. Refresh the table a1 in target group TGT1 which is location orlx. This is to sync before we start the integration job.

      

   This step will load the table a1_tgt1 in the target database.

9. Run HVR Refresh for the target group TGT2.

   1. Right-click the channel chn1 and select HVR Refresh, then select orlx2 as the location.

   2. Select Create Absent Tables and then select Always Recreate.

   3. Check Schedule Refresh Jobs to schedule this refresh job.

   4. Click Schedule to create the refresh job and schedule it.

      

      This will create a refresh job.

      

      The following are the actions for this channel.