CL Programming AS400: check fisical file members

Edited following @jmarkmurphy comment (***)

If you are on an AS400, and you are after field names starting with PT and not field contents starting with PT,

If IBM i ACS is available to you then great. ACS is a way of connecting to the AS400 which you may be using . In IBM i ACS run sql scripts you can run sql queries like the one below. You can be set up to save the output of any sql query to an excel spreadsheet.

Failing that if you are able to run CL command STRSQL from the command line you can run the same sql query below. Pressing shift and F1 (for F13) shows you a menu where option 1 is change Session Attributes. If you go in there you can select output 2 for printer. I have only used select output 3 for file. 1 for display is the default. You can set it back to this after.

select F1.table_name,count(*) counter from qsys2.systables F1
join qsys2.syscolumns F2
on F1.table_name=F2.table_name
where F1.table_type in ('P','T') -- physical files and sql tables (***)
and F1.table_schema='LIB1'     -- change LIB1 to your library
and F2.column_name like 'PT%'  -- field names starting with PT
group by F1.table_name         -- avoids duplicate file_names in output
                               -- instead you get a number with each file name
                               -- this is the number of fields starting with PT 
;                          -- if you in STRSQL you do not need the semi-colon

Note on the above: -- means everything after it on the line is a comment

= = =

Failing those 2 things you can do something like this,

run cl command,

dspffd file(LIB1/*ALL) OUTPUT(*OUTFILE) OUTFILE(QTEMP/X)

Then run cl command,

cpyf fromfile(qtemp/x) tofile(qtemp/x2) mbropt(*add) crtfile(*yes)
incchar(*RCD 130 *EQ 'PT') increl((*IF WHFTYP *EQ 'P'))

Again swap LIB1 for your library

Then you have a file in library qtemp called x2 containing the same information in the first column although this time the file names are repeated for every field found and you have not printed it yet. Hopefully it does not come to this but using WRKQRY you could create a query to remove duplicates and print file qtemp/x2. Or you could go into STRSEU and write an RPG program to print file qtemp/x2.

Good luck