SQL table function to view all 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.