Retrieving file's keys with SQL, having to use DSPFD

In my last post I wrote about how to list a file's key fields using the file QADBKATR to retrieve the key fields' information from.

I mentioned that there may be times that you are not authorized to use the QADBKATR file. If you are not authorized how else can you get the key fields? And present them as I did in the previous example?

I have to fall back and use the Display File Description command, DSPFD, using the Access Path value in the "Type of information" parameter:

DSPFD FILE(MYLIB/TESTFILE) TYPE(*ACCPTH)

The key fields are found in the field APKEYF in the output file generated by DSPFD.

Before I start with code examples let me do a quick reminder. I have five "files" in my library, MYLIB:

• TESTFILE:  DDS physical file
• TESTFILEL0:  DDS logical file built over TESTFILE
• TESTVIEW:  SQL View built over TESTFILE. Being a View it does not have a key
• TESTTABLE:  SQL DDL Table
• TESTINDEX:  SQL DDL Index built over TESTTABLE

Before I can see the key fields I need to generate an output from the DSPFD:

DSPFD FILE(MYLIB/TEST*) TYPE(*ACCPTH) OUTPUT(*OUTFILE) OUTFILE(QTEMP/WORK0)

I can then list the key fields using the following SQL statement:

01 SELECT APLIB,APFILE,APKEYF 02 FROM QTEMP.WORK2

Which gives me the following results:

APLIB APFILE APKEYF ----- ---------- ------- MYLIB TESTFILE FIELD01 MYLIB TESTFILE FIELD02 MYLIB TESTFILEL0 FIELD05 MYLIB TESTFILEL0 FIELD04 MYLIB TESTFILEL0 FIELD03 MYLIB TESTFILEL0 FIELD02 MYLIB TESTFILEL0 FIELD01 MYLIB TESTTABLE COLUMN_1 MYLIB TESTTABLE COLUMN_2 MYLIB TESTINDEX COLUMN_5 MYLIB TESTINDEX COLUMN_4 MYLIB TESTINDEX COLUMN_3 MYLIB TESTINDEX COLUMN_2 MYLIB TESTINDEX COLUMN_1 MYLIB TESTVIEW

I can take those results and use the LISTAGG scalar function to aggregate all of those rows into one row for each file with the key fields in a new column:

01 SELECT APLIB,APFILE,LISTAGG(RTRIM(APKEYF),',') 02 FROM QTEMP.WORK2 03 GROUP BY APLIB,APFILE

Which gives me:

APLIB APFILE 00003 ----- ---------- -------------------------------------------- MYLIB TESTFILE FIELD01,FIELD02 MYLIB TESTFILEL0 FIELD05,FIELD04,FIELD03,FIELD02,FIELD01 MYLIB TESTINDEX COLUMN_5,COLUMN_4,COLUMN_3,COLUMN_2,COLUMN_1 MYLIB TESTTABLE COLUMN_1,COLUMN_2 MYLIB TESTVIEW

Onto the example program showing how to use the output from DSPFD. A lot of it is the same as the previous program:

