Create/Write Data to 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. 

There are various ways to create stream files like CPYFRMIMPF, CPYFRMSTMF, EDTF...

Creating the IFS file and writing the data has been made much easier with SQL procedure IFS_WRITE (IFS_WRITE_BINARY for writing the data in Binary format and IFS_WRITE_UTF8 for writing the data in UTF8 format). 

In this post, we will see how to write the data into Stream file directly from SQL using IFS_WRITE procedure. 

E.g.: 

To start with, What we need is 'Path Name (IFS Directory & File Name)' and 'Data to be written'. 

• First parameter 'PATH_NAME' accepts the IFS path name along with the file name.
• In this example, '/home/REDDYP' is the directory and 'NewTextFile.txt' is the file name. 
• Directory should be present by the time the procedure is called. If not, Procedure call would be failed. 
• File name doesn't necessarily need to be present. If the is not present, File would be created. If already present, Data will appended to the file by default.
• Second parameter 'LINE' accepts the data that is to be written into the file. 

In this example, file 'NewTextFile.txt' isn't already present. So, the file is created once the procedure is run. 

This looks fine when we look at the file from 5250 session. But, if we download the file and open in the Text editor, it appears like the below.

The reason behind this is CCSID used. By default IFS_WRITE creates the file with Job's default CCSID. If we need the file to be created with specific CCSID, we need to pass this using 'FILE_CCSID' parameter. 

• Parameter 'FILE_CCSID' accepts an integer value (CCSID) and will be used to create the stream file.
• This is only used when creating the new stream file and has no effect if the file is already present. 
• Default value for this parameter is '0' for IFS_WRITE. File will be created with Job's CCSID if '0' is specified (or default).
• Default value for this parameter is '1208' for IFS_WRITE_UTF8 and '0' for IFS_WRITE_BINARY.

This looks fine from 5250 session, let's see how this would look like if we download the file. 

So, If we need to create the file with the data (and replace if the file already exist), we can make use of the parameter 'OVERWRITE'. 

• Parameter 'OVERWRITE' determines how the data passed (in LINE parameter) to be added to the file. Below are the valid values for this parameter.
• APPEND - This is the default value for this parameter. Data is appended if the file already exist and File would be created if not already exist.
• REPLACE - Replace the data in the existing stream file with the data passed. If file isn't already exist, file would be created. 
• NONE - This is to be used if the new file needs to be created always. Procedure will fail if the file already present and new file would be created if the file is not present.

Passing 'APPEND' parameter or not passing this parameter would append the data to the file.

In the above example, New line has been written with the data passed. If we need to append the data on to the same line rather than creating new line, we can make use of 'END_OF_LINE' parameter. However, this parameter needs to be used for the line which needs data is added on to (rather than in the query that needs to be added). 

E.g.: 

If the data needs to be appended to Third Record, END_OF_LINE parameter needs to be passed with 'NONE' while writing this record. So that following line can be appended to the same record. 

• Parameter 'END_OF_LINE' determines what needs to be written at the end of the line. Below are the valid values for this parameter.
• CRLF - This is the default value for IFS_WRITE (and IFS_WRITE_UTF8). Carriage return and Line feed are appended to the end of the line.
• CR - Carriage return is appended to the end of the line.
• LFCR - Line feed and Carriage return are appended to the end of the line. 
• LF - Line feed is appended to the end of the line.
• NONE - No end of the line characters are appended i.e., Data appended will be added to the same record. This is the default value for IFS_WRITE_BINARY.

When the IFS_WRITE is called next time data is appended to the same line. 

Below is the Syntax of this procedure with all the parameters. 

CALL QSYS2/IFS_WRITE

     (PATH_NAME => 'IFS Path with directory and File Name', 

      LINE => 'Data to be written',

      FILE_CCSID => CCSID(Integer),

      OVERWRITE => 'Overwrite Option',

      END_OF_LINE => 'End of Line Option') 

All the above examples showing the data line by line into the file. Let's see how we can write the data from a file into the Stream file using IFS_WRITE. This would a bit of coding in SQL. 

In the above query, we are calling IFS_WRITE twice. Once to create the empty file (or replace with blank if file is already present) and Second time, it is called inside FOR loop to write each line from Table. Let's see each of the parameter in detail.

• Line - 4: Parameter 'LINE' with no data is to create the blank file. Next time IFS_WRITE is called is in the loop, so it is better to create the blank file first and append the data. 
• Line - 5: Parameter 'OVERWRITE' with 'REPLACE', This is to replace any data in the current file if the file is already present. 
• Line - 6: Parameter 'END_OF_LINE' with 'NONE', This is to append the data from the first line.
• Line - 9: FOR loop with SELECT query of table 'REDDYP1/IFSFILE'.
• Line - 10: IFS_WRITE with data to be written. This procedure is called for each row in the table and the data is added to the new row (with default value on OVERWRITE).

IFS_WRITE_BINARY and IFS_WRITE_UTF8 works in the same way only difference is data to be passed in Binary and UTF8 formats respectively. 

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