Remove the deleted records from the file

This is the second part of the trilogy I started last week:

1. Change the file's size and reuse deleted records
2. Remove the deleted records from the file
3. Create a program to perform both of the above

In this post I will be giving an example of how I chose to remove the deleted records from all of the files in a library.

When a record is deleted from a file its space is not available to be reused, unless the file is reusing deleted records. Over time this can result in files have a few active records and many deleted ones. This is a waste of the available storage.

The Reorganize Physical File Member command, RGZPFM, is the command that will remove the delete records from a physical file. You need to be careful when using this command. If any of the files in the library are record address files the reorganization could make it impossible to retrieve the expected records from the file, do not reorganize them.

As data is contained in members, and a file can contain more than one member I need to work at the member level, rather than at the file level. In my years of experience most applications do not use multi-member files, but there is always one or two. If I RGZPFM without giving the member's name only the first member in the file will be reorganized.

I am going to use the SQL View SYSMEMBERSTAT to provide the list of files and members in any library. If you are on a system that is using a release that does not have SYSMEMBERSTAT, the view SYSPARTITIONSTAT can be used instead. I believe that the columns I will be using have the same names in both Views.

In this example I am going to same files I used in the previous post. Below is a statement I can use to see how many deleted records each of these files contain:

01 SELECT A.SYSTEM_TABLE_NAME,A.SYSTEM_TABLE_MEMBER, 02 A.NUMBER_ROWS,A.NUMBER_DELETED_ROWS 03 FROM QSYS2.SYSMEMBERSTAT A, QSYS2.SYSFILES B 04 WHERE (A.SYSTEM_TABLE_SCHEMA,A.SYSTEM_TABLE_NAME) = 05 (B.SYSTEM_TABLE_SCHEMA,B.SYSTEM_TABLE_NAME) 06 AND A.SYSTEM_TABLE_SCHEMA = 'MYLIB' 07 AND B.NATIVE_TYPE = 'PHYSICAL' 08 AND B.FILE_TYPE = 'DATA' 09 AND NUMBER_DELETED_ROWS > 0 10 ORDER BY 1,2

Lines 1 and 2: I think the column names explain what data is contained within them.

Line 3: I need data from both SYSMEMBERSTAT and SYSFILES. I need to use SYSFILES to identify the file type. I cannot reorganize logical files, and I do not want to reorganize source files.

Lines 4 and 5: This is "shortcut" method for giving the files to join Views together. I prefer this method doing it rather than the "long handed" way shown below:

WHERE A.SYSTEM_TABLE_SCHEMA = B.SYSTEM_TABLE_SCHEMA AND A.SYSTEM_TABLE_NAME = B.SYSTEM_TABLE_NAME

Line 6: I am only interested in reorganizing the files in my library.

Lines 7 and 8: These are the columns in SYSFILES that allow me to identify what is a physical file, line 7, and that it is a data file, line 8.

Line 9: I only want to find files with deleted records within them.

Line 10: I want the results sorted by file and member name.

The results are:

SYSTEM_ SYSTEM_ NUMBER_ TABLE_ TABLE_ NUMBER DELETED NAME MEMBER _ROWS _ROWS --------- --------- ------ ------- TESTFILE1 TESTFILE1 5000 5000 TESTFILE2 TESTFILE2 6667 3333 TESTFILE3 TESTFILE3 7500 2500 TESTFILE4 TESTFILE4 1 9999

As I mentioned before I will be using the RGZPFM command to perform the reorganization of the files. The basic syntax of the command I will be using is:

RGZPFM FILE( < library name > / < file name > ) MBR( < member name > ) + RBDACCPTH(*OPTIMIZE))

Like I did when I changed the files I am going to use the QCMDEXC scalar function to execute the CL command:

