Change the files' size and reuse deleted records

Recently I have been surprised there have been several messages during the day-end process alerting that various files are full. The on-call system administrator has been answering the messages without issue, and the job continues. This is a sign there is some "clean up" that needs to be performed upon the files this error happened to:

• File size
• Remove deleted records from the file

I could use the system reply list, which will automatically answer a message for me. But this will make the response to the error happen to every time, and in this case to all files, that the errors happens to. Which is not what I want.

Making the changes I am going to suggest something that can be and easily performed on a single file basis. I want to be proactive and stop the error from happening for all the files in a library.

Rather than pack all of what I did into one post I am going make this a 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

This post is the first part: changing the file size and making the file reuse deleted records.

When a physical file is created there are three parameters that control the size of the file:

Create Physical File (CRTPF) Member size: SIZE Initial number of records . . 100000 Increment number of records . 10000 Maximum increments . . . . . . 40

While this is more than large enough for many files, there are enough files that need to be larger. There is a special value that can be used for the size: "*NOMAX". Changing files to be "*NOMAX" has proved controversial in the past. Often cited is a scenario where a program gets stuck in a loop and, eventually, it writes to an output file so many times that it eventually causes a system error by consuming all the available storage. In my 35+ years of working with IBM i, and its earlier forms, I have never seen this happen. It is also interesting that when I create a DDL table, with the CREATE TABLE SQL statement, the table (= file) is created with the size of "*NOMAX".

I will also be changing the "Reuse deleted records" value too.

Reuse deleted records . . . . . REUSEDLT *NO

When a record is deleted from a file the space occupied by that record is not reclaimed. It is still present, and flagged to the operating system as deleted. This means I can have files of a few active records, and many deleted ones. On a database insert to the file the reuse deleted records will make the operating system find then first available deleted record and replace it with a new active record, or if no deleted records are present it will add a new record to the file.

What, IMHO, is the easiest way to change all of the files in a library or libraries to have a size of "*NOMAX" and reuse deleted records? SQL is the obvious choice. I can change every eligible file in one Select statement by using the QCMDEXC SQL scalar function.

I am going to use the SYSFILES SQL View. I want to be able to identify all files that are not "*NOMAX" and reuse deleted records.

This is the statement I used:

01 SELECT SYSTEM_TABLE_NAME,INITIAL_RECORDS,REUSE_DELETED_RECORDS 02 FROM QSYS2.SYSFILES 03 WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' 04 AND NATIVE_TYPE = 'PHYSICAL' 05 AND FILE_TYPE = 'DATA' 06 ORDER BY 1,2

Line 1: I want the results to be:

• File name
• Number of initial records (=file size)
• Re use delete records

Line 3: All of the file from my library.

Lines 4 and 5: These are important. I have different types of files in my library: physical files, logical files, source files, save files, etc. I only want to return physical data files in my results. I do not want to try and change logical and source files.

My results are:

SYSTEM_ REUSE_ TABLE_ INITIAL_ DELETED_ NAME RECORDS RECORDS --------- -------- -------- TESTFILE1 100000 NO TESTFILE2 100000 NO TESTFILE3 100000 NO TESTFILE4 100000 NO

I change the initial file size and reuse delete records using the Change Physical File command, CHGPF. This is a CL command I can use with the QCMDEXC scalar function.

The syntax I will be using for the CHGPF command is:

CHGPF FILE( < library name > / < file name > ) + SIZE(*NOMAX) REUSEDLT(*YES)

My SQL statement to build the command string and execute it, using the QCMDEXC scalar function, was:

01 SELECT QSYS2.QCMDEXC('CHGPF FILE(' || SYSTEM_TABLE_SCHEMA || '/' || 02 SYSTEM_TABLE_NAME || ') SIZE(*NOMAX) REUSEDLT(*YES)'), 03 ('CHGPF FILE(' || SYSTEM_TABLE_SCHEMA || '/' || 04 SYSTEM_TABLE_NAME || ') SIZE(*NOMAX) REUSEDLT(*YES)'), 05 FROM QSYS2.SYSFILES 06 WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' 07 AND NATIVE_TYPE = 'PHYSICAL' 08 AND FILE_TYPE = 'DATA'

Lines 1 and 2: The QCMDEXC scalar function contains the command string that will use the CHGPF to change the files. Those of you familiar with my work already know that the double pipe symbols ( || ) is the equivalent of concatenate. The scalar function returns:

• 1 = Command successfully completed
• -1 = Command failed

Lines 3 and 4: I want to show the command string used to change the files.

I have found the most common reason this command fails it is when the file being changed is in use by another job. As the file cannot be locked, the CHGPF fails.

When I execute this statement the following results are returned:

00001 00002 ----- ------------------------------------------------------- 1 CHGPF FILE(MYLIB/TESTFILE1) SIZE(*NOMAX) REUSEDLT(*YES) 1 CHGPF FILE(MYLIB/TESTFILE2) SIZE(*NOMAX) REUSEDLT(*YES) 1 CHGPF FILE(MYLIB/TESTFILE3) SIZE(*NOMAX) REUSEDLT(*YES) 1 CHGPF FILE(MYLIB/TESTFILE4) SIZE(*NOMAX) REUSEDLT(*YES)

As all the file have a return code of "1" I am confident that all of the files were changed. The proof is shown when I run the first SQL statement I gave in this post. The results it returned are:

SYSTEM_ REUSE_ TABLE_ INITIAL_ DELETED_ NAME RECORDS RECORDS --------- -------- -------- TESTFILE1 <NULL> YES TESTFILE2 <NULL> YES TESTFILE3 <NULL> YES TESTFILE4 <NULL> YES

This confirms that all of the files were changed.

If I wanted to change files in multiple libraries I would just change line 6 of the previous statement to be:

06 WHERE SYSTEM_TABLE_SCHEMA IN ('MYLIB','MYLIB1','MYLIB2')

Having changed the files' size and ability to reuse deleted records I have completed the first part of this trilogy. In my next post I will explain how I removed all of the deleted records from these files.

 

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