Topic
IC4NOTICE: 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.
1 reply Latest Post - ‏2013-03-14T10:50:12Z by SystemAdmin
SystemAdmin
SystemAdmin
17917 Posts
ACCEPTED ANSWER

Pinned topic Need help to delete old data from table

‏2013-03-14T03:12:53Z |
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
    SystemAdmin
    17917 Posts
    ACCEPTED ANSWER

    Re: Need help to delete old data from table

    ‏2013-03-14T10:50:12Z  in response to SystemAdmin
    Hi,

    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.