Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

To delete Data older than 60 days from any table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
raak

Active User


Joined: 23 May 2006
Posts: 166
Location: chennai

PostPosted: Thu Feb 15, 2007 3:18 pm    Post subject: To delete Data older than 60 days from any table
Reply with quote

Hi

Is it possible to delete data older than 60 days from a table if the table does not have any date columns?
Back to top
View user's profile Send private message

vijikesavan

Active User


Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA

PostPosted: Thu Feb 15, 2007 11:53 pm    Post subject: Re: To delete Data older than 60 days from any table
Reply with quote

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
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Fri Feb 16, 2007 12:12 am    Post subject:
Reply with quote

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..... icon_confused.gif
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Feb 16, 2007 12:49 am    Post subject:
Reply with quote

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. . .
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Feb 16, 2007 12:51 am    Post subject:
Reply with quote

Ooops. . . icon_redface.gif

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


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

Sorry 'bout that icon_neutral.gif
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Fri Feb 16, 2007 7:44 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Feb 16, 2007 8:22 am    Post subject:
Reply with quote

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. icon_smile.gif
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Fri Feb 16, 2007 10:21 am    Post subject: Re: To delete Data older than 60 days from any table
Reply with quote

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,

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


I was surprised because even with adding a new timestamp column, there is no way the user can start deleting records until 60 days from now are over.

Anyway, I am sorry for any confusion and as I understand, I have tons to learn from all you guys. So please don't get mad at me and cheers!
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Data replication from multiple Db2 ta... kishpra DB2 5 Mon Mar 27, 2017 9:58 pm
No new posts Export flat file data into excel sheet murali.andaluri DFSORT/ICETOOL 2 Mon Mar 20, 2017 5:39 pm
No new posts Append data from two files into a sin... Praveen04 DFSORT/ICETOOL 5 Thu Mar 16, 2017 7:29 pm
No new posts SPUFI -- Joining 3 tables – data in... Sysaron DB2 2 Wed Mar 08, 2017 4:18 am
No new posts Overriding PS data in rexx Shaheen Shaik CLIST & REXX 8 Fri Mar 03, 2017 5:08 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us