New SQL View gives DSPFD information

I have wanted to have a way to get to the same information via SQL that I can by using the Display File Description command, DSPFD. This absence has often led me to having to use the DSPFD command to create an output file, that I would then use SQL to gather the results I desired.

My wishes were answered in the Fall 2021 Technology Refreshes, IBM i 7.4 TR5 and IBM i 7.3 TR11, with the introduction of a new SQL view: SYSFILES.

Unlike the DSPFD command SYSFILES only contains data about what I would call "data files", what the DSPFD calls "*PF" and "*LF". Source files are included, but the column FILE_TYPE allows me to differentiate between source and data files.

The simplest Select statement to use this view is:

SELECT * FROM QSYS2.SYSFILES

I encourage you to run the above statement at least once to see all of the columns that the results will return.

The view returns results from all the libraries on your partition. This allows you to search across multiple libraries for the results I want.

There is so much useful information in this view I have multiple examples of the kind of searches I do.

In my first example I want to make a list of all the files where the SQL "long name" is different from the IBM i "short name":

01 SELECT SYSTEM_TABLE_NAME,TABLE_NAME 02 FROM QSYS2.SYSFILES 03 WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' 04 AND SYSTEM_TABLE_NAME <> TABLE_NAME

Line 1: I only want the results from two columns returned:

1. SYSTEM_TABLE_NAME:  The system (short) table name
2. TABLE_NAME:  The long (SQL) table name

Line 3: I am only interested with the objects in my library.

Line 4: And I only want the rows returned where the system table name and table name are different.

The results look like:

SYSTEM_TABLE _NAME TABLE_NAME ------------ --------------------- TESTTABLE THIS_IS_MY_TEST_TABLE EXITSSAVED SAVED_EXITS

In my next example I want to see all of the source members my files were compiled from:

01 SELECT SYSTEM_TABLE_NAME,FORMAT_NAME, 02 SOURCE_FILE_LIBRARY,SOURCE_FILE,SOURCE_FILE_MEMBER, 03 ACCESS_PATH_TYPE 04 FROM QSYS2.SYSFILES 05 WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' 06 AND FILE_TYPE = 'DATA'

Line 1: In my results I want to see:

1. SYSTEM_TABLE_NAME
2. FORMAT_NAME:  Record format name
3. SOURCE_FILE_LIBRARY:  Library the source file is in
4. SOURCE_FILE:  Name of the source file
5. SOURCE_FILE_MEMBER:  Source file member's name
6. ACCESS_PATH_TYPE:  Access path type

Line 5: I am only interested in files in my own library.

Line 6: And data files, not source files.

A sample of my results looks like:

SOURCE SOURCE ACCESS SYSTEM_ FORMAT _FILE SOURCE _FILE _PATH TABLE NAME _NAME _LIBRARY _FILE _MEMBER _TYPE ----------- ---------- -------- ------- ---------- -------------- TESTFILEX0 TESTFILER MYLIB DEVSRC TESTFILEX0 KEYED NO ORDER TESTFILEL0 TESTFILER MYLIB DEVSRC TESTFILEL0 KEYED NO ORDER FLATDTL FLATDTL <NULL> <NULL> <NULL> <NULL> TESTFILE TESTFILER MYLIB DEVSRC TESTFILE <NULL> TESTTABLE TESTTABLE MYLIB DEVSRC TESTTABLE KEYED UNIQUE

All but one of the files was compiled from source members in the DEVSRC source file. FLATDTL was created using the Create Physical File command, CRTPF, where I entered in the record length of the file.

FLATDTL and TESTFILE do not have a key. TESTFILEX0 and TESTFILEL0 have a non-unique key. Only TESTABLE has a unique key.

I can modify the above statement and get a list of all the objects compiled from source members in MYLIB, but the object is in another library:

01 SELECT SYSTEM_TABLE_SCHEMA,SYSTEM_TABLE_NAME, 02 SOURCE_FILE_LIBRARY,SOURCE_FILE,SOURCE_FILE_MEMBER 03 FROM QSYS2.SYSFILES 04 WHERE SOURCE_FILE_LIBRARY = 'MYLIB' 05 AND SOURCE_FILE_LIBRARY <> SYSTEM_TABLE_SCHEMA 06 AND FILE_TYPE = 'DATA' 07 ORDER BY 1,2

Line 1: I added the SYSTEM_TABLE_SCHEMA to the results so that I would know in which library these objects are in.

Line 4: The source file that the objects were created from has to be in MYLIB.

Line 5: The source file library and the library the object is in cannot be the same.

Line 7: I want to sort the results by the first, library, and second, file name, columns.

My results are as follows:

SYSTEM_ SYSTEM_ SOURCE_ SOURCE_ TABLE_ TABLE_ FILE_ SOURCE_ FILE_ SCHEMA NAME LIBRARY FILE MEMBER ------- --------- ------- ------- ------- MYLIB2 TESTFILE1 MYLIB DEVSRC TESTFILE1 MYLIB3 OTABLE MYLIB DEVSRC OVIEW MYLIB3 OVIEW MYLIB DEVSRC OVIEW MYLIB4 TESTFILE1 MYLIB DEVSRC TESTFILE1 MYLIB5 CHK_FILE MYLIB DEVSRC CHK_FILE

Another useful piece of information I can retrieve is the SQL type of an object. The operating system regards both SQL indexes and views are Logical files. As we know the two are very different. Using this View I can make a list of all the "logical" files in the library and see what their SQL type is:

