cancel
Showing results for 
Search instead for 
Did you mean: 

Delta Load in CPI-DS

aalapati
Explorer
0 Kudos

Hi CPI-DS experts,

I am new to DS and looking to execute a task(using preload and postload scripts) for Delta loads.I have a source DATASTORE as ECC using tables .For the initial run , I got few records from ECC. I changed one single record in ECC and executed the task again. Now I got all the records(with changed record as well) , where as I am looking ONLY for the single changed record in the second run.

Can someone throw light here on fetching ONLY modified records from ECC in the second run.Is that something need to managed at ECC or in DS.

Thanks in advance,

Accepted Solutions (0)

Answers (1)

Answers (1)

jeffrey_kresse
Employee
Employee
0 Kudos

In general, CPI-DS needs external record of changed data, it doesn't hold that on its own. Data never really lives in CPI-DS, just passes through.

With tables, CPI-DS will need some kind of change history to work with. For instance, if there is a change date column in the table, we can use that to filter down to just records updated since the last job execution. For this purpose we have save_data() and get_data() functions. In the post-load script, use save_data(systime()), which will save the end time of this job for the next execution. In a filter in the dataflow, you must filter on a last changed date/time and use get_data() as the filter condition.

Help Portal is down for me at the time of this writing, but this use case is described in CPI-DS documentation.

Best regards,
Jeff K

aalapati
Explorer
0 Kudos

Hi Jeff,

I have a table in ECC (CHDHR) which holds the changed records data. I like to query on date and timestamp on that table.

Like I want to query from yesterday (09-10-2018 14:00:00 to 09-11-2018 14:00:00)

Appreciate your quick help,

Aswini.

jeffrey_kresse
Employee
Employee
0 Kudos

This should be supported, directions provided here.

Create the following preload script:

# Start date
if (get_data('<task_name>') = " or $G_RESET = 'Y')
  $G_STARTDATE = to_date('1900-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss');
else
  $G_STARTDATE = to_date(get_data('<task_name>'),'yyyy-mm-dd hh24:mi:ss');

# End date
if ($G_ENDDATE is null) 
  $G_ENDDATE = sysutcdate(); 

print('Using query period from [$G_STARTDATE] to [$G_ENDDATE]');

Create the following postload script:

print('Saving enddate for next startdate: [$G_ENDDATE]');
save_data('<task_name>',to_char($G_ENDDATE,'yyyy-mm-dd hh24:mi:ss'));

Then you can implement a filter on your date/time columns based on the values of $G_STARTDATE and $G_ENDDATE.

Regards,

Jeff K