Reading only the active records in a flat file
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
Software/Hardware used:
AS400 V5R4
ASKED:
October 7, 2011 7:05 PM
UPDATED:
March 31, 2012 12:26 AM
Answer Wiki:
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.
To see all answers submitted to the Answer Wiki: View Answer History.
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.
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
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.
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.
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
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
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
Does change of the file to REUSEDLT(*YES) reqire an exclusive lock?
Phil
Does change of the file to REUSEDLT(*YES) reqire an exclusive lock?
It does for me.
Tom