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.
I’ll show the entire SQL statement (Figure 3), then I’ll break it down and explain the code.
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).
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.