01 **free 02 ctl-opt main(Main) option(*srcstmt) ; 03 dcl-proc Main ; 04 dcl-pi *n ; 05 inLibrary char(10) ; 06 end-pi ; 07 dcl-s String varchar(350) ; 08 exec sql CALL QSYS2.QCMDEXC('DLTF QTEMP/WORK*') ; 09 exec sql DROP TABLE IF EXISTS QTEMP.FINAL ; 10 String = 'CREATE TABLE QTEMP.WORK0 AS + 11 (SELECT SYSTEM_TABLE_SCHEMA,SYSTEM_TABLE_NAME,+ 12 NATIVE_TYPE,FILE_TYPE,B.LAST_USED_TIMESTAMP + 13 FROM QSYS2.SYSFILES, + 14 LATERAL + 15 (SELECT LAST_USED_TIMESTAMP + 16 FROM TABLE(QSYS2.OBJECT_STATISTICS(+ 17 SYSTEM_TABLE_SCHEMA,''FILE'',+ 18 SYSTEM_TABLE_NAME))) B + 19 WHERE SYSTEM_TABLE_SCHEMA = ''' + %trimr(inLibrary) + 20 ''') WITH DATA' ; 21 exec sql EXECUTE IMMEDIATE :String ; 22 exec sql CREATE TABLE QTEMP.WORK1 (RETURN_CODE) AS 23 (SELECT QCMDEXC('DSPFD FILE(' || SYSTEM_TABLE_SCHEMA || 24 '/' || SYSTEM_TABLE_NAME || 25 ') TYPE(*ACCPTH) OUTPUT(*OUTFILE) ' || 26 'OUTFILE(QTEMP/WORK2) OUTMBR(*FIRST *ADD)') 27 FROM QTEMP.WORK0) 28 WITH DATA ; 29 exec sql CREATE TABLE QTEMP.FINAL AS 30 (SELECT A.*,B.KEY_FIELDS 31 FROM QTEMP.WORK0 A, 32 LATERAL 33 (SELECT LISTAGG(RTRIM(APKEYF),',') AS KEY_FIELDS 34 FROM QTEMP.WORK2 35 WHERE (APLIB,APFILE) = (A.SYSTEM_TABLE_SCHEMA, 36 A.SYSTEM_TABLE_NAME) 37 GROUP BY APLIB,APFILE) B) 38 WITH DATA ; 39 end-proc ;

Line 1: My code is always modern RPG.

Line 2: I am using a Main procedure, which I am calling "Main".

Line 3: Start of the Main procedure.

Lines 4 – 6: This is the procedure interface for the Main procedure. The library name is passed to this program.

Line 7: Declaration of a variable that will be used to contain the first SQL statement.

Line 8: I will be creating several work files, whose names will start with the characters "WORK". If they already exist in this job's QTEMP I want to delete them. I could have had two DROP TABLE statements here. But I decided to use one Delete file command, DLTF, executed by the QCMDEXC SQL procedure.

Line 9: This file will contain the file data. I need to delete it too if it already exists. As this is just one file I can use the DROP statement. To stop an error if the Table does not exist I use the IF EXISTS.

Lines 10 – 20: This is the SQL statement that will be used to create the first work file, WORK0. It is the same SQL statement as I used in the other program. As I cannot use the passed library parameter in the WHERE clause, line 19, I am placing this statement in the variable String. Why did I use the RPG %TRIMR BiF rather than SQL's RTRIM? I chose to use so that the SQL statement would be a little shorter than it would have been if I had used the RTRIM.

Line 21: I use the EXECUTE IMMEDIATE statement to execute the SQL statement contained in the String variable.

Before I explain what is happening in the lines 22 – 28 I need to give a separate example. I have a list of all the files I care about in the WORK0 file, and I only want to perform the DSPFD command for those files. I need to be to execute a DSPFD for each row in WORK0. Fortunately, I can use the QCMDEXC scalar function to perform the DSPFD for each returned result.

I know the statement below looks messy, as I had to format it to fit the text in the width of this page:

01 SELECT SYSTEM_TABLE_SCHEMA AS "Lib",SYSTEM_TABLE_NAME AS "File", 02 'DSPFD FILE(' || SYSTEM_TABLE_SCHEMA || '/' || SYSTEM_TABLE_NAME || 03 ') TYPE(*ACCPTH) OUTPUT(*OUTFILE) OUTFILE(QTEMP/WORK2) OUTMBR(*FIRST *ADD)' AS "Cmd", 04 QCMDEXC('DSPFD FILE(' || SYSTEM_TABLE_SCHEMA || '/' || SYSTEM_TABLE_NAME || 05 ') TYPE(*ACCPTH) OUTPUT(*OUTFILE) OUTFILE(QTEMP/WORK2) OUTMBR(*FIRST *ADD)') 06 AS "RtnCode" 07 FROM QTEMP.WORK0

Line 1: Is the file library and name.

Lines 2 and 3: I am building the DSPFD for the file returned in the results.

Lines 4 – 6: This is the QCMDEXC scalar function. The command string is being built to be the same as what is generated on lines 2 and 3.

The results are:

Lib File Cmd RtnCode ----- ---------- --------------------------------------------- ------- MYLIB TESTFILE DSPFD FILE(MYLIB/TESTFILE) TYPE(*ACCPTH)... 1 MYLIB TESTFILEL0 DSPFD FILE(MYLIB/TESTFILEL0) TYPE(*ACCPT... 1 MYLIB TESTTABLE DSPFD FILE(MYLIB/TESTTABLE) TYPE(*ACCPTH... 1 MYLIB TESTINDEX DSPFD FILE(MYLIB/TESTINDEX) TYPE(*ACCPTH... 1 MYLIB TESTVIEW DSPFD FILE(MYLIB/TESTVIEW) TYPE(*ACCPTH)... 1

I cannot show the complete command above as there is not enough room. If I copy value from the command column from the first row I get:

DSPFD FILE(MYLIB/TESTFILE) TYPE(*ACCPTH) OUTPUT(*OUTFILE) OUTFILE(QTEMP/WORK2) OUTMBR(*FIRST *ADD)

The output member parameter has to be '*ADD' so that all the results are added to the output file WORK2.

Back to the program:

Lines 22 – 28: Run the DSPFD command for all of the rows in the WORK0 file. I need to create a file for this so that the return codes from the QCMDEXC scalar function have somewhere to go. I have no interest in the file WORK1 once this statement has completed. I am interested in the file WORK2 as that contains the outputs from the DSPFD command for each "file" in WORK0.

Lines 29 – 38: In this final statement I am joining the data from WORK0 to the data from WORK2. The key fields in WORK2 need to be aggregated into one column, which is appended to the end of the columns from WORK0.

When the program has completed I can query the FINAL file:

SELECT * FROM QTEMP.FINAL

Which gives me the following results:

SYSTEM_ SYSTEM_ TABLE_ TABLE_ NATIVE FILE LAST_USED_ SCHEMA NAME _TYPE _TYPE TIMESTAMP KEY_FIELDS ------- ---------- -------- ----- -------------- -------------------------------------------- MYLIB TESTFILE PHYSICAL DATA 2024-03-05-... FIELD01,FIELD02 MYLIB TESTFILEL0 LOGICAL DATA <NULL> FIELD05,FIELD04,FIELD03,FIELD02,FIELD01 MYLIB TESTTABLE PHYSICAL DATA 2024-03-05-... COLUMN_1,COLUMN_2 MYLIB TESTINDEX LOGICAL DATA <NULL> COLUMN_5,COLUMN_4,COLUMN_3,COLUMN_2,COLUMN_1 MYLIB TESTVIEW LOGICAL DATA <NULL>

Which are the same as the way I generated them previously. Which method would I use? I would use the method in my previous post as its program ran faster than this program. But if you are not authorized to QADBKATR then this method is an acceptable alternative.

 

This article was written for IBM i 7.5 TR3 and 7.4 TR9.