<br**********@gmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...
What is CURRENT_DATE? Shouldn't you be using CURRENT DATE?
I questioned that as well but found examples using CURRENT_DATE and
CURRENT DATE so I'm a little confused.
Traditionally, DB2 on OS/390 and Windows/Linux/Unix used 'CURRENT DATE' (no
underscore) but a few versions back, some of the platforms and versions
started accepting 'CURRENT_DATE' (with underscore). Perhaps this is some
kind of a standards comformance thing or an attempt to make DB2's syntax a
little closer to that of some competitor; I really don't know. In any case,
I suspect that most current platforms and versions support both formats and
that they are completely interchangeable. You haven't stated which platform
and version you are using so I can only suggest that you check your SQL
Reference to see which format(s) will work on your system.
It will run faster if you skip the conversion to DATE on both sides of
the
equation.
So I don't need to convert the timestamp value to a date using the DATE
function?
I think Mark is suggesting you do this:
Delete from LOG_TABLE
where LOG_TS < CURRENT_TIMESTAMP - 21 DAYS;
On any reasonably current version of DB2 on any platform, you should be able
to subtract 21 days from a timestamp without difficulty. Then you avoid
having the date() scalar function on both sides of the predicate which, as
Mark says, should give you better performance.
For what it's worth, I wrote this little script and executed it on my system
(DB2 UDB V8.2 FP8 on Windows XP) and it worked fine; the only three records
returned by the select were the ones that were 21 days old or older:
----------------------------------------------------------------------------
---
drop table log_table;
create table log_table
(log_ts timestamp not null,
entry char(20) not null,
primary key(log_ts));
insert into log_table values
(current timestamp + 3 days, '3 days from now'),
(current timestamp + 2 days, '2 days from now'),
(current timestamp + 1 day, 'tomorrow'),
(current timestamp, 'today'),
(current timestamp - 3 days, '3 days ago'),
(current timestamp - 7 days, '7 days ago'),
(current timestamp - 14 days, '14 days ago'),
(current timestamp - 21 days, '21 days ago'),
(current timestamp - 22 days, '22 days ago'),
(current timestamp - 24 days, '24 days ago');
select * from log_table;
select * from log_table
where log_ts < current timestamp - 21 days;
----------------------------------------------------------------------------
---
The only reason I can see for you getting only newer records is that your
operator is the reverse of the one you've shown in your question, which
would be a simple typo/transcription error.
Rhino
