Wednesday, January 13, 2021

SQL table function to view all open files

using sql to get list of all job open files

One of the many additions to SQL in the latest round of Technology Refreshes, IBM i 7.3 TR3 and 7.3 TR9, is a Table function that displays a job's open files.

I can see the same information using the Display Job command, DSPJOB, option 14. But there are times I might want to access the same information in a manner that I can get to easily in a program.

The Open Files table function, OPEN_FILES, is found in the QSYS2 library. It has only one parameter, the job name. If you want to retrieve the information for the current job you can use "*" rather than give the full job. Retrieving the current job's name requires minimal effort as it is a "built in" global variable, JOB_NAME.

The following SQL statements would return the results for current job:

SELECT * FROM TABLE(QSYS2.OPEN_FILES(JOB_NAME => '*')) ;

SELECT * FROM TABLE(QSYS2.OPEN_FILES(JOB_NAME => JOB_NAME)) ;

SELECT * FROM TABLE(QSYS2.OPEN_FILES('*')) ;

SELECT * FROM TABLE(QSYS2.OPEN_FILES(JOB_NAME)) ;

As this table function only has one parameter I see no reason why I would ever give the parameter's name, as is shown in the first two examples.

This function gets interesting when I use it with a job that is opening files. I put together a small RPG program with a few files, performed some file I/O, opened a SQL cursor, and displayed a display file record format so I would have some information to show here.

01  **free
02  dcl-f TESTDSPF workstn ;
03  dcl-f TESTFILE ;
04  dcl-f TESTFILEL0 usage(*output) 
                       rename(TESTFILER:TESTFILER0) ;

05  dcl-s Counter like(NUMBER) ;

06  for Counter = 1 to 10 ;
07    read TESTFILER ;
08  endfor ;

09  for NUMBER = 1 to 5 ;
10    write TESTFILER0 ;
11  endfor ;

12  exec sql SET OPTION COMMIT = *NONE ;

13  exec sql DECLARE C0 CURSOR FOR
               SELECT * FROM TESTTABLE
                  FOR READ ONLY ;

14  exec sql OPEN C0 ;

15  exec sql FETCH NEXT FROM C0 INTO :NUMBER ;

16  exfmt SCREEN ;

17  exec sql CLOSE C0 ;

18  *inlr = *on ;

Line 1: I only write totally free format RPG as it is so much easier to write and understand, easier than using columns.

Lines 2 – 4: Defining the files I will be using in this program. The first is a display file. The second is a physical file that will be used for input only. And the third a logical file I will be using for output only.

Line 5: Define a variable I will be using.

Lines 6 – 8: Using a For group to read the physical file 10 times.

Lines 9 – 11. Using a another For group to write five records to the logical file.

Line 13: Declare a SQL cursor for fetching data from a SQL DDL table. As I have used FOR READ ONLY the table will be opened for input only.

Line 15: The table defined in the cursor only contains one column, therefore, when I fetch a single row I can place that value from the result into the variable NUMBER.

Line 16: Display the record format SCREEN. This is the point where I can run my SQL statements with the OPEN_FILES table function in them to see all the open files and record counts.

I call this program and when the record format is displayed I go to "Run SQL scripts" and run the following statement:

SELECT LIBRARY_NAME,
       FILE_NAME,
       FILE_TYPE,
       MEMBER_NAME,
       DEVICE_NAME,
       RECORD_FORMAT,
       RELATIVE_RECORD_NUMBER AS "RRN",
       ACTIVATION_GROUP_NAME,
       OPEN_OPTION, 
       WRITE_COUNT,
       READ_COUNT,
       WRITE_READ_COUNT,
       OTHER_IO_COUNT
  FROM TABLE(QSYS2.OPEN_FILES('730154/SIMON/QPADEV0002'))

I am only interested in some of the columns returned by the table function. You will find a list of all the available columns in the IBM documentation that is in a link at the bottom of this post. I have used the job name of the 5250 session I ran the program in. I have separated the results into three rows as they would not be too wide to show here.

LIBRARY_NAME  FILE_NAME   FILE_TYPE  MEMBER_NAME  DEVICE_NAME
------------  ----------  ---------  -----------  -----------
QSYS          QDUI132     DSPF       -            QPADEV0002
QPDA          QDUODSPF    DSPF       -            QPADEV0002
QSYS          QADBXRMTNM  LF         QADBXRMTNM   -
QSYS2         QASQVRSS    LF         QASQVRSS     -
MYLIB         TESTFILEL0  LF         TESTFILEL0   -
MYLIB         TESTFILE    PF         TESTFILE     -
MYLIB         TESTDSPF    DSPF       -            QPADEV0002
QTEMP         TESTTABLE   PF         TESTTABLE    -

RECORD_FORMAT  RRN  ACTIVATION_GROUP_NAME  OPEN_OPTION
-------------  ---  ---------------------  -----------
.                .  .                      .
.                .  .                      .
.                .  .                      .
.                .  .                      .
TESTFILER        6  *DFTACTGRP             OUTPUT
TESTFILER       10  *DFTACTGRP             INPUT
SCREEN           -  *DFTACTGRP             ALL
-                -  *DFTACTGRP             INPUT


WRITE_COUNT  READ_COUNT  WRITE_READ_COUNT  OTHER_IO_COUNT
-----------  ----------  ----------------  --------------
          .           .                 .               .
          .           .                 .               .
          .           .                 .               .
          .           .                 .               .
          5           0                 0               0
          0          10                 0               0
          0           0                 0               1
          0           1                 0               0

The first and third results are system files I always see in every 5250 job with a display file.

The second result I only see when I display a display file record format.

The fourth is something to do with the program running SQL statements.

I am only interested in the next four results, which are my files and table. Therefore, I have removed the data for the system files from the second and third rows of the results.

The FILE_TYPE shows me the system type of the object. The SQL table is considered a physical file.

The OPEN_TYPE shows how the files have been opened.

The entire third row is unique to the OPEN_FILES table function. The DSPJOB command displays the total "I/O count", it does not break it down into the various types. It is interesting to see that the EXFMT operation in the RPG program is considered "Other I/O".

This is another example of good functionality being added to SQL to get system information that was previously only available either on a screen or by using a complex API.

 

You can learn more about the SQL table function OPEN_FILES from the IBM website here.

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

4 comments:

  1. "As this table function only has one parameter I see no reason why I would ever give the parameter's name, as is shown in the first two examples." So that you can have code that will still operate without changes when/if IBM decides to change the interface.
    _-Matt

    ReplyDelete
  2. Simon, thanks for sharing. Great example of the function.

    ReplyDelete
  3. Thanks for sharing

    ReplyDelete

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.