Reading only the active records in a flat file

190 pts.
Tags:
AS/400 flat file
RPG
RPG ILE
RPG IV
RPG/400
RPGILE
RPGLE
I have a physical flat file which has thousands of deleted records. (I can't get exclusive use of the system to do a reorg) I am doing a sequential read of the file because the file only has a few active records. However, it is taking the program awhile to finish and I'm guessing it is because the program is having to read all the deleted records as well. Is there a way to get the program to read only the active records?

Software/Hardware used:
AS400 V5R4

Answer Wiki

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

If there is maintenance to the file, you could CHGPF to REUSEDLT(*YES). Only do this if arrival sequence is not important to you.

Another option is to create a LF over th PF. That will not include the deleted records. You do not have to specify a KEY so it should read fast.

You do not say how large the file is or if there is even a small window to clean it up.
Even if you cannot get a lock on the file, you can use SQL to insert the records into a temporary file, then SQL DELETE to clear the file and then SQL INSERT ot put them back from the temporary file.

Discuss This Question: 12  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
  • GHENDER
    Thank you. I did not know that if I did a sequential read over a logical with no key, the read would only look at acive records. If I used SQL the way you suggested, would that remove deleted records? Is that the equivalent of doing a reorg? Also, there is not even a small window to clean up the files. However, I am looking into a 3rd party package which would allow me to do a reorg while active w/o having to turn on journaling.
    190 pointsBadges:
    report
  • PSMurray
    There is ALWAYS a window. We have several files that are in use constantly. The program that locks the file is started when the interactive subsystem starts. We have the ability to start an alternative SBS to accomplish the exact problem you describe. Where is your opportunity? Phil
    530 pointsBadges:
    report
  • philpl1jb
    The SQL delete is not a REORG the process Charlie Brown described would leave a set of deleted records at the beginning of your file = current active records plus deleted records. Your first read might be a loooooong one then you would run better. Reorgs are really fast when the file is almost empty but you do need a window. Phil.
    49,550 pointsBadges:
    report
  • CharlieBrowne
    Thanks Phil I was thinking the SQL delete got rid of the deleted records, but that would not be logical. I should ahve realized that.
    41,370 pointsBadges:
    report
  • TomLiotta
    Note that SQL DELETE that specifies no WHERE clause can implement a CLRPFM operation. I'll have to do some digging to see if the conditions are met in this case, but it seems likely for a couple reasons. Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    There is ALWAYS a window. I generally agree. But even more: ...w/o having to turn on journaling. If there truly is no window and there is no journaling, it's a very risky situation. "No window" implies "no backup", and it would seem that this file must be important to processes. Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    Changing the file to REUSEDLT(*YES) is probably the best option. Using a LF without building a new access path (e.g., a key) probably won't help. I'm not sure if there's any way to specify a selection value that will provide an index. Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    Does change of the file to REUSEDLT(*YES) reqire an exclusive lock? Phil
    49,550 pointsBadges:
    report
  • TomLiotta
    Does change of the file to REUSEDLT(*YES) reqire an exclusive lock? It does for me. Tom
    125,585 pointsBadges:
    report
  • GHENDER
    [...] Reading only the active records in a flat file [...]
    0 pointsBadges:
    report
  • GHENDER
    [...] CharlieBrowne, PSMurray, Philpl1jb, and TomLiotta discuss reading only the active records in a flat file. 6. Have you had to use INZSR with RETURN & [...]
    0 pointsBadges:
    report
  • GHENDER
    [...] CharlieBrowne, PSMurray, Philpl1jb, and TomLiotta discuss reading only the active records in a flat file. 6. If you have a question about the max size of a subfile, CharlieBrowne and TomLiotta can help [...]
    0 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