Automatic reorganization of IBM i tables using RGZPFM

Infor ERP XA Knowledge

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.