Read data in IFS file from SQL - IBM i

IFS Stream File

IFS (Integrated File System) Stream files are major part of any (or most of) IBM i applications. Having to read data from the Stream files become necessary. 

We can read the IFS file from Programs in two different ways. 

• By copying the data from IFS file to Database file (CPYFRMSTMF or CPYFRMIMPF).
• By using 'open' & 'read' procedures written in 'C' language. Click Here to see more about how to read IFS file using these procedures from RPGLE.

One other way to do this is by using SQL table function 'IFS_READ'. There are couple of more table functions 'IFS_READ_BINARY' and 'IFS_READ_UTF8' to read the data and return in Binary and UTF8 formats accordingly. 

In this post, we will see how to read IFS file using 'IFS_READ' and little about the other two table functions towards the end.

E.g.: 

Above query is simple and straight forward, All we need to do is pass the required file name (along with full path) against parameter 'PATH_NAME'.

This table function has got only two columns. 

• 'LINE_NUMBER' - Indicates the position of the line in file.
• 'LINE' - Actual data in the file.

Let's say if there is a limit on the number of characters to receive, We can control this using MAXIMUM_LINE_LENGTH parameter. 

• Parameter 'MAXIMUM_LINE_LENGTH' accepts numeric values.

If we look at the above result, each row contains only 12 digits and any additional characters has been returned as new line. 

Line number 1, 4 & 7 has length of 12 digits and has returned only one row. Line numbers 2 & 6 are exceeding 12 digits, so only 12 digits are returned and the rest are returned as new lines (3 & 6).

There is another important parameter 'END_OF_LINE' this is used to determine the end of line in Stream file. Let's have a look at how it makes difference before we see the list of valid values. 

In the above query, 'NONE' is passed as 'END_OF_LINE', Query will not consider any End of Line characters. 

All the records has been returned in a single row. Number of digits allowed in a single line can be controlled using 'MAXIMUM_LINE_LENGTH'. If nothing is specified, by default 2GB is returned in a single line and the next is returned in a new line. 

Below are the possible options for END_OF_LINE parameter.

• CRLF - A Carriage return and Line feed indicate End of line.
• LFCR - A Line feed and Carriage return indicate End of line.
• CR - A Carriage return indicate End of line.
• LF - A Line feed indicate End of line.
• ANY - Any of the above four indicate End of line (Default for IFS_READ & IFS_READ_UTF8).
• NONE - No End of line characters are considered (Default and only allowed value for IFS_READ_BINARY). 

Let's see a simple example of how to get the data in Binary format using IFS_READ_BINARY. 

Parameters are same as IFS_READ except 'END_OF_LINE' which always accepts 'NONE'. This implies that IFS_READ_BINARY always returns a single row (MAXIMUM_LINE_LENGTH restrictions apply).

In the same way, data can be retrieved in UTF8 format using IFS_READ_UTF8. 

Below is the syntax of IFS_READ with all the parameters.

SELECT * FROM

         TABLE(QSYS2/IFS_READ(

               PATH_NAME => 'ifs_path_name',

               MAXIMUM_LINE_LENGTH => maximum_length(in decimal),

               END_OF_LINE => 'end_of_line_characters'))

In all of the examples shared above returns a valid data. So, What happens if an incorrect path is passed? No data is received and query is completed with SQL Code '100'. 

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