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.