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.