Search for a string in IFS using SQL - IBM i

Search for a string in IFS

IFS (Integrated File System) Stream files are major part of any (or most of) IBM i applications. 

Sometimes it becomes essential to scan through the IFS directory to identify the stream files with particular string. 

We can do this from SQL by writing a simple function using functions* IFS_OBJECT_STATISTICS (to retrieve the stream files present in directory) and IFS_READ (to read through the stream file and query for a specific string). Click on each of these functions to know more. 

Let's have a look at how new function (SCAN_IFS_FOR_STRING) returns the search results. 

Function 'SCAN_IFS_FOR_STRING' accepts two parameters.

1. Input IFS Directory - IFS Directory that is to be scanned. Please note that all the Sub directories would be scanned as well.
2. Scan String - String that is to be scanned for.

This query should return the stream files that contains the scan string in the specified Input directory. 

This function returns three columns.

• STMF_NAME - Stream file name (with full path) 
• LINE_NUMBER - Line number with in stream file where the string is found. 
• LINE - Line from the stream file where the the string is found. 

Below is the source for the function 'SCAN_IFS_FOR_STRING', same can be found in the Git Hub Repository. 

Below is the brief description on what the above function would do.

• Lines 5 - 7: Create (or Replace) function with two Input parameters (Input IFS directory & Scan string).
• Lines 9 - 11: Format of the data to be returned. Scan results would be returned in three columns (Stream file name, Line number and Line).
• Lines 19-22: Create (or Replace) temporary table with the three columns to be returned and to clear the data from the temporary table if the table already exist.
• Lines 27-30: Retrieve the list of stream files present in the Input directory using 'IFS_OBJECT_STATISTICS' by passing Input directory and Object type as '*ALLSTMF' and to repeat the loop for all the files present.
• Lines 34-38: Read stream files using function IFS_READ and select the records with only matching records by specifying where condition on LINE. Using UPPER() for both LINE and Scan string would convert the string to upper case for comparison and returns the matching records.
• Line 43: Return the contents of temporary table.

*SQL Functions IFS_OBJECT_STATISTICS and IFS_READ are only supported since IBM i 7.3 (TR9) and 7.4 (TR3).

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