Wednesday, October 13, 2021

New SQL View gives DSPFD information

sql view sysfiles better than dspfd

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.

20 comments:

  1. Does anyone know if the PTF is supposed to only load into *SYSBAS library QSYS2? My company uses an IASP and our QSYS2 IASP library, QSYS200033, does not contain this view and the base QYSYS2.SYSFILEs does not show IASP libraries objects.

    -Matt

    ReplyDelete
    Replies
    1. Did you apply the database PTFs? The SQL improvements are in those PTFs.

      Delete
    2. Not my position but I can see SYSFILES in QSYS2 just not in QSYS200033 which is our IASP QSYS2 library.

      I am just curious if this is wide spread with others or just a case my company needs to open with IBM.

      -Matt

      Delete
  2. Simon, thanks a lot for sharing. This is very useful tool and provides great flexibility. Now I do not need to dspfd to outfile to find out number of deleted records or number of members. Can not wait till Monday to try on my system. Please continue your work on educating all of us of new features/ capabilities. Greatly appreciated.

    ReplyDelete
    Replies
    1. Tested today.
      Sysfiles has a lot of interesting information, however not what I was looking for.
      However, I found you post from 2015 which provided the answer based on SYSTABLESTAT.
      Thank you again.

      Delete
  3. I don't have QSYS2.SYSFILES on my system.

    ReplyDelete
    Replies
    1. If your partition is version 7.3 or 7.4, install and apply all the latest TR PTFs, including those for the database.

      If you are on an older release then this View is not available to you.

      Delete
  4. Is there a system table to get DSPF and PRTF information ?

    ReplyDelete
    Replies
    1. As you have found that information is not contained in SYSFILES.

      You can run DSPFFD for DSPF and PRTF. I have used that before to recreate the source of a PRTF.

      Delete
  5. Had high hopes that I could get to # of records. Is there another way to get to that with SQL

    ReplyDelete
  6. Simon, Thanks for your sharing.
    like dspfd *accpth
    Where can we see the file keys?

    ReplyDelete
  7. Simón, thanks for sharing, great read. Did not know I could do that with sql. Your examples are very good. Have a great day

    ReplyDelete
  8. Can we get the primary key from this way?

    ReplyDelete
  9. Is there a way to get thru SQL the same outcome as DSPFD (MYLIB/MYFILE) TYPE(*MBRLIST) ?

    ReplyDelete
  10. Try using the SYSPARTITIONSTAT SQL View, see here

    ReplyDelete
  11. Is there a way to find the 'Based on file' list with library for a logical file as part of DSPFD?

    ReplyDelete
    Replies
    1. That information can be found in the SQL View SYSFILES. For example:

      SELECT SYSTEM_TABLE_NAME,SQL_OBJECT_TYPE,BASED_ON_FILES
      FROM QSYS2.SYSFILES
      WHERE SYSTEM_TABLE_SCHEMA = 'your-library'
      AND NATIVE_TYPE = 'LOGICAL'

      More information is here.

      Delete
    2. If you want to break up the JSON array see here

      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.