select records from DB2 table that are not updated recently

95 pts.
AS 400
Is there a way to select records from a DB2 table that are not written/updated in last 2 years? This is for archiving purpose. 
From the data we cannot tell if the data is recent or not. We cannot rely on the RRN, old RRN records might have been updated recently. 
Purpose is to archive data which is not written or updated in last 2 years.

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 6  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.
  • ToddN2000
    Unless you have a date or timestamp in the data, I don't think you are going to be able to do it.  You would need something to compare the current date to in order to archive the data. What do you plan on doing with the data if it was identified, are you planning on deleting it from the table or flagging it in some way?  There may be some other possibilities like joining to another table. 
    Example if it's a part number / description you may want to archive, join the part to your sales table and check for a last sale date. If not sold in the past 2 years then archive the part.  If we knew more on the table set up and columns available , we might be able to provide other solutions. 
    136,970 pointsBadges:
  • nishav
    Thanks for the reply, Todd.

    There is no timestamp in the data. 
    Main table (say Table A) holds the details of PO, and table (A) is getting purged after 2 years. So the main table (A) only have the latest 2 year data. 
    Another table (say Table B) is used to keep the comments associated with PO number. Table B is never purged. 

    PO number can repeat after many years. PO number will still be unique in table A (as it only contain 2 year data), but it is not unique in table B, which is causing problems. Solution is to purge table B as well.  Note that PO number is an external data and it is not sequential number, and we cannot predict which PO will repeat.

    As a first step, I can identify all POs (in table B) that are not in Table A, and then purge these records (plan is to copy the data to a history table, and delete from table B).
    Also need to change the program (job) that archives Table A, to archive Table B as well. This will take care of all future records.

    The problem is with the repeated POs. There are some POs with one entry in Table A and more than one entry in Table B (only one record of table B will be current, and the rest will be old records). I cannot delete base on RRN, as some of the old records got updated when the PO is maintained (as the maintenance program always retrieve/update first matching record for the PO).

    These files are journalled. Can I retrieve data from journal to a temporary file and then join with Table B? I need to archive only the records that are updated 2 years before.

    95 pointsBadges:
  • ToddN2000
    @nishav: I'd check to see if there is a second column besides the PO# to use when yo do your join. Look for something else like a customer#, vendor#, warehouse, division to use in conjunction with the PO#. This may let you do the purge more accurately. 

    136,970 pointsBadges:
  • Splat
    Do your history files have a date column to indicate the archive date?
    12,935 pointsBadges:
  • carlosdl
    From the pure database standpoint, if the data itself doesn't provide a way to identify it by date, there is nothing you can do.

    I don't really know what "journalling" really means or how it works on that platform, but it sounds to me like the only thing that could probably be used for the purpose you describe, although, if none of the experts on that platform have paid attention to it or said anything about it, I'm probably wrong.
    86,030 pointsBadges:
  • TheRealRaven
    IF the files are journaled AND you have journal receivers on-line for the entire time period needed AND each row has a unique identifier, then it's possible. It might even be possible if you can't have all receivers on-line at the same time as long as each can be put on-line for processing.

    However, practically nothing can be extracted from the journal that isn't clear from the database itself except for (1) date/time an action was taken and (2) the sequence of actions. If rows don't have unique identifiers, a date/time won't help since you might not be able to link it to a unique row.
    37,225 pointsBadges:

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.

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


Share this item with your network: