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:

MBLIB Library
MBFILE File
MBNAME Member
MBTXT Description
MBNDTR Number of deleted records
MBMXRL Max. record length (for calculation only, see below)
BYTESFREE calculated as MBNDTR*MBMXRL

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

LIBFILEMEMBERDESCRIPTIONDEL RECSFREE BYTES
AMFLIB5TSKMSGTSKMSGM7X-PSI-Task messages2,2078,040,101
AMFLIB5TRNDTATRNDTAM7X-PSI-Transaction data  8,5312,363,087
AMFLIB8MSCSLTMSCSLTPSI-mass change select149449,682
AMFLIB3TSKMSGTSKMSGM7X-PSI-Task messages117426,231
AMFLIB3TRNDTATRNDTAM7X-PSI-Transaction data1,304361,208
AMFLIB3MSCSLTMSCSLTPSI-mass change select  110331,980
AMFLIB4MSCSLTMSCSLTPSI-mass change select105316,890
AMFLIB5MSCSLTMSCSLTPSI-mass change select   76   229,368
AMFLIB8TRNDTATRNDTAM7X-PSI-Transaction data605167,585
AMFLIBZMSCSLTMSCSLTPSI-mass change select3296,576
AMFLIB3TRNSTSTRNSTSM7X-Transaction status37193,492
AMFLIB8TRNSTSTRNSTSM7X-Transaction status26165,772
AMFLIB7TSKMSGTSKMSGM7X-PSI-Task messages1865,574
AMFLIB7TRNDTATRNDTAM7X-PSI-Transaction data19052,630

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.

    [recaptcha]

     

    TRDATA processing – behind the scenes
    Infor ERP XA Release 10