{"id":7330,"date":"2022-07-21T14:15:42","date_gmt":"2022-07-21T12:15:42","guid":{"rendered":"https:\/\/mjr.gmbh\/?p=7330"},"modified":"2023-07-27T15:59:48","modified_gmt":"2023-07-27T13:59:48","slug":"automatic-reorganization-of-ibm-i-tables-using-rgzpfm","status":"publish","type":"post","link":"https:\/\/testsite.mjr.gmbh\/en\/infor-erp-xa-knowledge\/automatic-reorganization-of-ibm-i-tables-using-rgzpfm\/","title":{"rendered":"Automatic reorganization of IBM i tables using RGZPFM"},"content":{"rendered":"

In this article we made a short summary for the automatic reorganization of IBM i tables using RGZPFM.
\nPlease note:<\/em><\/strong> 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.<\/span><\/p>\n

Usually, we don\u2019t 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<\/code> automatically without any effort for administrators. <\/span><\/p>\n

This is the basic concept:<\/h3>\n

It all starts with the command DSPFD to analyze the tables on the system like that:<\/span><\/p>\n

DSPFD FILE(*LIBL\/*ALL) <\/span>\r\nTYPE(*MBR) <\/span>\r\nOUTPUT(*OUTFILE) <\/span>\r\nOUTFILE(*LIB\/*FILE<\/em>)<\/span><\/pre>\n
Please be careful with the selection of the libraries. In the example mentioned above, we used *LIBL.<\/code> But there are other options. You could also use *ALLUSR<\/code>, 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 \u2013 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.<\/span><\/p>\n
The parameter TYPE(*MBR)<\/code> is used to get information about deleted records and OUTPUT<\/code> and OUTFILE<\/code> 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:<\/span><\/p>\n
DSPFD FILE(*LIBL\/*ALL) <\/span>\r\nTYPE(*MBR) <\/span>\r\nOUTPUT(*OUTFILE) <\/span>\r\nOUTFILE(AUTOREORG\/AUTOREORG)<\/span><\/pre>\n
In the file AUTOREORG<\/code>, which we just created we need to check the following fields:<\/span><\/p>\n\n\n\n\n\n\n\n\n\n
MBLIB <\/span><\/code><\/td>\nLibrary<\/span><\/td>\n<\/tr>\n
MBFILE <\/span><\/code><\/td>\nFile<\/span><\/td>\n<\/tr>\n
MBNAME <\/span><\/code><\/td>\nMember<\/span><\/td>\n<\/tr>\n
MBTXT <\/span><\/code><\/td>\nDescription<\/span><\/td>\n<\/tr>\n
MBNDTR <\/span><\/code><\/td>\nNumber of deleted records<\/span><\/td>\n<\/tr>\n
MBMXRL <\/span><\/code><\/td>\n Max. record length (for calculation only, see below)<\/span><\/td>\n<\/tr>\n
BYTESFREE <\/span><\/code><\/td>\ncalculated as MBNDTR*MBMXRL<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Now let us select physical files only with BYTESFREE>0<\/code> and sort this descending by BYTESFREE<\/code> to see the result (only the first records): <\/span><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
LIB<\/td>\nFILE<\/td>\nMEMBER<\/td>\nDESCRIPTION<\/td>\nDEL RECS<\/td>\nFREE BYTES<\/td>\n<\/tr>\n
AMFLIB5<\/td>\nTSKMSG<\/td>\nTSKMSG<\/td>\nM7X-PSI-Task messages<\/td>\n2,207<\/td>\n8,040,101<\/td>\n<\/tr>\n
AMFLIB5<\/td>\nTRNDTA<\/td>\nTRNDTA<\/td>\nM7X-PSI-Transaction data<\/td>\n\u00a0 8,531<\/td>\n2,363,087<\/td>\n<\/tr>\n
AMFLIB8<\/td>\nMSCSLT<\/td>\nMSCSLT<\/td>\nPSI-mass change select<\/td>\n149<\/td>\n449,682<\/td>\n<\/tr>\n
AMFLIB3<\/td>\nTSKMSG<\/td>\nTSKMSG<\/td>\nM7X-PSI-Task messages<\/td>\n117<\/td>\n426,231<\/td>\n<\/tr>\n
AMFLIB3<\/td>\nTRNDTA<\/td>\nTRNDTA<\/td>\nM7X-PSI-Transaction data<\/td>\n1,304<\/td>\n361,208<\/td>\n<\/tr>\n
AMFLIB3<\/td>\nMSCSLT<\/td>\nMSCSLT<\/td>\nPSI-mass change select<\/td>\n\u00a0 110<\/td>\n331,980<\/td>\n<\/tr>\n
AMFLIB4<\/td>\nMSCSLT<\/td>\nMSCSLT<\/td>\nPSI-mass change select<\/td>\n105<\/td>\n316,890<\/td>\n<\/tr>\n
AMFLIB5<\/td>\nMSCSLT<\/td>\nMSCSLT<\/td>\nPSI-mass change select<\/td>\n\u00a0\u00a0 76<\/td>\n\u00a0\u00a0 229,368<\/td>\n<\/tr>\n
AMFLIB8<\/td>\nTRNDTA<\/td>\nTRNDTA<\/td>\nM7X-PSI-Transaction data<\/td>\n605<\/td>\n167,585<\/td>\n<\/tr>\n
AMFLIBZ<\/td>\nMSCSLT<\/td>\nMSCSLT<\/td>\nPSI-mass change select<\/td>\n32<\/td>\n96,576<\/td>\n<\/tr>\n
AMFLIB3<\/td>\nTRNSTS<\/td>\nTRNSTS<\/td>\nM7X-Transaction status<\/td>\n371<\/td>\n93,492<\/td>\n<\/tr>\n
AMFLIB8<\/td>\nTRNSTS<\/td>\nTRNSTS<\/td>\nM7X-Transaction status<\/td>\n261<\/td>\n65,772<\/td>\n<\/tr>\n
AMFLIB7<\/td>\nTSKMSG<\/td>\nTSKMSG<\/td>\nM7X-PSI-Task messages<\/td>\n18<\/td>\n65,574<\/td>\n<\/tr>\n
AMFLIB7<\/td>\nTRNDTA<\/td>\nTRNDTA<\/td>\nM7X-PSI-Transaction data<\/td>\n190<\/td>\n52,630<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
This looks very good, because on our system we run the RGZPFM<\/code> every week. You should try this on your system (again<\/strong>: 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.<\/span><\/p>\n
But it makes no sense to reorganize each single file. We should focus on the files with an impact on the diskspace. That\u2019s why we sorted by BYTESFREE<\/code> descending.<\/span><\/p>\n
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 \u2013 means highest number of unused bytes (BYTESFREE<\/code>) 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<\/code> 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<\/code> for the top records. Please make sure, that this command is followed by a MONMSG<\/code> 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.<\/span><\/p>\n
Conclusion<\/h3>\n
We hope this helps you in your daily business. For more information around Infor XA please see our Infor ERP XA competence center<\/a>.<\/span><\/p>\n
In case you have any feedback, comment or additional questions, do not hesitate to use our contact form.<\/span><\/p>\n
\n
\n
<\/p> 
    <\/ul><\/div>\n
    \n
    \n\n\n\n\n\n\n<\/div>\n
    <\/span>\n<\/p>\n
    <\/span>\n<\/p>\n
    <\/span>\n<\/p>\n