To delete Data older than 60 days from any table

raak · Posted: Thu Feb 15, 2007 3:18 pm

Hi

Is it possible to delete data older than 60 days from a table if the table does not have any date columns?

vijikesavan · Posted: Thu Feb 15, 2007 11:53 pm

hmm. interesting.
Is it one time delete? or recrusive?
Can you elaborate more on your requirement and the columns in the table?
Do you have something like a Key field which follows a specific pattern during every insert?

I am assuming something like this:

Say FieldA exists and it follows a pattern like gets incremented on every insert. Then you can possibly write a query to delete records less than that value which it could have possibly 60 days before.
This is just my assumption. You need to know the DATA in the table, the pattern , the business reason etc....and decide on the logic.

Otherwise, I don't think there is any straight forward method to do this.
Folks, correct me if I am wrong.
If something like that exists, pls post it.

Thanks,
Viji

William Thompson · Posted: Fri Feb 16, 2007 12:12 am

I see it as a simpler query, I'd guess the poster is looking for a magic hidden internal timestamp that might be used to purge old data.....

dick scherrer · Posted: Fri Feb 16, 2007 12:49 am

Hello,

Well, it is not magic or hidden, but there is timestamp data type in DB2.

If this was defined for each table that needs to have data older than 60 days, it should be a simple "delete from" query. . .

dick scherrer · Posted: Fri Feb 16, 2007 12:51 am

Ooops. . .

wanderer · Posted: Fri Feb 16, 2007 7:44 am

But raak already says that there is no date field in the table!

Anyway raak, if an imagecopy is taken everyday in your place, then you could do some tedious process to somehow compare and decide what to delete.

Probably what you can do now is, add a column called date to your table with current date as default. Then wait for 60 days. And then start your delete process.

dick scherrer · Posted: Fri Feb 16, 2007 8:22 am

Hello wanderer,

Yes, the original post was clear that there was no date field in the table.

Though i did not actually specify a field could be added, my meaning was that adding a field with a data-type of timestamp might be a workable solution. And no, it would not have to be the timestamp data type.

You even arrived at the same suggestion after your thought about "some tedious process".

What you probably didn't realize was that my post was 2-fold - part 1 was to try to help raak, part 2 was a reply for Bill. We try to have a bit of fun while we're at it.

wanderer · Posted: Fri Feb 16, 2007 10:21 am

Hi Dick,

William was being a little sarcastic about the silliness of the question when he said about "hidden internal timestamp". Because there is no such thing that can be used here.

So when you said,