NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
This topic has been locked.
1 reply Latest Post - 2013-03-14T10:50:12Z by SystemAdmin
Pinned topic Need help to delete old data from table
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Can someone help me how to delete old data from table in database. I want to delete the data that more than 31 days from the table. I dont know what command I should use.
Updated on 2013-03-14T10:50:12Z at 2013-03-14T10:50:12Z by SystemAdmin
SystemAdmin 110000D4XK17917 PostsACCEPTED ANSWER
Re: Need help to delete old data from table2013-03-14T10:50:12Z in response to SystemAdminHi,
By default, DB2 does not store information in the tables about when the rows were inserted or updated. It really depends on your database design/model, and you must have a date reference column in order to do what you want.
For inserted rows:
Is there any column with a date or timestamp value on it that registers when it was inserted?
For updated rows:
Is there any column with a date or timestamp value on it that registers when it was updated?
For accessed rows, there is not any option.
If you have a column with that information, first you write a select to preview the data, and once you have the values that you want to delete, transform the select into a delete.
select * from table1 where days (current timestamp) - days(ins_date) >= 31 delete from table1 where days (current timestamp) - days(ins_date) >= 31
In order to allow this functionallity in the future, you can use Time Travel. This feature will store automatically when a row was inserted and updated.