Wednesday, November 27, 2019

More SQL Table Functions for interrogating IFS

More sql table functions for viewing files and objects in the ifs

Last week I started writing about the new table functions introduced in IBM i 7.4 TR1 and 7.3 TR7, describing the first of the four table functions that allow me to retrieve information about IFS directories and files. In this post I will describe the other three:

 

IFS_OBJECT_LOCK_INFO

The IFS_OBJECT_LOCK_INFO table function returns a results row for each job that is holding a lock on the object in the given path.

If you were use this in a Select statement there are two forms of syntax:

SELECT * 
FROM TABLE(QSYS2.IFS_OBJECT_LOCK_INFO(PATH_NAME => 'path name'))

SELECT * 
FROM TABLE(QSYS2.IFS_OBJECT_LOCK_INFO('path name'))

As there is only one parameter passed to this table function, the path name, personally I would never bother with the "PATH_NAME =>".

I used a RPG program to open a file in the IFS, and wrote a couple of records to it. I did not use the API to close the file, therefore, at the time I executed this Select statement the IFS file was still open and locked.

There are many more columns returned by the table function than I am going to show in this example. If you are interested in learning about all of them click on the link to the IBM documentation at the bottom of this post.

Here I only care about:

Column name Description
PATH_NAME Path name of the object
JOB_NAME Qualified job name that is locking the file or directory
WO_COUNT Number of writes performed to this object in the job

My Select statement looks like:

SELECT PATH_NAME,JOB_NAME,WO_COUNT
FROM TABLE(QSYS2.IFS_OBJECT_LOCK_INFO('/MyFolder/test_file.txt'))

The results look like:

PATH_NAME               JOB_NAME                WO_COUNT
----------------------- ----------------------- --------
/MyFolder/test_file.txt 675090/SIMON/QPADEV0001        2

The value in the WO_COLUMN is two as I had written to the file twice.

If no rows are returned in the results then the object is not locked.

 

IFS_JOB_INFO

IFS_JOB_INFO is almost the opposite of IFS_OBJECT_LOCK_INFO, while IFS_OBJECT_LOCK_INFO looks at what jobs are using an object, IFS_JOB_INFO looks at which directories and objects a job is using.

The syntax is similar too, but IFS_JOB_INFO only input parameter is the fully qualified job name:

SELECT * 
FROM TABLE(QSYS2.IFS_JOB_INFO(JOB_NAME => 'job name'))  ;

SELECT * 
FROM TABLE(QSYS2.IFS_JOB_INFO('job name'))  ;

As there is only one input parameter I am not going to bother with entering "JOB_NAME =>" in my statements.

Most of the result columns are the same as those from IFS_OBJECT_LOCK_INFO. The columns I am interested in for this example are:

Column name Description
PATH_NAME Path name of the object
FILE_SYSTEM_TYPE File system the object is found in

NFS: Network File System
QDLS: Document Library Services (QDLS) file system
QFILSVR400: QFileSvr.400 file system
QNTC: Windows NT Server file system
QOPENSYS: QOpenSys file system
QOPT: Optical file system
QSYS: QSYS>LIB or IBM i file system
QSYSIASP: An independent ASP QSYS.LIB file system
ROOT: The root (/) file system
UDFS: A user-defined file system
UDFS MANAGEMENT: A file system that manages the block special files (*BLKSF) for the user-defined file systems

You will find a link to IBM's documentation, which lists all of the columns, at the bottom of this post.

Let me see which objects this table function has identified that I am using with my current job:

SELECT PATH_NAME,FILE_SYSTEM_TYPE
FROM TABLE(QSYS2.IFS_JOB_INFO(JOB_NAME => '675090/SIMON/QPADEV0001'))

The results list the path to the file I had locked using the same RPG program as before.

PATH_NAME                                     FILE_SYSTEM_TYPE
--------------------------------------------  ----------------
/                                             ROOT
/home/MyFolder                                ROOT
/MyFolder/test_file.txt                       ROOT

As the documentation says I can also include files from the QSYS.LIB (IBM i) file system I tried to use the C APIs to lock a typical DDS file, TESTFILE:

PathFile = '/QSYS.LIB/MYLIB.LIB/TESTFILE.FILE/TESTFILE.MBR' + x'00' ;

When I ran the RPG program I received the following message:

Message ID . . :  CPDA033       Severity . . :  50
Message type . :  Diagnostic

Message . . . . : Could not open member TESTFILE in
     file TESTFILE in library MYLIB.
Cause . . . . . : The open of member TESTFILE in file
TESTFILE in library MYLIB failed. Reason code 1 
indicates why the request failed:
01 -- Unsupported file type.  If opening for text mode,
the file must be either a data physical file which is
program-described, or a source physical file which has
only one data field. If opening for data mode 
(binary), the file must be either a data physical file
(program-described or externally described), or a
source physical file which has only one data field.

