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):

LIB FILE MEMBER DESCRIPTION DEL RECS FREE BYTES
AMFLIB5 TSKMSG TSKMSG M7X-PSI-Task messages 2,207 8,040,101
AMFLIB5 TRNDTA TRNDTA M7X-PSI-Transaction data   8,531 2,363,087
AMFLIB8 MSCSLT MSCSLT PSI-mass change select 149 449,682
AMFLIB3 TSKMSG TSKMSG M7X-PSI-Task messages 117 426,231
AMFLIB3 TRNDTA TRNDTA M7X-PSI-Transaction data 1,304 361,208
AMFLIB3 MSCSLT MSCSLT PSI-mass change select   110 331,980
AMFLIB4 MSCSLT MSCSLT PSI-mass change select 105 316,890
AMFLIB5 MSCSLT MSCSLT PSI-mass change select    76    229,368
AMFLIB8 TRNDTA TRNDTA M7X-PSI-Transaction data 605 167,585
AMFLIBZ MSCSLT MSCSLT PSI-mass change select 32 96,576
AMFLIB3 TRNSTS TRNSTS M7X-Transaction status 371 93,492
AMFLIB8 TRNSTS TRNSTS M7X-Transaction status 261 65,772
AMFLIB7 TSKMSG TSKMSG M7X-PSI-Task messages 18 65,574
AMFLIB7 TRNDTA TRNDTA M7X-PSI-Transaction data 190 52,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.

     

    Infor ERP XA Release 10