01 SELECT SYSTEM_TABLE_NAME,NATIVE_TYPE,SQL_OBJECT_TYPE, 02 SELECT_OMIT 03 FROM QSYS2.SYSFILES 04 WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' 05 AND NATIVE_TYPE = 'LOGICAL'

Line 1: The columns I want in my results are:

1. SYSTEM_TABLE_NAME
2. NATIVE_TYPE:  What the operating system considers the object to be.
3. SQL_OBJECT_TYPE  What SQL considers the object to be.
4. SELECT_OMIT  Does the "Logical file" contain a select or omit statement?

Line 4: I am only interested in the objects in my library.

Line 5: And only what are considered by the operating system as "Logical" files.

The results are:

SYSTEM_TABLE SQL_OBJECT SELECT_ _NAME NATIVE_TYPE _TYPE OMIT ------------ ----------- ---------- ------- TESTFILEX0 LOGICAL INDEX NO TESTFILEL0 LOGICAL <NULL> NO AVAILSPACE LOGICAL VIEW NO PERSONV0 LOGICAL VIEW NO

TESTFILEL0 does not have a SQL object type as it is a DDS logical file.

The following statement is a combination of several different pieces of information I find useful.

01 SELECT SYSTEM_TABLE_NAME AS "File", 02 NATIVE_TYPE AS "Type", 03 LEVEL_CHECK AS "Lvl chk", 04 MAXIMUM_MEMBERS AS "Mbrs", 05 MAXIMUM_RECORD_LENGTH AS "Length", 06 TRIGGER_COUNT AS "Trig", 07 FORMAT_LEVEL_ID AS "Format id", 08 FORMAT_NAME AS "Format", 09 NUMBER_FIELDS AS "Flds", 10 REUSE_DELETED_RECORDS AS "Reuse" 11 FROM QSYS2.SYSFILES 12 WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' 12 AND FILE_TYPE = 'DATA' 13 ORDER BY 1

Lines 1 – 10: These are the columns I often want information about a file. I have given the result columns new names to fit the results on the width of this page.

1. SYSTEM_TABLE_NAME
2. NATIVE_TYPE
3. LEVEL_CHECK:  Equivalent of LVLCHK(*YES) or LVLCHK(*NO).
4. MAXIMUM_MEMBERS:  Maximum number of members the file can have.
5. MAXIMUM_RECORD_LENGTH:  Maximum record length. With varying length fields/column the actual record length can vary.
6. TRIGGER_COUNT:  Number of triggers added to the file.
7. FORMAT_LEVEL_ID:  Record format level id.
8. FORMAT_NAME:  Record format name.
9. NUMBER_FIELDS:  Number of fields/columns in the file.
10. REUSE_DELETED_RECORDS:  Does the file reuse deleted records.

The first ten results look like:

File Type Lvl chk Mbrs Length Trig ---------- -------- ------- ---- ------ ---- AVAILSPACE LOGICAL YES 1 42 0 EXITSSAVED PHYSICAL YES 1 2342 0 EXP_LICS PHYSICAL YES 1 127 0 FILE1 PHYSICAL YES 1 72 0 FILE2 PHYSICAL YES 1 72 0 FLATDTL PHYSICAL YES 1 50 0 FLATHDR PHYSICAL YES 1 30 0 LCL_OBJS PHYSICAL YES 1 180 0 NOT_THIS_1 PHYSICAL YES 1 6 0 NOT_THIS_2 PHYSICAL YES 1 6 0 Format id Format Flds Reuse ------------- ---------- ---- ----- 41C3E083DEC85 AVAILSPACE 6 <NULL> 4ACE5BC1F8816 EXITSSAVED 17 YES 393163BD715F5 EXP_LICS 4 YES 2D0C141358D98 FILE1 2 YES 2D1C24236909A FILE2 2 YES 35DE3031EA211 FLATDTL 1 NO 348CA090E76CF FLATHDR 1 NO 4533520B17F54 LCL_OBJS 10 YES 21AB780C7DD77 DUMMY 1 YES 21AB780C7DD77 DUMMY 1 YES

The reuse deleted records column is null for AVAILSPACE as this is an attribute of a file or table, rather than a logical file, view, or index.

All of my previous examples have been looking at data files. This last one is just so I can get a list of all the source files in a library, and the number of source members contained within.

01 SELECT SYSTEM_TABLE_NAME,NUMBER_MEMBERS,MAXIMUM_RECORD_LENGTH 02 FROM QSYS2.SYSFILES 03 WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' 04 AND FILE_TYPE = 'SOURCE'

Line 1: My previous examples included all of these columns.

Line 4: This time the file type is "SOURCE".

SYSTEM_ NUMBER_ MAXIMUM_RECORD TABLE_NAME MEMBERS _LENGTH ---------- ------- -------------- DEVSRC 493 112 FTPSRC 0 112 OLDSRC 26 112

The above shows that I use the source file DEVSRC more than the others. And the source member length shows I always create my source file with a record length of 100.

CRTSRCPF FILE(MYLIB/source-file-name) RCDLEN(100)

The source member length (100) + source sequence number (6) + date (6) = 112.

I am loving this view, and I know that I will be using this a lot.

 

You can learn more about the SYSFILES SQL view from the IBM website here.

 

This article was written for IBM i 7.4 TR5 and 7.3 TR11.