Maintaining the DB2 table with only 30 days from SAS

[vickys]

Dear Guru's,

I am struck up with the logic in maintaining the DB2 table with only 30 days data.
In detail :
I will load the DB2 table through SAS Job daily. So, once the 31st day date is loaded then the first day data should be deleted or archived like FIFO( 30days).
so every time I should maintain only 30 days data.
How can I do that. Can some one please help with user written code.
Appreciate your inputs

Thanks,
Vicks

[Kurt_Bremser]

Without any data, I can only give you an abstract:

where date > today() - 30;

[vickys]

Thanks Kurt,

 Yes that works, but I am loading daily data into my DB2 table and I am looking to update this  table every day so, that the more than 30 days records gets deleted.

Thanks,
Vicks

[Kurt_Bremser]

---

@vickys wrote:

Thanks Kurt,

 Yes that works, but I am loading daily data into my DB2 table and I am looking to update this  table every day so, that the more than 30 days records gets deleted.

Thanks,
Vicks

---

That's exactly what the where condition does, it only keeps observations younger than 30 days. Apply it to your existing dataset:

data new;
set
  old (where=(date > today() - 30))
  import
;
run;

[LinusH]

@Kurt_Bremser I believe that @vickys doesn't want to reload all 30 days each day. Rather add today's data and drop the 31st day data, and not touching the other 29 days.
If data volumes are fairly small I would go the reload path as Kurt suggests.
Otherwise, you need some kind of DB2 syntax, like a partitioned table. This drives syntax, especially for creating and dropping partitions.

[Kurt_Bremser]

Ups, yeah, the OP is talking about an operation within DB/2. With DB/2 (or any DBMS), one can selectively delete the older observations, as that will really free the spaces (what you don't get with SAS, where space is freed only with a complete rewrite).

[vickys]

Yes @LinusH. ok so we can do that in DB2 not in sas DI you mean?

[LinusH]

You can do it from DI. but you probably need to use DB2 specific syntax.
Suggest that you first develop working code in DB2, then implement it in DI.