Skip to main content

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. 

Retrieve Objects present in IFS directory

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. 

Retrieve Objects present in IFS directory

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. 

Retrieve Objects present in IFS Directory

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).
Retrieve Objects present in IFS Directory

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. 

Retrieve Objects present in IFS Directory

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.

Check if an object is present in IFS directory

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.
Labels:

Comments

Post a Comment

Popular posts from this blog

All about READ in RPGLE & Why we use it with SETLL/SETGT?

READ READ is one of the most used Opcodes in RPGLE. As the name suggests main purpose of this Opcode is to read a record from Database file. What are the different READ Opcodes? To list, Below are the five Opcodes.  READ - Read a Record READC - Read Next Changed Record READE - Read Equal Key Record READP - Read Prior Record READPE - Read Prior Equal Record We will see more about each of these later in this article. Before that, We will see a bit about SETLL/SETGT .  SETLL (Set Lower Limit) SETLL accepts Key Fields or Relative Record Number (RRN) as Search Arguments and positions the file at the Corresponding Record (or Next Record if exact match isn't found).  SETGT (Set Greater Than) SETGT accepts Key Fields or Relative Record Number (RRN) as Search Arguments and positions the file at the Next Record (Greater Than the Key value). Syntax: SETLL SEARCH-ARGUMENTS/KEYFIELDS FILENAME SETGT  SEARCH-ARGUMENTS/KEYFIELDS FILENAME One of the below can be passed as Search Arguments. Key Fiel
4 comments
Read more

What we need to know about CHAIN (RPGLE) & How is it different from READ?

CHAIN READ & CHAIN, These are one of the most used (& useful) Opcodes by any RPG developer. These Opcodes are used to read a record from file. So, What's the difference between CHAIN & READ?   CHAIN operation retrieves a record based on the Key specified. It's more like Retrieving Random record from a Database file based on the Key fields.  READ operation reads the record currently pointed to from a Database file. There are multiple Opcodes that start with READ and all are used to read a record but with slight difference. We will see more about different Opcodes and How they are different from each other (and CHAIN) in another article. Few differences to note.  CHAIN requires Key fields to read a record where as READ would read the record currently pointed to (SETLL or SETGT are used to point a Record).  If there are multiple records with the same Key data, CHAIN would return the same record every time. READE can be used to read all the records with the specified Ke
Post a Comment
Read more

Extract a portion of a Date/Time/Timestamp in RPGLE - IBM i

%SUBDT Extracting Year, Month, Day, Hour, Minutes, Seconds or Milli seconds of a given Date/Time/Timestamp is required most of the times.  This can be extracted easily by using %SUBDT. BIF name looks more similar to %SUBST which is used to extract a portion of string by passing from and two positions of the original string. Instead, We would need to pass a value (i.e., Date, Time or Timestamp ) and Unit (i.e., *YEARS, *MONTHS, *DAYS, *HOURS, *MINUTES, *SECONDS or *MSECONDS) to %SUBDT.  Valid unit should be passed for the type of the value passed. Below are the valid values for each type. Date - *DAYS, *MONTHS, *YEARS Time - *HOURS, *MINUTES, *SECONDS Timestamp - *DAYS, *MONTHS, *YEARS, *HOURS, *MINUTES, *SECONDS, *MSECONDS Syntax: %SUBDT(value : unit { : digits { : decpos} }) Value and Unit are the mandatory arguments.  Digits and Decimal positions are optional and can only be used with *SECONDS for Timestamp. We can either pass the full form for the unit or use the short form. Below i
2 comments
Read more