How To Avoid Truncation of Table Data
Introduction
How to avoid truncation of table data in target from the previous sliced refresh job by the current sliced refresh job.
Description
This article describes the workaround to avoid the truncation of the data. This article assumes that the table is not present in the Target database.
Below is sample data for the primary key in the table that we want to perform a sliced refresh on.
4041139230, 4041139231, 4041139232, 4041139233, 4041139234, 4041139235, 4041139236, 4041139237, 4041139238, 4041139239, 4041139240, 4041139241, 4041139242, 4041139243
The current channel definition is as below.
Perform the following steps to schedule the refresh job.
Right-click the channel and select the HVR Refresh option. Select the options as shown on the image below.
Go to the Scheduling tab. The Slice Column should be the stable column. In our case li_ccf_id is the primary column. When Job Quota is set to 1, only a single job will run at a time.
Click the Schedule button to create the jobs. This will create 4 jobs.
Job testsliceref-refr0-ora-olx will refresh data till 4041139230
Job testsliceref-refr1-ora-olx will refresh data between 4041139231 and 4041139233
Job testsliceref-refr2-ora-olx will refresh data between 4041139234 and 4041139237
Job testsliceref-refr3-ora-olx will refresh data between 4041139238 and 4041139243
Issue
On the Target we see data only for the last job testsliceref-refr3-ora-olx (or which is run last out of the 4).
Resolution
To avoid this, there are 2 possible resolutions.
Option 1
Option 1 assumes that there is empty table on the target.
Add below action to the channel (if the data is being loaded for the first time)
CHANNEL GROUP TABLE LOCATION ACTION testsliceref TD_TARGET test_slice_tab * Restrict /RefreshCondition="0=1" /Context=slice Initialize the channel with Scripts and Jobs.
Recreate the refresh jobs using the following steps.
a. Right-click the channel and select HVR Refresh.
b. In the Scheduling tab, select the following options.
c. Under the Contexts tab, click the checkbox for slice.
The final channel configuration looks like below.
NOTE: This will ensure that the data from previous job is not deleted.
Option 2
Option 2 assumes that there is old data in the target database.
Change the Restrict /RefreshCondition so that it is applicable to both source and target groups.
CHANNEL GROUP TABLE LOCATION ACTION testsliceref * test_slice_tab * Restrict /RefreshCondition={hvr_var_slice_condition} /Context=slice Initialize the channel with Scripts and Jobs.
Create refresh job using steps a, b, and c in Option 1.
The final channel configuration will look like below.