How to identify flat files

The question was is there an easy way to identify "flat files" without having to use the DSPFD command. The answer, of course, is "Yes".

The questioner explained that a "flat file" was a file that was generated without the use of DDS or DDL. In other words, just with the Create Physical File command, CRTPF. For example:

CRTPF FILE(MYLIB/FLATFILE) RCDLEN(100)

The questioner was finding he could identify these "flat files" with the Display File Description command, DSPFD, like this:

DSPFD FILE(MYLIB/FLATFILE)

This displays the following screen:

MM/DD/YY Display File Description DSPFD Command Input File . . . . . . . . . . . . . . . : FILE FLATFILE Library . . . . . . . . . . . . . : MYLIB Type of information . . . . . . . . : TYPE *ALL File attributes . . . . . . . . . . : FILEATR *ALL System . . . . . . . . . . . . . . : SYSTEM *LCL File Description Header File . . . . . . . . . . . . . . . : FILE FLATFILE Library . . . . . . . . . . . . . . : MYLIB Type of file . . . . . . . . . . . : Physical File type . . . . . . . . . . . . . : FILETYPE *DATA Auxiliary storage pool ID . . . . . : 00001 Data Base File Attributes Externally described file . . . . . : No

The value to notice is the last one, "Externally described file", as it shows "No" I know that FLATFILE was not created using DDS.

The questioner wanted to have a way where he could generate a list of "flat files" in a particular library. In these examples I am going to use the library MYLIB as that library.

I can use the DSPFD command to generate a list of files in my library:

01 DSPFD FILE(MYLIB/*ALL) 02 TYPE(*MBR) 03 OUTPUT(*OUTFILE) 04 FILEATR(*PF) 05 OUTFILE(QTEMP/OUTFILE)

Line 1: I want to include all objects in my library.

Line 2: I want the output file for member information.

Line 3: I want the output to be a file.

Line 4: I only want to include physical files.

Line 5: My output file is called OUTFILE, and it will be created in the library QTEMP.

Once the file was generated I could use the following SQL statement to identify the "flat files":

01 SELECT MBFILE,MBFLS 02 FROM QTEMP.OUTFILE 03 WHERE MBFLS = 'N'

Line 1: I only want the file name, MBFILE, and the externally described file field, MBFLS.

Line 2: From the output file I generated from DSPFD.

Line 3: Only select rows where the externally described file field is "N".

The results of this statement are:

MBFILE MBFLS -------- ----- FLATFILE N

Why would I want to do it this way? Two steps is a waste of time and effort, when I can do it in one using the SYSFILES SQL View.

I want just two columns from SYSFILES:

1. SYSTEM_TABLE_NAME
2. PROGRAM_DESCRIBED:  "YES" means that this is a "flat file", when "NO" the file is externally described

To list the files in MYLIB and show whether they are program described my statement is:

01 SELECT SYSTEM_TABLE_NAME,PROGRAM_DESCRIBED 02 FROM QSYS2.SYSFILES 03 WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' 04 AND NATIVE_TYPE = 'PHYSICAL' 05 AND FILE_TYPE = 'DATA'

Line 1: I only want the columns I desire.

Line 2: From the View SYSFILES.

Line 3: I only want the results for the files in MYLIB.

Line 4: Only physical files or DDL Tables.

Line 5: And only data files, not source files.

I did not do the equivalent selection as lines 4 and 5 when using the output from DSPFD as the command created a file of just physical files. But the output file contained all physical files, data and source files.

A sample of the results were:

SYSTEM_TABLE_NAME PROGRAM_DESCRIBED ----------------- ----------------- REFFILE NO CUSTOMER NO DETAILS NO FLATFILE YES

If I only wanted to return rows that are "flat files" I would revise my statement to be:

01 SELECT SYSTEM_TABLE_NAME,PROGRAM_DESCRIBED 02 FROM QSYS2.SYSFILES 03 WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' 04 AND NATIVE_TYPE = 'PHYSICAL' 05 AND FILE_TYPE = 'DATA' 06 AND PROGRAM_DESCRIBED = 'YES'

Line 6: This line is the only change from the previous statement. It selects rows where the file is program described, "flat file".

SYSTEM_TABLE_NAME PROGRAM_DESCRIBED ----------------- ----------------- FLATFILE YES

This is another win for using just SQL. A single step solution that is quicker than using DSPFD.

 

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