Guru: Comparing IFS Directories Using SQL
June 19, 2023 Mike Larsen
I use SQL almost every day at my job. It may be just a query in ACS, or it may be embedded SQL in an RPG program. A few days ago, I needed to compare the contents of one IFS directory to another. Combining a few different table functions, I was able to develop a nice solution.
Note: The code for this article can be downloaded here.
In this example, I have a directory that has five text files in it. I have a second directory that has three text files in it, and they are the same documents that are in the first directory. My goal is to identify the two documents that are in the first directory, but not in the second directory. Figures 1 and 2 show both directories to give you a visual.
Figure 1. Directory one
Figure 2. Directory two
I’ll show the entire SQL statement (Figure 3), then I’ll break it down and explain the code.
Figure 3. SQL statement
In lines 1 – 9, I’m using the table function, Ifs_object_statistics, to gather information from the first directory. I’m also using the Systools.split table function in lines 3 – 5. Why am I doing that? Ifs_object_statistics returns the full path to the item in the directory, and I just want to compare the name of the item. By using the Systools.split table function, I can extract the name of the document, in this case. I’m splitting the path name at the slashes and I’m using the fourth ordinal position as that’s where the name of the document is.
At the end of line 9, I’m doing an exception join to the second directory, again using the table function Ifs_object_statistics to obtain information about that directory. I’m using the exception join type as I want to see what documents are in the first directory that are not in the second directory.
Here’s where it gets interesting. In lines 13 – 15 and 17 – 19, I’m using the Systools.split table function to join the two tables together by the document name. That allows me to see which documents are in the first directory and are not in the second directory.
Finally, in line 21, I’m using criteria to only select files that were created on or after June 10, 2023.
When I run this statement, I see that testDocument2.txt and testDocument4.txt are not in the second directory (Figure 4).
Figure 4. Result of comparison
This short, but powerful, SQL statement provided me with the results for which I was looking and helped me in a real production situation. Perhaps it can help you, too.
Mike Larsen is a director of information technology at Auburn Pharmaceutical and has been working with IBM i systems for over 20 years. He specializes in RPG, CL, and SQL and recently has been working with PHP and Python. Current projects have given Mike the opportunity to work with generating and parsing XML and JSON from SQL and consuming SOAP and REST web services. Although his main area of expertise is on IBM i, Mike has a passion for learning other languages and how he can integrate other platforms with IBM i.
RELATED STORIES
Guru: String Manipulation Using SQL
Guru: Regular Expressions, Part 1
Guru: Regular Expressions, Part 2
Guru: Debugging SQL Stored Procedures With ACS
I don’t know about performance but I much prefer to use CTEs for readability. I find the code above hard to read. I would have the following:
with … as folder1,
… as folder2, select * from folder 1 exception join folder2.
I can Cut and Paste the code from an image.
do you have a link to the code?
There should have been code included. One sec.
It’s fixed now. Sorry about that.
What are the potential performance implications of using table functions like Ifs_object_statistics and Systools.split in the SQL statement described in the article, particularly when dealing with large directories or a significant number of files?
Thanks Timothy!
Is there a way to compare folders on different LPAR’s ?