Using SQL to check for level check, reuse deleted, and file size

There are three things about data physical files I always like to keep track of:

1. Level check status
2. Will file reuse deleted records
3. What is the maximum number of records the file can contain

For years I have used the Display File Description command, DSPFD, to create a lists of the files in a library, or libraries, and then read that file to check the fields for that information. As I can execute CL commands in ACS's Run SQL Scripts I can do the following:

01 CL:DSPFD FILE(MYLIB/*ALL) TYPE(*ATR) OUTPUT(*OUTFILE) FILEATR(*PF) OUTFILE(QTEMP/DSPFD_ATR) ; 02 SELECT PHLIB,PHFILE,PHDTAT,PHLVLC,PHRCDC,PHRUSE FROM QTEMP/DSPFD_ATR ;

Line 1: As this line starts with "CL:" Run SQL Scripts knows that this is CL command, rather then a SQL statement. To get the information I desire I need the attribute information, TYPE(*ATR). I am creating a list of all the physical files in my library, MYLIB, and sending the output to the file DSPFD_ATR in the library QTEMP.

Line 2: I am using this SQL statement to only display the fields I am interested in:

• PHLIB:  Library
• PHFILE:  File
• PHDTAT:  File type
• PHLVLC:  Level check
• PHRCDC:  Initial number of records
• PHRUSE:  Reuse deleted records

A very small example of the results from my library are:

PHLIB PHFILE PHDTAT PHLVLC PHRCDC PHRUSE ------- -------- ------ ------ ------ ------ MYLIB DEVSRC S N 509000 MYLIB TESTFILE D Y 0 Y

DEVSRC is a source file, as shown by "S" in the field PHDTAT. TESTFILE is a data file, that has level check set to Yes. Zero in PHRCDC means that file is set to a size of *NOMAX. And it reuses deleted records.

The draw back of this method is if I change anything and I want to check it I have to run the DSPFD command again to regenerate the output file. For one small library this may not be a big delay, but if I was checking several large libraries this process could take many minutes.

Fortunately the same information is held in the SQL View SYSFILES, that is found in the library QSYS2. The columns I am interested in are:

• SYSTEM_TABLE_SCHEMA:  Library
• SYSTEM_TABLE_NAME:  File
• NATIVE_TYPE  The type of object IBM i thinks it is
• FILE_TYPE
• LEVEL_CHECK
• REUSE_DELETED_RECORDS
• INITIAL_RECORDS

I can create the following Select statement to retrieve the information I want:

01 SELECT SYSTEM_TABLE_SCHEMA AS "Library", 02 SYSTEM_TABLE_NAME AS "File", 03 NATIVE_TYPE AS "Native typ", 04 FILE_TYPE AS "File typ", 05 LEVEL_CHECK AS "Level check", 06 REUSE_DELETED_RECORDS AS "Reuse", 07 INITIAL_RECORDS AS "Size" 08 FROM QSYS2.SYSFILES 09 WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB'

Lines 1 - 7: I have given the columns shorter column headings so that the results will fit on this page.

Line 8: Getting the results from SYSFILES.

Line 9: Only for files in my library, MYLIB.

A sample of the results look like:

Library File Native typ File typ Lvl chk Reuse Size ------- ---------- ---------- -------- ------- ----- ------ MYLIB DEVSRC PHYSICAL SOURCE NO NO 10000 MYLIB FILE1 PHYSICAL DATA YES YES <NULL> MYLIB FILE2 PHYSICAL DATA YES YES <NULL> MYLIB PERSONV0 LOGICAL DATA YES <NULL> <NULL>

For what I want here I am going to ignore DEVSRC as the file type column shows that it is a source file. I am also going to ignore PERSONV0 as the native type column shows that it is a logical file. That leaves FILE1 and FILE2. The null value in size columns indicates that the file is set to *NOMAX. How can I make this statement just give me what I want?

01 SELECT SYSTEM_TABLE_SCHEMA AS "Library", 02 SYSTEM_TABLE_NAME AS "File", 03 NATIVE_TYPE AS "Native typ", 04 FILE_TYPE AS "File typ", 05 LEVEL_CHECK AS "Level check", 06 REUSE_DELETED_RECORDS AS "Reuse", 07 CHAR(IFNULL(TO_CHAR(INITIAL_RECORDS),'*NOMAX'),10) AS "Size" 08 FROM QSYS2.SYSFILES 09 WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' 10 AND NATIVE_TYPE = 'PHYSICAL' 11 AND FILE_TYPE = 'DATA'

What have I changed?

Line 7: Looks complicated by really it is not. I want "*NOMAX" to be displayed in the initial records column, as IMHO it would make the result easier to understand. I can convert the null value to another using IFNULL. The problem is that this column is numeric, therefore, I need to convert the value in the column to character using TO_CHAR within the IFNULL. If the value is not null then it is numeric, therefore, I need convert the value in the column to character using CHAR, and I gave it a length of ten.

Line 10: I only want results for physical type objects.

Line 11: And of the file type is data.

The results look so much better:

Library File Native typ File typ Lvl chk Reuse Size ------- ---------- ---------- -------- ------- ----- ------ MYLIB FILE1 PHYSICAL DATA YES YES *NOMAX MYLIB FILE2 PHYSICAL DATA YES YES *NOMAX

Let me give examples of what these columns will show. I will start by creating a physical file, TESTFILE:

01 CRTPF FILE(MYLIB/TESTFILE) 02 RCDLEN(10) 03 SIZE(10000 1000) 04 LVLCHK(*YES)

Lines 3 and 4: These are the defaults for the Create Physical File command, CRTPF. I have given them here just to show what the defaults are.

Next is to create a SQL Table, TESTTABLE:

01 CREATE TABLE MYLIB.TESTTABLE 02 (C1 VARCHAR(30),C2 VARCHAR(30))

I have an added an extra line to the previous Select statement to only return the details for the file and table I created above, line 12:

01 SELECT SYSTEM_TABLE_SCHEMA AS "Library", 02 SYSTEM_TABLE_NAME AS "File", 03 NATIVE_TYPE AS "Native typ", 04 FILE_TYPE AS "File typ", 05 LEVEL_CHECK AS "Lvl check", 06 REUSE_DELETED_RECORDS AS "Reuse", 07 CHAR(IFNULL(TO_CHAR(INITIAL_RECORDS),'*NOMAX'),10) AS "Size" 08 FROM QSYS2.SYSFILES 09 WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' 10 AND NATIVE_TYPE = 'PHYSICAL' 11 AND FILE_TYPE = 'DATA' 12 AND SYSTEM_TABLE_NAME IN ('TESTFILE','TESTTABLE')

The results are:

Library File Native typ File typ Lvl chk Reuse Size ------- -------- ---------- -------- ------- ----- ------ MYLIB TESTFILE PHYSICAL DATA YES NO 10000 MYLIB TESTTABLE PHYSICAL DATA YES YES *NOMAX

This shows that the table's defaults are just the way I want them to be. The file does not reuse deleted records and is not set to the size of *NOMAX.

Just to show that the level check column is valid I changed it value using the Change Physical File command, CHGPF:

01 CHGPF FILE(TESTFILE) LVLCHK(*NO)

I am using the same Select statement as before, but just showing the result for TESTFILE.

Library File Native typ File typ Lvl chk Reuse Size ------- -------- ---------- -------- ------- ----- ------ MYLIB TESTFILE PHYSICAL DATA NO NO 10000

It shows that the level check is now "NO". The level check, reuse delete records, and size are not what I want them to be. I change them using the CHGPF command:

01 CHGPF FILE(TESTFILE) 02 SIZE(*NOMAX) 03 REUSEDLT(*YES) 04 LVLCHK(*YES)

Now the result for TESTFILE is what I desired:

Library File Native typ File typ Lvl chk Reuse Size ------- -------- ---------- -------- ------- ----- ------ MYLIB TESTFILE PHYSICAL DATA YES YES *NOMAX

With these results I could write a program to check all the files and tables in a library that they have these the three parameters the way I want, and if not change them.

As I said this is faster way to retrieve the information than using the DSPFD command.

 

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