Retrieve Objects present in IFS Directory from SQL - IBM i

Objects in IFS Directory

Working with IFS (Integrated File System) is essential and almost part of day to day work for most of the IBM i developers.  

In my experience below are the couple tasks I had to do frequently. 

• Check if an object is present in IFS directory. 
• Retrieve list of objects present in IFS directory. 

This has been made easier to do these now (and much more) from SQL directly with 'IFS_OBJECT_STATISTICS' table function. 

What does IFS_OBJECT_STATISTICS table function return? It returns the attributes of an IFS Object for every object (including directory) present in the IFS Path provided. 

Let's now have a look at how to retrieve the objects present for a specified path. 

In the above query,

• Line - 1: Column 'PATH_NAME' returns the IFS Path (or Object) the row is returned for. 
• Line - 2: Column 'OBJECT_TYPE' returns the type of object retrieved. 
• Line - 3: Column 'CREATE_TIMESTAMP' returns the timestamp when the object was created. 
• Line - 5: 'IFS_OBJECT_STATISTICS' is the table function that retrieves the data. 
• Line - 6: Parameter 'START_PATH_NAME' is to pass the IFS Directory or Object Name. 

Looking at the above result, 

• PATH_NAME contains,
• Path passed in the parameter 'home/REDDYP'.
• All Stream files under directory 'home/REDDYP' (accessibmi.py, Sample1.csv etc).
• Sub Directories under the path passed (/home/REDDYP/SubDirectory).
• Files present in the Subdirectory (/home/REDDYP/SubDirectory/Sample_Textfile.txt). 
• OBJECT_TYPE contains the type of the Object returned i.e., *DIR (for directory), *STMF (stream file) etc. 
• CREATE_TIMESTAMP contains the timestamp the Object or Directory is created on.
• There are many other columns IFS_OBJECT_STATISTICS would return. You can explore the full list of columns by running the query with '*' instead of column names. 

The above query returned sub directories and objects inside sub directory. It is helpful to know all the objects present in the sub directories as well if there aren't many sub directories and objects in sub directories. 

With the use of 'SUBTREE_DIRECTORIES' parameter, we can control if we need to display the objects of sub directory or not. This would become helpful if there are many objects and sub directories. 

In the above query, Additional parameter SUBTREE_DIRECTORIES accepts two parameters.

• YES (Default) - This would retrieve all the objects inside Sub Directory.
• NO - This would not retrieve the objects inside Sub Directory (Would list any Sub Directory under the path specified).

There is another important parameter I would like to mention is 'OBJECT_TYPE_LIST'. This parameter can be used to verify or retrieve any the IFS Objects of specific type. 

In the above example, We can see there are two different object types *DIR and *STMF. Let's say If I'm only interested in *STMF and not the directories. then this parameter is very much helpful. 

Above query would only return objects of type '*STMF'. So, what if we need to select from multiple types? All the required object types can be specified by separating with space.

OBJECT_TYPE_LIST => '*STMF *DIR'

This parameter accepts some special values to indicate group of object types along with specific object types. 

• *ALLDIR - All directory types (*LIB, *DIR, *FLR, *FILE and *DDIR)
• *ALLSTMF - All stream file types (*MBR, *DOC, *STMF, *DSTMF and *USRSPC)
• *MBR - All database file member types.
• *NOQDLS - All object types except QDLS object types.
• *NOQOPT - All object types except QOPT and QNTC object types.
• *NOQSYS - All object types except QSYS.LIB object types.

There are couple of more parameters and below is the brief about these parameters.

OMIT_LIST

List of path names to exclude from processing. All objects and sub directories under this path would be excluded as well. Multiple path names can be specified by separating with blanks. There are couple of exceptions to this.

• If there are spaces exist in the path name then path name to be enclosed in apostrophes or quotes. 

    OMIT_LIST => '/home/REDDYP/Sub Directory'

• If there is an apostrophes or quotes in the path name, the embedded character needs to be doubled. 

       OMIT_LIST => '"/home/REDDYP/Memeber''s Data" "/home/REDDYP/Sub Directory"'

       In the above example,

• First path '/home/REDDYP/Memeber's Data' has apostrophe. So, this needs to be doubled (Member''s Data)
• Both the paths are enclosed in quotes. Using the quotes is the best practice when dealing with multiple paths, it is easy to understand and maintain.

IGNORE_ERRORS

This is more of a error handling, Advising the system on what to do in case of an error while executing the query. 

• NO - An error is returned.
• YES (default) - A warning is returned and no data is returned when error is occurred. 

Query looks like below if we use all the parameters.

SELECT * FROM TABLE(IFS_OBJECT_STATISTICS(

                    START_PATH_NAME => 'Start Path Name'

                    SUBTREE_DIRECTORIES => 'YES/NO',

                    OBJECT_TYPE_LIST => 'List of Object Types',

                    OMIT_LIST => 'Paths to be Omitted',

                    IGNORE_ERRORS => 'YES/NO'))  

There is one thing I haven't mentioned is "How to check if an object is present in IFS directory?" Well, this isn't the actual purpose of the function. But, we can achieve this with a query like the below.

This query returns '1' if the object (/home/REDDYP/hello.py) is present and returns '0' if not present. 

If you have any Suggestions or Feedback, Please leave a comment below or use Contact Form.