How To: 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.
Scenario:
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. This kind of set up is desired if you want to have multiple copies of the same table in one location. Below we have already defined two locations orcl (Oracle database on Windows) which will be our source and orlx1 and orlx2 will be the target location.
Overview:
This can be achieved by having 2 targets (Example TGT1 and TGT2) pointing to the same target location. And a table can be renamed using base name and integrated and refreshed to the same location.
Prerequisites:
This example assumes the following steps have already been completed:
- Three locations have been defined
- orcl serves as the source location in this example
- orlx serves as the target location pointing to the target database for integrate in this example
- orlx2 serves as the target location pointing to the target database for refresh in the example
- A Channel has already been created
- chn1 serves as the name of the channel in this example
- A sample user table has already been created on source, but Local Data Processing will create the two target tables for you as part of the refresh job.
- A1 serves as the sample source table which you can substitute with your own table name
- Integrate actions are added to the channel for both the target groups TGT1 and TGT2. Even if TGT2 is solely responsible for refreshing the table on the target, integrate action is required on the group
Steps:
Step 1: Create source, target 1 and target 2 under Location Groups.
A] Right click in Location Groups and select New Group.
B] In the New Group Window select the location that is pointing to the source location and name the Group as SRC followed by clicking on ok so that this location shows up under the location groups
Step 2: Create the target locations TGT1 and TGT2 for the channel.
A] Right click in Location Groups and select New Group.
B] In the New Group Window select the location that is pointing to the target location (orlx) and name the Group as TGT1 followed by clicking on OK so that this location shows up under the location groups
C] In the New Group Window select the location that is pointing to the target location (orlx2) and name the Group as TGT2 followed by clicking on OK so that this location shows up under the location groups
Step 3] Create Capture action on source group, SRC.
A] Source will have action Capture which looks like below
chn1 SRC * * Capture
Step 4] Create TableProperties action for TGT1 group.
A] Apart from Integrate action on TGT1 group, action TableProperties is defined with /BaseName option to indicate the name table a1 will have at the target. In this case it is denoted as a1_tgt1 by the user.
The action will look as below.
chn1 TGT1 a1 orlx TableProperties /BaseName=a1_tgt1
Step 5] Create TableProperties action on TGT2 group.
A] Apart from integrate action on tgt2 group, action TableProperties is defined with /BaseName option to indicate the name table a1 will have at the target location. In this case it is denoted as a1_tgt2 by the user. The action will look as below.
chn1 TGT2 a1 orlx2 TableProperties /BaseName=a1_tgt2
Step 5] Create Scheduling action on TGT2 to schedule a refresh job so that there is no need to start refresh job manually.
A] Select Scheduling under New action for the group TGT2.
B] Select RefreshStartTimes option and click on the expand button which is highlighted next to it.
C] Select the intervals at which you want the refresh to take place. In our example refresh is scheduled to run once every hour every day. The action for that looks as below.
chn1 TGT2 * * Scheduling /RefreshStartTimes="0 * * * *"
Step 6] Perform Initialize
A] 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 group and TGT2 group. We will keep the integrate job for TGT1 and delete the integrate
job in TGT2 because our use case needs only to refresh table in TGT2
Step 7] Local Data Processing Refresh for the target group TGT1
A] Refresh for the target group TGT1
Right click on the Local Data Processing Refresh for the channel chn1 and select location orlx under Location in the window with the option Create Absent Tables and Always Recreate. We refresh the table a1 in target group TGT1 which is location orlx in our case so that it is in sync before we start the integration job.
B] This step will load the table a1_tgt1 in the Target database
Step 8] Local Data Processing Refresh for group TGT2 to schedule the refresh job for table a1
A] Right click on the Local Data Processing Refresh for the channel chn1 and select location orlx2 under Location in the window with the option Create Absent Tables and Always Recreate which will load the entire table on the target and check option Schedule Refresh Jobs to schedule this refresh job.
B] Next action will be to click on Schedule button below to create the refresh job and schedule it.
C] This will create 1 refresh job.
D] Below are the actions for this channel.
