Exploring the IFS with DB2 services

The rich “family” of DB2 for i services has recently seen the birth of a new category: IFS services. This group of services is available from IBM i 7.3 with the DB2 SF99703 Level 16 update (but it is better to consider only Level 17, since level 16 led to some problems… see here).

These new services return information about objects located in IFS folders; 4 services are available, and the most interesting is IFS_OBJECT_STATISTICS, a function returning a table with the list of files in a specified directory (or starting from the specified directory), with all related information.
This function greatly facilitates the retrieval of the contents of a folder and its subfolders, the same can also be obtained with the RTVDIRINF or PRTDIRINF commands or with the rather complex Qp0lGetAttr() (Get Attributes) API. However, these “standard” methods do not allow you to redirect the output to a file, but with SQL you can do that on the fly.
Here is an instruction for creating a table with the contents of a folder and its subfolders, and some information about individual files:

CREATE TABLE mduca1.ifsdir AS (
SELECT 
 CAST(path_name AS CHAR(100)) path, object_type, data_size, object_owner, 
 CAST(data_change_timestamp AS TIMESTAMP) last_chg
FROM TABLE (QSYS2.IFS_OBJECT_STATISTICS(
 START_PATH_NAME => '/home/mduca')) t
ORDER BY path_name
) WITH DATA

Here the contents of the table:

The IFS_OBJECT_STATISTICS function has four parameters:

• START_PATH_NAME: name of the starting directory
• SUBTREE_DIRECTORIES: if set to NO, subfolders are not analyzed. The default is YES
• OBJECT_TYPE_LIST: list of one or more object types to extract. You can also specify the standard object types (*PGM, *FILE, etc.) or the special values *ALLDIR, *ALLSTMF, *MBR, *NOQSYS, to filter only some objects
• OMIT_LIST: list of objects to be omitted from the list.

The function also works with normal system objects, starting from path /QSYS.LIB. In this case, however, the performance is much better with the specific SQL function OBJECT_STATISTICS.
The IFS function is particularly useful when we have to programmatically check the existence of a given file in an IFS folder (or starting from a root folder). Below, an example of an RPG program that receives two parameters as input: the path name and the file name, and returns a boolean flag indicating whether the specified file exists.

       //---------------------------------------------------------------
       //
       // Test IFS_OBJECT_STATISTICS table function
       //
       // Returns a boolean to inform the given file is found in the 
       //  given starting directory or in a subdirectory
       //
       //---------------------------------------------------------------
       ctl-opt decedit('0,') datedit(*dmy.)
       dftactgrp(*no) actgrp(*caller)
       option(*srcstmt : *nodebugio : *noexpdds)
       fixnbr(*zoned : *inputpacked);

       // *ENTRY
       dcl-pi FAQ40010A;
          dcl-parm pPath   char(100);
          dcl-parm pFile   char(80);
          dcl-parm pFound  ind;
       end-pi;
       // variabili
       dcl-s found         ind ;
       dcl-s fullPath      varchar(180) ;
       //--------------------------------------------------------------
       Exec SQL
       SET OPTION   COMMIT=*none,
                    CLOSQLCSR=*endmod,
                    ALWCPYDTA=*yes
       ;

       fullPath = %trim (pPath) + '/' + %trim(pFile) ;

       Exec SQL
       SET :found = (SELECT DISTINCT '1'
       FROM TABLE (qsys2.ifs_object_statistics(
        START_PATH_NAME => :fullPath )) t
       )
       ;
       if SQLCODE <> 0;
          found = *off;
       endif;

       *inLR = *on;
       return;

The program performs a case insensitive search.
Let’s see a variant of the first program; it receives the initial directory, the file name and returns the full path of where the file was found (including subdirectories). Through the UPPER function this search is also case-insensitive.

       //---------------------------------------------------------------
       //
       // Test IFS_OBJECT_STATISTICS table function
       //
       // Returns full path if file found in starting directory or in
       //  a subdirectory
       //---------------------------------------------------------------
       ctl-opt decedit('0,') datedit(*dmy.)
       dftactgrp(*no) actgrp(*caller)
       option(*srcstmt : *nodebugio : *noexpdds)
       fixnbr(*zoned : *inputpacked);

       // *ENTRY
       dcl-pi FAQ40010B;
          dcl-parm pPath        char(100);
          dcl-parm pFile        char(80);
          dcl-parm pFullPath    char(180);
       end-pi;
       // variabili

       //--------------------------------------------------------------
       Exec SQL
       SET OPTION   COMMIT=*none,
                    CLOSQLCSR=*endmod,
                    ALWCPYDTA=*yes
       ;

       pFullPath = *blank ;
       Exec SQL
       SET :pFullPath = (SELECT
       COALESCE(MIN(path_name), '**NOT FOUND**')
       FROM TABLE (qsys2.ifs_object_statistics(
        START_PATH_NAME => TRIM(:pPath) )) t
       WHERE LOCATE(TRIM(UPPER(:pFile)), UPPER(path_name)) <> 0
       )
       ;

       *inLR = *on;
       return;

This program returns only the first directory where the file is found, if needed you can declare a cursor and return an array with all the paths where the file is found, including subdirectories.

Warning: running the IFS functions in a job under the “generic” CCSID 65535, an SQL0332 error may occur since some columns of the returned table are coded with CCSID 1200 and no conversion table exists with 65535. To fix the problem, before running the statements simply set the job’s CCSID with a specific value (eg 280 for Italian systems or 37 for US, Canada and others).

DOCUMENTATION:
Servizi IFS : https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzajq/rzajqservicesifs.htm
IFS_OBJECT_STATISTICS: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzajq/rzajqudfifsobjstat.htm
OBJECT_STATISTICS: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzajq/rzajqudfobjectstat.htm