topic Re: Maintaining the DB2 table with only 30 days from SAS in New SAS User

Maintaining the DB2 table with only 30 days from SAS

vickys — Thu, 27 Jun 2019 08:54:20 GMT

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

Re: Maintaining the DB2 table with only 30 days from SAS

Kurt_Bremser — Thu, 27 Jun 2019 09:06:07 GMT

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

where date > today() - 30;

Re: Maintaining the DB2 table with only 30 days from SAS

vickys — Fri, 28 Jun 2019 08:39:33 GMT

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

Re: Maintaining the DB2 table with only 30 days from SAS

Kurt_Bremser — Fri, 28 Jun 2019 09:34:41 GMT

@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;

Re: Maintaining the DB2 table with only 30 days from SAS

LinusH — Sat, 29 Jun 2019 05:54:47 GMT

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

Re: Maintaining the DB2 table with only 30 days from SAS

Kurt_Bremser — Tue, 02 Jul 2019 12:42:32 GMT

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

Re: Maintaining the DB2 table with only 30 days from SAS

vickys — Wed, 10 Jul 2019 06:15:12 GMT

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

Re: Maintaining the DB2 table with only 30 days from SAS

LinusH — Thu, 11 Jul 2019 05:02:23 GMT

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.