• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Use SQL To Read IFS Directories

    August 27, 2014 Ted Holt

    Note: The code accompanying this article is available for download here.

    The Integrated File System (IFS) is marvelous, and without it this system I love so well would be history. Nevertheless, managing the files in the IFS is challenging. Recently I found myself wishing that I could use SQL to query an IFS directory. Since IBM had not provided me with that interface, I decided to build it myself. Here you are.

    I didn’t start from scratch. Thanks to Bob Cozzi and Scott Klement, whose code I’ve appropriated over the years, I’ve written programs that access the IFS. Throwing together an SQL function from code I already had wasn’t hard.

    If this is something that interests you, the first thing you need to do is get the source code. You’ll need my RTVDIR RPG source member, which you can download at the code link given above. You’ll also need Scott Klement’s IFSIO_H copybook, which you can get at Scott’s website.

    Once you’ve loaded the source code on your system, create a module and service program from the RPG source.

    CRTRPGMOD MODULE(xxx/RTVDIR) SRCFILE(xxx/SRC) SRCMBR(RTVDIR)
    CRTSRVPGM SRVPGM(xxx/RTVDIR) MODULE(RTVDIR) EXPORT(*ALL)
    

    Replace xxx with the name of the library of your choice.

    Then create the RTVDIR SQL function.

    create function xxx/RtvDir (Dir varchar(256))
       returns table (
         File    varchar(256),
         Size    integer,
         AccessTime timestamp,
         ModifyTime timestamp,
         StatusTime timestamp,
         NbrLinks   integer,
         CCSID      integer,
         CodePage   integer,
         ObjType    char(11) )
    external name 'xxx/RTVDIR(RTVDIR)'
    language rpgle
    parameter style db2sql
    no sql
    not deterministic
    disallow parallel
    

    Now you can do things like this:

    select ccsid, size, modifytime, file
      from table(rtvdir('/tmp')) as t
     where nbrlinks > 1
     order by 3 desc
    

    And get results like this:

    CCSID     SIZE  MODIFYTIME                  FILE
    =====  =======  ==========================  =========================
       37   32,768  2014-08-27-16.15.01.000000  br549
       37  139,264  2014-08-27-13.52.17.000000  .
      819    8,192  2014-08-27-13.52.17.000000  .chuck
       37  233,472  2014-08-27-13.44.16.000000  ..
      819    8,192  2014-08-26-19.21.30.000000  maria-gadu.txt
      819    8,192  2014-08-26-19.21.18.000000  laurent-voulzy.b
       37    8,192  2014-08-18-10.54.25.000000  cluck.cluck.cluck
      819    8,192  2014-08-16-19.37.28.000000  brevard-zoo.csv
     1251   58,728  2014-08-14-08.36.22.000000  wiggins.f.department.jpg
      819   45,146  2014-08-11-19.12.49.000000  eleftheria.arvanitaki.jpg
      819   95,192  2014-08-09-19.33.48.000000  san_luis_rey.pdf
      819   12,224  2014-08-04-19.43.55.000000  n-v-peale.txt
    

    The RTVDIR function doesn’t replace other interfaces. I still use IBM Navigator for i and other interfaces that have been mentioned earlier in this august publication. (See the related stories below.) But it’s been a welcome addition to my tool kit.

    Have fun. Let me know if you find problems with it or have suggestions for improvement.

     

    RELATED STORIES

    The Integrated File System for Intelligent People

    EDTF Sizes Up OS/400 IFS Directories

    IFSPOP–Another (and a Better) IFS Interface

     



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Midrange Dynamics North America

    Git up to speed with MDChange!

    Git can be lightning-fast when dealing with just a few hundred items in a repository. But when dealing with tens of thousands of items, transaction wait times can take minutes.

    MDChange offers an elegant solution that enables you to work efficiently any size Git repository while making your Git experience seamless and highly responsive.

    Learn more.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Maxava:  Don't wait for a disaster. Start planning today. DR Strategy Guide for IBM i FREE eBook. System i Developer:  Upgrade your skills at the RPG & DB2 Summit in Minneapolis, Sept 30 - Oct 2. COMMON:  Join us at the COMMON 2014 Fall Conference & Expo in Indianapolis, Oct 27-29

    More IT Jungle Resources:

    System i PTF Guide: Weekly PTF Updates
    IBM i Events Calendar: National Conferences, Local Events, and Webinars
    Breaking News: News Hot Off The Press
    TPM @ EnterpriseTech: High Performance Computing Industry News From ITJ EIC Timothy Prickett Morgan

    IT Evolution Chicago Style Plotting Out A Power Systems Resurgence

    2 thoughts on “Use SQL To Read IFS Directories”

    • Giovanni Formentini says:
      September 11, 2019 at 7:42 am

      This is absolutely priceless. Thank you.

      Reply
    • Ted Holt says:
      June 22, 2020 at 4:28 pm

      My function did its job, but now there’s something better. I recommend using the IFS_OBJECT_STATISTICS table function instead.

      https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzajq/rzajqudfifsobjstat.htm

      Reply

    Leave a Reply Cancel reply

Volume 14, Number 19 -- August 27, 2014
THIS ISSUE SPONSORED BY:

Bug Busters Software Engineering
WorksRight Software
QUERY FILE for IBM i

Table of Contents

  • Creating A GUID In DB2 For i
  • Use SQL To Read IFS Directories
  • Automatically Detecting And Re-Enabling Disabled NetServer Profiles

Content archive

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

Recent Posts

  • Public Preview For Watson Code Assistant for i Available Soon
  • COMMON Youth Movement Continues at POWERUp 2025
  • IBM Preserves Memory Investments Across Power10 And Power11
  • Eradani Uses AI For New EDI And API Service
  • Picking Apart IBM’s $150 Billion In US Manufacturing And R&D
  • FAX/400 And CICS For i Are Dead. What Will IBM Kill Next?
  • Fresche Overhauls X-Analysis With Web UI, AI Smarts
  • Is It Time To Add The Rust Programming Language To IBM i?
  • Is IBM Going To Raise Prices On Power10 Expert Care?
  • IBM i PTF Guide, Volume 27, Number 20

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