Delete OLD files from Table not having date or timestamp

45 pts.
Tags:
AS/400
as400/DB2
DB/400
DB2
DB2/400
delete sql
DELETE statement
SQL
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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>

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Yorkshireman
    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 ?
    5,580 pointsBadges:
    report
  • philpl1jb
    Perhaps you can add the create date field, for future use.
    49,730 pointsBadges:
    report
  • RJES
    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.
    45 pointsBadges:
    report
  • Meandyou
    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...
    5,220 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following