Create a program to change the size of a file, reuse deleted records, and remove deleted records

This is the final part of the following trilogy:

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

The earlier posts described how to manually perform the SQL statements needed. In this post I am going to show a program that combines both of the SQL statements, and makes a program that can be run time and again.

I am not going to repeat a lot of what I said in those posts, therefore, I recommend you read them before you start with this one.

I will show this program in three parts, as I think that will make it easier to explain and for you to understand. This is the first part:

01 **free 02 ctl-opt main(Main) option(*srcstmt) ; 03 dcl-proc Main ; 04 exec sql SET GENERIC_CHARACTER = 'MYLIB2' ; 05 exec sql DROP TABLE IF EXISTS MYLIB.NOMAXFILES ; 06 exec sql DROP TABLE IF EXISTS MYLIB.RGZFILES ;

Line 2: This program has a Main procedure, that means that it does not perform the RPG cycle. The other control option means that the compiler will use the source member line numbers, rather than generate its own.

Line 3: Start of the Main procedure.

Line 4: I cannot use RPG host variables in the SQL statements this program uses. I can use the value contained in a SQL global variable, and I have several generic global variables that can be used for this kind of thing. Holding a character value, I will use later in the program. In this case the library the files I will be changing are in.

Lines 4 and 5: These tables will be generated by the SQL statements that make the changes. I want to delete the tables if they already exists, and not error if they do not.

The second part of the program is the SQL statement to change all the files to have an initial size of NOMAX and to reuse deleted records. I am not going to discuss the statement in great detail as I did in the earlier post.

07 exec sql CREATE TABLE MYLIB.NOMAXFILES 08 (RETURN_CODE,CL_COMMAND) 09 AS 10 (SELECT QSYS2.QCMDEXC( 11 'CHGPF FILE(' || SYSTEM_TABLE_SCHEMA || '/' || 12 SYSTEM_TABLE_NAME || ') SIZE(*NOMAX) REUSEDLT(*YES)'), 13 ('CHGPF FILE(' || SYSTEM_TABLE_SCHEMA || '/' || 14 SYSTEM_TABLE_NAME || ') SIZE(*NOMAX) REUSEDLT(*YES)') 15 FROM QSYS2.SYSFILES 16 WHERE SYSTEM_TABLE_SCHEMA = GENERIC_CHARACTER 17 AND NATIVE_TYPE = 'PHYSICAL' 18 AND FILE_TYPE = 'DATA') 19 WITH DATA ;

Line 7: I am going to create an output file from this statement.

Line 8: The output file will contain two columns: the return code from the QCMDEXC scalar function, and the CL command used.

Lines 10 – 18: This is the same as the statement I explained in the previous post, therefore, I am only going to explain the difference.

Line 16: The library name comes from the generic global variable I populated on line 4.

Line 19: I want the file to contain data, so I need the WITH DATA.

The last part of the program is the statement that reorganizes the files, and the end of the procedure:

20 exec sql CREATE TABLE MYLIB.RGZFILES 21 (RETURN_CODE,CL_COMMAND) 22 AS 23 (SELECT QSYS2.QCMDEXC( 24 'RGZPFM FILE(' || SYSTEM_TABLE_SCHEMA || 25 '/' || SYSTEM_TABLE_NAME || ') MBR(' || 26 SYSTEM_TABLE_MEMBER || ') RBDACCPTH(*OPTIMIZE)'), 27 ('RGZPFM FILE(' || SYSTEM_TABLE_SCHEMA || '/' 28 || SYSTEM_TABLE_NAME || ') MBR(' || 29 SYSTEM_TABLE_MEMBER || ') RBDACCPTH(*OPTIMIZE)') 30 FROM QSYS2.SYSMEMBERSTAT 31 WHERE SYSTEM_TABLE_SCHEMA = GENERIC_CHARACTER 32 AND NUMBER_DELETED_ROWS > 0) 33 WITH DATA ; 34 return ; 35 end-proc ;

Line 20: I am creating an output file for this statement too.

Line 21: It contains the same two columns as the previous one: return code and CL command.

Lines 23 – 32: This is the same as I showed in another previous post.

Line 31: The only difference from those statements is that this one is getting the library name from the generic global variable.

After compiling this program the two output files are created with the results of the QCMDEXC scalar function.

To confirm that the initial file sizes and reuse of deleted records was used:

01 SELECT * FROM NOMAXFILES

Which returned the following:

RETURN _CODE CL_COMMAND ------ -------------------------------------------------------- 1 CHGPF FILE(MYLIB2/TESTFILE0) SIZE(*NOMAX) REUSEDLT(*YES) 1 CHGPF FILE(MYLIB2/TESTFILE1) SIZE(*NOMAX) REUSEDLT(*YES) 1 CHGPF FILE(MYLIB2/TESTFILE2) SIZE(*NOMAX) REUSEDLT(*YES) 1 CHGPF FILE(MYLIB2/TESTFILE3) SIZE(*NOMAX) REUSEDLT(*YES) 1 CHGPF FILE(MYLIB2/TESTFILE4) SIZE(*NOMAX) REUSEDLT(*YES)

As the return codes are all 1 I know that all the CL commands completed successfully, and files must have been changed.

The verify the members changed to remove the deleted records from them I would use:

01 SELECT * FROM RGZFILES

The results are:

RETURN _CODE CL_COMMAND ------ ----------------------------------------------------------------- 1 RGZPFM FILE(MYLIB2/TESTFILE1) MBR(TESTFILE1) RBDACCPTH(*OPTIMIZE) 1 RGZPFM FILE(MYLIB2/TESTFILE2) MBR(TESTFILE2) RBDACCPTH(*OPTIMIZE) 1 RGZPFM FILE(MYLIB2/TESTFILE3) MBR(TESTFILE3) RBDACCPTH(*OPTIMIZE) 1 RGZPFM FILE(MYLIB2/TESTFILE4) MBR(TESTFILE4) RBDACCPTH(*OPTIMIZE)

The return codes confirm that the members were all reorganized.

The trilogy is complete. You know how to change your file sizes, reuse deleted records, and remove the deleted records from the files to not have files full of deleted records wasting your IBM i partition's storage.

 

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