Wednesday, October 12, 2022

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.

6 comments:

  1. how about getting the record format ?

    ReplyDelete
    Replies
    1. The column name for record format is FORMAT_NAME. Use SELECT * FROM SYSFILES to see all the available information.

      Delete
  2. Great information as always. I was wondering why the CHAR() function was necessary in this line CHAR(IFNULL(TO_CHAR(INITIAL_RECORDS),'*NOMAX'),10) AS "Size"
    since you are already converting the numeric value to character using the TO_CHAR() function.

    ReplyDelete
  3. Very good tip, as usual ! In queries like this one, I always prefer perform tests with a CASE, for better understanding, so I would code this:
    CASE WHEN INITIAL_RECORDS IS NULL THEN '*NOMAX'
    ELSE CHAR(INITIAL_RECORDS) END AS "Size"

    ReplyDelete
  4. Great article--how do I get the list that is generated into a spreadsheet or AS400 database file?

    ReplyDelete
    Replies
    1. Use what is described here to output to an output file.

      You can then copy it to the IFS using what is described here to create a CSV, which can be opened by Excel, etc.

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.