• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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

    Guru: Creating PDF Documents With Python

    Guru: Creating Excel Spreadsheets With Python

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400guru, ACS, Four Hundred Guru, IBM i, IFS, RPG, SQL

    Sponsored by
    VISUAL LANSA 16 WEBINAR

    Trying to balance stability and agility in your IBM i environment?

    Join this webinar and explore Visual LANSA 16 – our enhanced professional low-code platform designed to help organizations running on IBM i evolve seamlessly for what’s next.

    🎙️VISUAL LANSA 16 WEBINAR

    Break Monolithic IBM i Applications and Unlock New Value

    Explore modernization without rewriting. Decouple monolithic applications and extend their value through integration with modern services, web frameworks, and cloud technologies.

    🗓️ July 10, 2025

    ⏰ 9 AM – 10 AM CDT (4 PM to 5 PM CEST)

    See the webinar schedule in your time zone

    Register to join the webinar now

    What to Expect

    • Get to know Visual LANSA 16, its core features, latest enhancements, and use cases
    • Understand how you can transition to a MACH-aligned architecture to enable faster innovation
    • Discover native REST APIs, WebView2 support, cloud-ready Azure licensing, and more to help transform and scale your IBM i applications

    Read more about V16 here.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    As I See It: Entitlement Master Class How To Celebrate IBM i Turning 35 This Week

    6 thoughts on “Guru: Comparing IFS Directories Using SQL”

    • Glenn Gundermann says:
      June 19, 2023 at 9:23 am

      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.

      Reply
    • Steven Easton says:
      June 19, 2023 at 12:29 pm

      I can Cut and Paste the code from an image.
      do you have a link to the code?

      Reply
      • Timothy Prickett Morgan says:
        June 19, 2023 at 5:17 pm

        There should have been code included. One sec.

        Reply
      • Timothy Prickett Morgan says:
        June 19, 2023 at 7:17 pm

        It’s fixed now. Sorry about that.

        Reply
    • luqmaan s says:
      June 20, 2023 at 3:35 am

      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?

      Reply
    • Les Turner says:
      June 20, 2023 at 7:29 am

      Thanks Timothy!
      Is there a way to compare folders on different LPAR’s ?

      Reply

    Leave a Reply Cancel reply

TFH Volume: 33 Issue: 36

This Issue Sponsored By

  • Focal Point Solutions Group
  • Cozzi Research
  • Racksquared
  • DRV Technologies
  • WorksRight Software

Table of Contents

  • Happy Coral Anniversary, System/36 And System/38!
  • How To Celebrate IBM i Turning 35 This Week
  • Guru: Comparing IFS Directories Using SQL
  • As I See It: Entitlement Master Class
  • IBM i PTF Guide, Volume 25, Number 25

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26
  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle