Home > IT Answers > AS/400 > Delete OLD files from Table not having date o...
RJES
45 pts.
0
Q:
Delete OLD files from Table not having date or timestamp
AS/400, as400/DB2, DB2/400, DB2, DB/400, DELETE statement, delete sql, 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.
ASKED: Jul 3 2009  10:46 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
PSMurray
290 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
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'.
 File is currently journaled . . . . . . . . :            No



Phil
Reminder: If the file was created with reuse set to *YES, then the RRN sequence for inserts is no longer valid.

 Reuse deleted records  . . . . .   *YES           *YES, *NO
Last Answered: Jul 3 2009  12:45 AM GMT by PSMurray   290 pts.
Latest Contributors: Philpl1jb   24080 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Yorkshireman   3200 pts.  |   Jul 3 2009  2:41PM GMT

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 ?

 

Philpl1jb   24080 pts.  |   Jul 5 2009  4:51PM GMT

Perhaps you can add the create date field, for future use.

 

RJES   45 pts.  |   Jul 6 2009  5:48AM GMT

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.

 

Meandyou   1795 pts.  |   Jul 30 2009  1:47PM GMT

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…

 
0