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:
- 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:
This article was written for IBM i 7.4 TR1 and 7.3 TR7.
Hi Simon,
ReplyDeleteI'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?
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.
DeleteCAST(PATH_NAME AS CHAR(100) CCSID 37)
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