Searching for empty IFS folders using SQL

I was asked how to make a list of empty IFS folders using SQL. My first thought was to go to the table function IFS_OBJECT_STATISTICS. By using this table function I can retrieve a list of, for examples, just folders or the objects in a particular folder.

My thought was to do this in two steps:

1. Make a list of all the folders
2. Generate a count of the objects in each folder

I used a Common Table Expression, CTE. The CTE would allow me to create a temporary table, which only exists in memory, rather than create a physical table that would exist on disk.

The statement I created looks like:

01 WITH T1(FOLDER) 02 AS (SELECT PATH_NAME 03 FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS('/','YES','*ALLDIR *NOQSYS *NOQOPT', 'YES'))), 04 T2(FOLDER,COUNT) 05 AS (SELECT T1.FOLDER,F2 06 FROM T1, 07 LATERAL(SELECT (COUNT(*) - 1) 08 FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS(T1.FOLDER,'YES',IGNORE_ERRORS => 'YES'))) B(F2) 09 WHERE F2 = 0) 10 SELECT * FROM T2 11 ORDER BY FOLDER

My CTE is divided into three parts:

1. Create the temporary table T1, which contains the list of all the IFS folders: Lines 1 - 3
2. Create the temporary table T2, which contains a list of all the folders that contain no objects: Lines 4 - 9
3. Display the results: Lines 10 and 11

Line 1: All CTEs start with WITH followed by the name of the temporary table, I am calling mine T1, and a list of the columns it contains. In this case I only want to have the folder name, which is the table function's column PATH_NAME.

Line 2: AS is followed by the SQL statement that is used to generate the contents for T1.

Line 3: I am not using the keywords before the four parameters I am passing to the table function. I am passing the following:

1. Starting folder. By entering '/', the root folder, my results will contain all of the folders in the IFS
2. Include subfolders. 'YES' means I want to include all of the subfolders
3. Object types to include and folders to exclude. '*ALLDIR' means I only want the folders (directories) returned. '*NOQSYS *NOQOPT' indicates that I do not want data from the QSYS environment (what could be call the IBM i library system) and from optical devices
4. Ignore errors. If any errors happen when this statement is executed ignore the error and continue processing

There is another temporary table definition after this line, therefore, line 3 has to end with a comma.

Line 4: As this is the definition for a second table I do not use the WITH. This temporary folder has two columns.

Lines 5 – 9: I am returning two columns from my SQL statement to the table T2. The first is the folder name from the temporary file T1, and the second, F2, comes from the lateral part of the statement. Here is where I join the temporary table T1 to the IFS_OBJECT_STATISTICS table function with a lateral join. I have defined the result of the table function as the column F2.

Why did I subtract 1 from the count?

The statement always returns a row for the folder itself. I just wanted to get a true count of the objects in the folder, and not include the folder in that count.

There is no comma at the end of line 9, as there are no more temporary tables to define.

Lines 10 and 11: This Select statement returns the results from the T2 table.

When I execute this CTE I get the following results, first ten only:

FOLDER COUNT ----------------------------------------------------- ----- /QIBM/ProdData/ARE/conf 0 /QIBM/ProdData/ARE/mri 0 /QIBM/ProdData/ARE/res 0 /QIBM/ProdData/Access/ACS/Base/Fonts 0 /QIBM/ProdData/Access/Web2/classes/com/ibm/as400/a... 0 /QIBM/ProdData/Access/Web2/classes/com/ibm/as400/a... 0

One thing I like about CTE is that I can "debug" it. If I change line 10 to:

10 SELECT * FROM T1

When I execute the CTE I get the results from the T1 table. This allows me to check that all the folders I am interested in have been inserted into T1.

FOLDER ----------------------------------------------------- / /QDLS /QIBM /QIBM/ProdData /QIBM/ProdData/ARE

What am I going to do with these results?

If this was a one off and I would want the results into an Excel compatible file I would include the CTE in a CREATE TABLE statement:

01 CREATE TABLE MYLIB.OUTFILE (FOLDER,COUNT) 02 AS ( 03 WITH T1(FOLDER) 04 AS (SELECT PATH_NAME 05 FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS('/', 'YES', '*ALLDIR *NOQSYS *NOQOPT', 'YES'))), 06 T2(FOLDER,COUNT) 07 AS (SELECT T1.FOLDER,F2 08 FROM T1, 09 LATERAL(SELECT (COUNT(*) - 1) 10 FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS(T1.FOLDER, 'YES', IGNORE_ERRORS => 'YES'))) B(F2) 11 WHERE F2 = 0) 12 SELECT * FROM T2 13 ORDER BY FOLDER) 14 WITH DATA ;

If I was going to need this information more than once I would build a SQL view that I could use instead of having to remember the CTE statement.

01 CREATE OR REPLACE VIEW MYLIB.EMPTY_IFS_FOLDERS 02 FOR SYSTEM NAME "EMPTYIFSFL" 03 (FOLDER) 04 AS ( 05 WITH T1(FOLDER) 06 AS (SELECT PATH_NAME 07 FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS('/', 'YES', '*ALLDIR *NOQSYS *NOQOPT', 'YES'))), 08 T2(FOLDER,COUNT) 09 AS (SELECT T1.FOLDER,F2 10 FROM T1, 11 LATERAL(SELECT (COUNT(*) - 1) 12 FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS(T1.FOLDER, 'YES', IGNORE_ERRORS => 'YES'))) B(F2) 13 WHERE F2 = 0) 14 SELECT FOLDER FROM T2) ;

Now whenever I need a list of all the empty IFS folders I can just use this select statement:

SELECT * FROM EMPTY_IFS_FOLDERS ORDER BY FOLDER

 

This article was written for IBM i 7.4, and should work for some earlier releases too.