I am trying to open the file using the C API in text mode, therefore, I cannot open TESTFILE. As this is just an example I can open a member in my source file DEVSRC:

PathFile = '/QSYS.LIB/MYLIB.LIB/DEVSRC.FILE/TESTFILE.MBR' + x'00' ;  

When I run the SQL statement again this time I have a results row for the member in my source file, and the file system type shows it is from the QSYS.LIB file system:

PATH_NAME                                     FILE_SYSTEM_TYPE
--------------------------------------------  ----------------
/                                             ROOT
/home/MyFolder                                ROOT
/MyFolder/test_file.txt                       ROOT
/QSYS.LIB/MYLIB.LIB/DEVSRC.FILE/TESTFILE.MBR  QSYS

 

IFS_OBJECT_REFERENCES_INFO

This table function returns a single row result containing information about the object usage. It can only be used for objects in the root, QOpenSys, and user defined file systems.

IFS_OBJECT_REFERENCES_INFO has two input parameters:

  1. Path name
  2. Include detail information information

The second parameter is optional, and if it is not given the request "NO" is assumed.

The syntax in a Select statement would be:

SELECT *
FROM TABLE(QSYS2.IFS_OBJECT_REFERENCES_INFO(PATH_NAME => 'path name',
                                            DETAILED_INFO => 'YES'))

SELECT *
FROM TABLE(QSYS2.IFS_OBJECT_REFERENCES_INFO(PATH_NAME => 'path name',
                                            DETAILED_INFO => 'NO'))

SELECT *
FROM TABLE(QSYS2.IFS_OBJECT_REFERENCES_INFO('path name','YES'))
 
SELECT *
FROM TABLE(QSYS2.IFS_OBJECT_REFERENCES_INFO('path name'))

I see no reason to use the parameter names, therefore, my examples will not include them.

In this example I am only interested in three columns from the results:

Column name Description
PATH_NAME Path name of the object
IN_USE Is the object currently being used
REFERENCE_COUNT Current number of references using the object

To best illustrate this I am going to have two Select statements, one for a file that is being used and another for a file that is not being used, the statements are joined with a UNION clause.

SELECT PATH_NAME,IN_USE,REFERENCE_COUNT FROM
TABLE(QSYS2.IFS_OBJECT_REFERENCES_INFO('/MyFolder/test_file.txt'))
UNION ALL
SELECT PATH_NAME,IN_USE,REFERENCE_COUNT FROM
TABLE(QSYS2.IFS_OBJECT_REFERENCES_INFO('/MyFolder/xmlfile.xml'))

One set of results are returned with two rows, one for each file.

PATH_NAME                IN_USE  REFERENCE_COUNT
-----------------------  ------  ---------------
/MyFolder/test_file.txt  YES     1
/MyFolder/xmlfile.xml    NO      0

test_file.txt is being used by one job, hence the REFERENCE_COUNT is 1.

If I combine the results from IFS_OBJECT_STATISTICS with this table function I can have a list of all the files in an IFS folder and whether they are being used:

SELECT A.PATH_NAME,B.IN_USE,B.REFERENCE_COUNT
FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS('/MyFolder')) A
CROSS JOIN TABLE(QSYS2.IFS_OBJECT_REFERENCES_INFO(A.PATH_NAME)) B

I am using the path name returned from IFS_OBJECT_STATISTICS as the parameter for IFS_OBJECT_REFERENCES_INFO, and using a CROSS JOIN to link the two sets of results into one.

PATH_NAME                IN_USE  REFERENCE_COUNT
-----------------------  ------  ---------------
/MyFolder                NO      0
/MyFolder/report.csv     NO      0
/MyFolder/list.txt       NO      0
/MyFolder/xmlfile.xml    NO      0
/MyFolder/test_file.txt  YES     1

Why would I not join this to IFS_OBJECT_LOCK_INFO?

If there was more than one job using one of these files I would get more than one row returned in the results, one for each job name taken from IFS_OBJECT_LOCK_INFO. Personally I think that would make the results confusing as I only wanted to know which files are being used.

 

You can learn more about this from the IBM website:

 

This article was written for IBM i 7.4 TR1 and 7.3 TR7.

3 comments:

  1. Hi Simon,

    I've been trying to use this as an embedded SQL statement in a SQLRPGLE program, but it seems to only work if you hard-code the path name.

    If I try to use a variable for the path name it throws a SQL0332 error.

    I've tried using DBCLOB as I think that may have something to do with it, but to no avail.

    Would you be able to supply an example of using this with a variable foor the path name please?

    ReplyDelete
    Replies
    1. RPG does tend to use strict SQL rules. As such the path name is in CCSID 1200. If that is not the default CCSID for your partition it may be better for you to do what I do.
      CAST(PATH_NAME AS CHAR(100) CCSID 37)

      Delete
    2. Thanks Simon, I actually meant as a parameter into the ifs_object_references_info function, but your reply gave me the clue I needed: define the variable as CCSID(37), which worked.

      Delete

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.