I believe that all of us who work in the IBM i environment have happened, and continue to do, to have a multitude of obsolete files in the IFS and waste time locating and delet

ing them. One of the new features IBM added with the PTF 7.4 TR1 and 7.3 TR7 allows you to get a list of the contents of an IFS folder simply via SQL.

I discovered this by looking for a solution to clean an IFS folder with over 290,000 files and, searching through the various BLOGs, I found this article by Simon Hutchinson https://www.rpgpgm.com/2019/11/using-sql-to- list-directories-and-files.html in addition to that of our Blogger Massimo Duca (“Let’s explore IFS with DB2 services) in this blog and I decided to try to create a program to better manage it.

Having solved some small logic problems I was able to try it and I obtained an excellent result by reducing the contents of the folder from over 290,000 files to just over 4,000 and, scheduling the program, I know that the files will remain in the folder for 5 days and then they will come S

eeing the result I decided to share this program in the BLOG and I am attaching i

t to you.Obviously, and it couldn’t be otherwise, the program is written in TOTAL FREE which I hope will be used by an increasing number of users

** free
ctl-opt datfmt (* eur) datedit (* dmy) debug (* yes) copyright ('FAQ400.COM') decedit ('0,');    
// ------------------------------------------------ ------------------------------------------------- *
// Definition of convenience fields *
// ------------------------------------------------ ------------------------------------------------- *
dcl-s date date;
dcl-s command char (500);
dcl-s json_name char (500);
dcl-s messagetext char (1024);
// ------------------------------------------------ ------------------------------------------------- *
// Main program *
// ------------------------------------------------ ------------------------------------------------- *
// Set deadline for JSON search
date =% date () -% days (5); // Setting the date field to today -5 days

// SQL preparation for reading IFS content
exec sql // Declaration of the cursor for reading the IFS
  declare jsn cursor for
    select CAST (PATH_NAME AS CHAR (500)) // The PATH_NAME field is a 16M blob so I read it
      from table (QSYS2.IFS_OBJECT_STATISTICS ('/ myfolder / json', 'YES')) // using the CAST function to avoid SQL errors
      where create_timestamp <=: data and // The * YES option allows reading of subfolders
            object_type = '* STMF'; // This condition makes me read only * STMF files

exec sql
  open jsn;

dow 1 = 1;

  exec sql
    fetch jsn
      into: json_name;

  if sqlcod <> 0;

    if sqlcod <> 100; // This function is very useful if you receive
      exec sql // a sqlcode <> from 100 to get in the messagetext field
        Get Diagnostics Condition 1 // a more detailed indication of the error received
          : messageText = MESSAGE_TEXT;


  command = 'RMVLNK OBJLNK (' '' +% trim (json_name) + '' ')'; // I compose the command to remove the affected object

  exec sql
    call qsys2.qcmdexc (: command); // I execute, via SQL, the command prepared previously
                                                                                // If you have an outdated 7.1 version it might be
endDo; // need to enter the length of the command to execute

exec sql
  close jsn;

exsr exit;
// ------------------------------------------------ ------------------------------------------------- *
// End of job routine *
// ------------------------------------------------ ------------------------------------------------- *
begsr quit;

  * inlr = * on;


I hope you find the example shown useful.

About author

IBMi System Developper | Blogger and Software Specialist on IBMi | Nextev srl | Faq400

Leave a Reply

%d bloggers like this: