Quick way to delete x number of records from a file

4055 pts.
Tags:
AS/400
I just found a history file that has over 57 million records and is taking up a good share of disk space. This is a file that contains 1 field per record of mixed data. There is nothing that resembles a time stamp of any form in the record. I would like to at least delete the first 50 million records (I have no idea how old some of this data is, but I have been here for 2 years and didn't even know the file existed. So I can't imagine the data is very important after a few weeks/days.) I know I could write a program to loop 50 million times and read/delete each record. But looking for something that might already be in place w/o creating this little program and perhaps be less of a resource hog while it runs.


Software/Hardware used:
OS V7.1
1

Answer Wiki

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

use truncate table command to delete all records in fraction of seconds

Discuss This Question: 16  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.
  • philpl1jb

    Create a copy with CPYF starting at 50000000

    Then copy back over original.

    - are there any logicals over file?


    54,090 pointsBadges:
    report
  • azohawk
    no logicals, but the file is in regular use and no way to predict when records will be added.
    4,055 pointsBadges:
    report
  • CharlieBrowne

    Is this file setup to use deleted records?

    If Yes, and new records are not alw2ays inserted at the end of the file, it will be more work to decide which records to keep. You may have to do SQL deletes based on contents on one or more data elements. NOTE: This doers not solve your issue with the disk space this file is using until you do a RGZPGM.  

    If not, the easiest way is to copy to a new file starting at a specific relative record number. Then copy that file back MBROPT(*REPLACE) back to the original file.

    You could use SQL to delete records with a RRN less that a specific value but you would still need to do a RGZPFM to free up the space if you use SQL

    62,385 pointsBadges:
    report
  • azohawk
    Found this on a web site: delete from LIBRARY/FILENAME A where RRN(A) = 3  but it is described as working for keyed files. This file is not keyed. I wonder if I change it to delete from LIBRARY/FILENAME A where RRN(A) < 50,000,000 would work?  (seperators added for readability.)
    4,055 pointsBadges:
    report
  • CharlieBrowne
    If the objective is to free up disk space, you will need to get a lock on the object to clear the file (or use MBROPT(*REPLACE) on CPYF.

    62,385 pointsBadges:
    report
  • azohawk

    This file only has one field, of a long bit of text, but none of it is a date. Yes, I would like to clear up some disk space (I believe that this file alone is using over 10% of the total disk space on the system.)  And I know that I need to do a RGZ after I delete records.

    Just looking for a way to perform the delete of that many records. (I can only view about 70 with runqry or SQL select before it crashes with "unviewable data" error.

    4,055 pointsBadges:
    report
  • TheRealRaven
    A SQL DELETE statement with a WHERE RRN(tablename)<50000000 clause could delete the rows, but it won't dree up space until the file is reorged.

    The deletions can be done while the file is in use, but RGZPFM in current OS releases needs consideration for LOCK() and ALWCANCEL() parameters.

    A CPYF with FROMRCD(50000000) will create a copy of just the records at the end of the file. The current number of deleted records might affect the choice of the FROMRCD() value.

    Again, it can be done while the file is active, but at some time the copy has to replace the original which requires exclusive access. And if rows are often being added, some might be lost at the end of the file between the time the copy is created and it replaces the original. It should only take a couple minutes to effect the physical replacement.

    No matter how it's done, at least a few minutes of exclusive access will be needed.
    35,650 pointsBadges:
    report
  • azohawk
    Thanks guys. Setting a jobschde to run the SQL statement on Saturday morning (no one works on Saturday) then in the evening I'll sign in to do a RGZPFM and can check for locks doing it manually.
    4,055 pointsBadges:
    report
  • ToddN2000
    Just back up the file to removable media. Are you sure this file is not being used for any reason? We have some file that have our historical data that go back to 1999. There might be very little activity as the are used at month or year end only. This is for tracking sales history. If I blew away data there would be trouble. You said it's one field in the record with no timestamp? Check for programs the use this file as input or write to it. That may give you a better idea of how you can clean the data up.
    133,750 pointsBadges:
    report
  • TheRealRaven
    It'd be nicer if the rows had a timestamp column. You could then schedule regular deletions based on age and also change the file to be REUSEDLT(*YES) if it's not already set that way. That would minimize future problems with it.
    35,650 pointsBadges:
    report
  • ToddN2000
    Check to see if any of this data can be linked to a file that may have a date field and delete based on that date. I find it hard to believe there is a file that large with data that nobody can figure out where it came form or how it's used.
    133,750 pointsBadges:
    report
  • azohawk

    I know where it comes from, just not what it is used for. I have a trigger program on the file now, to track reads. I modified the program that populates the file to not write to the add records during the hour each day the big hitters that were filling up the file. Somewhere along the line a timestamp was added to file (just not the earliest records). 

    The file was set up by someone two before me (and this being a 1 person iSeries shop...) the reason for the file appears to have not been communciated well. 

    The file itself is pretty much a single text field (1024 characters) record. Data would need to be split out in a data structure to read it (much if it appears to be packed.  Knowing what the data is (changes made to the item master), I really can't see a reason to keep records for more than a few days.

    The hardest part could be that after I get the OK to delete the older data is the reorg of the file to clearout the deleted records. The program that feeds the data into this file is always running when the system is active.  I have done some reading about reorg while active.

    4,055 pointsBadges:
    report
  • ToddN2000
    You could always sub string on where the date is now, and if it blank/zeros, it must be ancient history and you can delete it. The reorg will remove the deleted records but the file cannot be in use when the command is issued.
    133,750 pointsBadges:
    report
  • GregManzo
    Nobody seems to have considered what happens if the file is journalled. OK, unlikely if it is a historical archive, but worth checking just in case. If it is journalled then:
    Delete 50m records will write 50m entries to the journal. CPYF *REPLACE will write one entry for the clear up front & 7m entries for the records put back. Neither very good. An alternative approach:
    ALCOBJ *EXCL
    ENDJRNPF
    CPYF FROMRCD(50m)
    CPYF *REPLACE
    STRJRNPF
    DLCOBJ
    Of course if it isn't journalled then I'll just sneak away quietly..
    2,960 pointsBadges:
    report
  • GregManzo
    Nobody seems to have considered what happens if the file is journalled. OK, unlikely if it is a historical archive, but worth checking just in case.
    2,960 pointsBadges:
    report
  • ToddN2000
    Another thing you may want to consider is doing a RGZPFM to free up the deleted records. Even though you deleted them they still take up space. There are tools that will let you recover them when deleted if you have not done a RGZPFM. You need to make sure the file is not in use to run the command.
    133,750 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.

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

Following

Share this item with your network: