How To Avoid Truncation of Table Data

Question

How can I avoid truncation of table data in target from the previous sliced refresh job by the current sliced refresh job?

Environment

HVR 5

Answer

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.

1. Right-click the channel and select the HVR Refresh option. Select the options as shown on the image below.

   

2. 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.

   

3. 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.

1. 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

2. Initialize the channel with Scripts and Jobs.

3. 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.

1. 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

2. Initialize the channel with Scripts and Jobs.

3. Create refresh job using steps a, b, and c in Option 1.

   The final channel configuration will look like below.