Automatic reorganization of IBM i tables using RGZPFM

In this article we made a short summary for the automatic reorganization of IBM i tables using RGZPFM.
Please note: this is a concept and there is no warranty. You are responsible for the impact on your system. If you need further information, please do not hesitate to use our contact form.

Usually, we don’t care about deleted records in IBM i tables anymore because disk spaces became cheaper and cheaper over the years. But of course, it makes sense to check from time to time and keep the size of the tables as small as possible. As a leading partner for Infor ERP XA we discussed this topic several times with our customers and we built solutions to do the RGZPFM automatically without any effort for administrators.

This is the basic concept:

It all starts with the command DSPFD to analyze the tables on the system like that:

DSPFD FILE(*LIBL/*ALL) 
TYPE(*MBR) 
OUTPUT(*OUTFILE) 
OUTFILE(*LIB/*FILE)

Please be careful with the selection of the libraries. In the example mentioned above, we used *LIBL. But there are other options. You could also use *ALLUSR, which means the command would analyze all libraries except system libraries (see help with F1 for further information). You can imagine, this would be the best option to keep the system clean, but it takes a lot of time – probably some/many hours, generates workload on the machine and you have to make sure that there is no impact on any application on the system. Think about your needs.

The parameter TYPE(*MBR) is used to get information about deleted records and OUTPUT and OUTFILE will write the result in a new table. Let’s see the result of an example. We will check the file libraries of Infor ERP XA and the library list contains the file libraries of all our XA environments:

DSPFD FILE(*LIBL/*ALL) 
TYPE(*MBR) 
OUTPUT(*OUTFILE) 
OUTFILE(AUTOREORG/AUTOREORG)

In the file AUTOREORG, which we just created we need to check the following fields:

Now let us select physical files only with BYTESFREE>0 and sort this descending by BYTESFREE to see the result (only the first records):

This looks very good, because on our system we run the RGZPFM every week. You should try this on your system (again: be careful with the libraries, see above). To keep the system clean automatically, it makes sense to do that in a CLP and schedule this. Maybe it is a good idea to start manually with the most important libraries.

But it makes no sense to reorganize each single file. We should focus on the files with an impact on the diskspace. That’s why we sorted by BYTESFREE descending.

Next step is now to reorganize the top of the list only. It is up to you to reorganize the top 5 or 10 or more. But we should limit that to reduce the processing time. We will not process all files, but only the worst – means highest number of unused bytes (BYTESFREE) with the best result. If you do this every day or week, you will reorganize all the files step by step and after a while, the maximum number of BYTESFREE will be very low. It makes sense – and that’s how we did that for our customers – to create a CL-Programm to reads the first records in this table and do a RGZPFM for the top records. Please make sure, that this command is followed by a MONMSG command, in case the file cannot be reorganized because it is locked or for whatever reason. In this case, just skip the file and take the next one.

Conclusion

We hope this helps you in your daily business. For more information around Infor XA please see our Infor ERP XA competence center.

In case you have any feedback, comment or additional questions, do not hesitate to use our contact form.