After reviewing the responses I received from my last tip Get rid of deleted records and improve performance) I thought it would be helpful to share these comments with you.
The biggest point people brought up was that some programs access files via RRN (Relative Record Number), and reorganizing one of these files will cause these programs to retrieve the wrong data. This should be evaluated before you remove deleted records. If your programs do, in fact, access the data via RRN, then you will need to evaluate what the impact on those programs would be to see if there are alternatives.
An alternative idea to RGZPFM is to copy (CPYF) the data to a holding file and then back to the original file. When you copy a file (CPYF), deleted records are not copied, thus removing them.
CPYF will remove deleted records, and by writing a simple CL program you can achieve the same results. Many AS400 shops use this method and achieve the desired results.
One other issue someone brought up was that when dealing with files that have more then one member, RGZPFM will reorganize the first member by default. If you are dealing with such a file, you will need to enter the member name in the command.
Another option to reclaim deleted records is to set the physical file attribute to use deleted records. You can set this attribute on live data by entering CHGPF (MYLIB/MYFILE) REUSEDLT(*YES). By doing this, when new records are added to the file, they will replace deleted records. If you use this method, arrival order becomes meaningless for a file that reuses deleted record space. Records might not be added at the end of the file.
As with any tip, recommendation or suggestion you receive, always take the time to understand the impact it will have and how it will affect the environment you are in. No one solution will ever work for everyone, but many people -- like me -- enjoy the opportunity to share their experiences with others.
One of the traits I admire the most about AS/400 programmers and administrators is the conviction they have with this machine. They know what works in their shops, and they know what does not. They always speak their minds openly and honestly. I welcome all the responses I receive and look forward to many more.
One last thing: Remember to delete the FILE you created with the DSPFD command. Once you are done, it just takes up DASD.
---------------------------------------
Kohan is a senior programmer analyst for a software development company. He has worked with the AS/400 since 1990 and has learned how to use the platform to solve business problems. He is also an adjunct instructor, teaching AS/400 classes at his local state college.
