Last Updated on 23 February 2020 by Roberto De Pedrini
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 DATAHere 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
Introduction Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful Read more
DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more
Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more
Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more
