Wednesday, May 15, 2024

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.

No comments:

Post a Comment

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.