01 SELECT QSYS2.QCMDEXC('RGZPFM FILE(' || A.SYSTEM_TABLE_SCHEMA || '/' || A.SYSTEM_TABLE_NAME || 02 ') MBR(' || A.SYSTEM_TABLE_MEMBER || ') RBDACCPTH(*OPTIMIZE)'), 03 ('RGZPFM FILE(' || A.SYSTEM_TABLE_SCHEMA || '/' || A.SYSTEM_TABLE_NAME || 04 ') MBR(' || A.SYSTEM_TABLE_MEMBER || ') RBDACCPTH(*OPTIMIZE)') 05 FROM QSYS2.SYSMEMBERSTAT A, QSYS2.SYSFILES B 06 WHERE (A.SYSTEM_TABLE_SCHEMA,A.SYSTEM_TABLE_NAME) = 07 (B.SYSTEM_TABLE_SCHEMA,B.SYSTEM_TABLE_NAME) 08 AND A.SYSTEM_TABLE_SCHEMA = 'MYLIB' 09 AND B.NATIVE_TYPE = 'PHYSICAL' 10 AND B.FILE_TYPE = 'DATA' 11 AND NUMBER_DELETED_ROWS > 0 12 ORDER BY NUMBER_DELETED_ROWS DESC

Lines 1 and 2: The QCMDEXC scalar function contains the CL command RGZPFM. I am sure you all know by now that the double pipe symbol ( || ) is the equivalent of concatenate. This scalar function returns one of two values:

• 1 = This command successfully completed
• -1 = The command failed

Lines 3 and 4: I want to show the command that was executed.

Lines 5 – 11: This is the same as the previous SQL statement.

Line 12: I want to sort the results from the file with the most deleted records first and descending to ones with the least. Why? If I only have a limited amount of time I would add the following to this statement:

13 LIMIT 1000

And this statement would only reorganize the "worst" 1,000 files. The rest would be left for another day.

The results are:

00001 00002 ----- ------------------------------------------------------------------ 1 RGZPFM FILE(RPGPGM1/TESTFILE4) MBR(TESTFILE4) RBDACCPTH(*OPTIMIZE) 1 RGZPFM FILE(RPGPGM1/TESTFILE1) MBR(TESTFILE1) RBDACCPTH(*OPTIMIZE) 1 RGZPFM FILE(RPGPGM1/TESTFILE2) MBR(TESTFILE2) RBDACCPTH(*OPTIMIZE) 1 RGZPFM FILE(RPGPGM1/TESTFILE3) MBR(TESTFILE3) RBDACCPTH(*OPTIMIZE)

All of the files were successfully reorganized.

The common reason for failing to be reorganized is the file is in use by another job.

To verify that none of my files contain delete records I can take the first SQL statement I showed and modify it:

01 SELECT A.SYSTEM_TABLE_NAME,A.SYSTEM_TABLE_MEMBER, 02 A.NUMBER_ROWS,A.NUMBER_DELETED_ROWS 03 FROM QSYS2.SYSMEMBERSTAT A, QSYS2.SYSFILES B 04 WHERE (A.SYSTEM_TABLE_SCHEMA,A.SYSTEM_TABLE_NAME) = 05 (B.SYSTEM_TABLE_SCHEMA,B.SYSTEM_TABLE_NAME) 06 AND A.SYSTEM_TABLE_SCHEMA = 'MYLIB' 07 AND B.NATIVE_TYPE = 'PHYSICAL' 08 AND B.FILE_TYPE = 'DATA' 09 ORDER BY 1,2

The only difference between this and the first one is that I needed to remove the line that checked if there were more than zero delete records. As this files have all be reorganized all the files have no deleted records:

SYSTEM_ SYSTEM_ NUMBER_ TABLE_ TABLE_ NUMBER DELETED NAME MEMBER _ROWS _ROWS --------- --------- ------ ------- TESTFILE1 TESTFILE1 5000 0 TESTFILE2 TESTFILE2 6667 0 TESTFILE3 TESTFILE3 7500 0 TESTFILE4 TESTFILE4 1 0

That is the first two parts of the trilogy completed. In my next post I will show an example program, containing both statements, that can be used repeatedly.

 

This article was written for IBM i 7.6, and should work for some earlier releases too.