Skip to Content
0
Former Member
Feb 05, 2008 at 08:21 PM

Delete ODS records in Start Routine

9 Views

Hi,

I'm working on next scenario.

I have a zODS with overwrite aggregation and w/o Delta.

This zODS datasource is an Oracle table that I access througt DbConnect.

This Oracle table contains only one month of information. So I add month by month information in the ODS.

This Oracle table is deleted and filled for every load; and the ODS ought to have exactly same number of records than the Oracle table for the month loading.

So imagine:

Load 1

Time: 200701 Key: 1 Measure: 10

Time: 200701 Key: 2 Measure: 20

Load 2

Time: 200701 Key: 1 Measure: 15

Time: 200701 Key: 2 Measure: 20

Time: 200701 Key: 3 Measure: 10

Load 3

Time: 200701 Key: 2 Measure: 20

Time: 200701 Key: 3 Measure: 10

And this is the problem, how I delete record with Key: 1 from the ODS ? This record has disapeared from the Oracle table on Load # 3 and I want it out also from the ODS.

Is it posible to use sentence like DELETE FROM /BIC/zODS00 where month = YYYYMM in the Start Routine, before each load ? (Where YYYYMM is month being loaded).

Is this procedure usual or adviced ?

It exists a best solution ?

Thank you very much.

Points assigned.