Last week I started writing about the new table functions introduced in IBM i 7.4 TR 1 and 7.3 TR 7, 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:

Path name 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: