Delete OLD files from Table not having date or timestamp
Hi,
In DB2, Is it possible to get the insert date of a record in a table which doesn't have a seperate field for creation date or timestamp.
I have a task where I have to delete record older than 1 year from a table, but the files doesn't have creation date field.
So, like RRN does DB2 have any other functionality to track the record insertion?
Thanks in advance for your replies.
Software/Hardware used:
Software/Hardware used:
ASKED:
July 3, 2009 10:46 AM
UPDATED:
July 30, 2009 1:47 PM
Answer Wiki:
I'm not aware of any method that automagically keeps track of that information.
If you do not see a field(s) that relates to a create Date/Time stamp, then have a problem.
Are you journaling the file? That would keep track of changes made to the file.
Use DSPFD and search for 'jour'.
<pre> File is currently journaled . . . . . . . . : No </pre>
Phil
Reminder: If the file was created with reuse set to *YES, then the RRN sequence for inserts is no longer valid.
<pre>
Reuse deleted records . . . . . *YES *YES, *NO
</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
This is a pretty fundamental design requirement, and if it wasn’t thought about when the file was created, then you need to find another way of approaching the problem.
A journal would be the most obvious way of managing it. As journals tend to fill and be removed rapidly, you may need a journal for this file only. Build a journal reader to log usage of specific records into another file by having key and last use date and in a years time, you have the answer.
Is it high traffic? is it merely a reference table, is it huge? millions of records? coupla hundred ?
Perhaps you can add the create date field, for future use.
Hi,
Yes It’s a creation date and time for a transaction file is a fundamental requirment,
The file that I was referring is a transaction file for Java where, it’s SQL file having 34 fields of 30 char type.
Java program that runs in AS400 dynamically writes all the transaction details with a code type. But some records like error info the record has no date associated with it.
The file is attached to a journal and we have added creation date and timestamp for the past 5 months but the records created before that have no values for that field’s.
I’ll try to accomplish with journal, thanks for the comments.
Every table (or virtually every) should have date and time of row added, who added it, date and time or row modification, and who modified it. DB2′s TIMESTAMP is wonderful. Next comes the discussion of historical information after a row has been modified more than once…