SQLRPGLE example writing to IFS with SQL

QSYS2.IFS_WRITE is a system-provided function in IBM i that allows you to write data to the Integrated File System (IFS). The IFS is the file system on IBM i that provides a UNIX-like file structure and allows access to files and directories.

The QSYS2.IFS_WRITE function can be used to write data to a file in the IFS. It takes the following parameters:

  • Path Name - A character or graphic string that defines the path name for the file to be written. If an absolute path name is not specified, the current working directory is used in combination with the relative path name to resolve to the object. If the path name identifies an existing object, the object must be a stream file. Otherwise, a stream file will be created. When a stream file is created, the default authority for the parent directory is used.
  • Line - A character or graphic string containing the data to be written to the stream file at the specified path name. It can be up to 2 gigabytes long. For IFS_WRITE, the line is a character string in the job CCSID. If the stream file is not in the job CCSID, the line will be converted to the stream file's CCSID as it is written. For IFS_WRITE_BINARY, the line is a binary string. The data will not be converted when writing to the stream file. For IFS_WRITE_UTF8, the line is a UTF-8 string. If the stream file is not UTF-8, the line will be converted to the stream file's CCSID as it is written.
  • File CCSID - An integer value that specifies the CCSID to be used when creating a new stream file. This parameter is ignored when appending to an existing file. If this parameter is not specified, the default is 1208 for IFS_WRITE_UTF8 and 0 for IFS_WRITE and IFS_WRITE_BINARY. When file-ccsid is 0, the job CCSID is used when creating a new stream file.
  • Overwrite A character or graphic string that specifies whether the write operation appends to the stream file, replaces the stream file, or fails when a stream file with the specified name already exists.
    • APPEND: The data in line is added to the end of the existing stream file. If the stream file does not exist, it is created. This is the default.
    • NONE: The write operation fails if the stream file exists.
    • REPLACE: The data in line replaces the existing stream file if it exists. An existing stream file is deleted, and a new stream file is created. The CCSID of the stream file might change. If the stream file does not exist, it is created.
  • End-of-Line A character or graphic string that specifies the end of line characters to write to the stream file after the line is written. When using IFS_WRITE_BINARY, end of line characters are never appended, so this parameter must have a value of NONE. The carriage-return character is always X'0D'. Based on the CCSID of the stream file being written, the line feed character is X'25' for an EBCDIC CCSID and X'0A' for ASCII and UTF-8 CCSIDs.
    • CR: A carriage return is appended.
    • CRLF: A carriage return and line feed are appended. This is the default for IFS_WRITE and IFS_WRITE_UTF8.
    • LF: A line feed is appended.
    • LFCR: A line feed and carriage return are appended.
    • NONE: No end of line characters are appended. This is the default for IFS_WRITE_BINARY.

Here's an example of how you might use the QSYS2.IFS_WRITE function in SQL:

-- Make sure output file is empty to start
CALL QSYS2.IFS_WRITE
 (PATH_NAME =>'/tmp/mystuff.txt',
  LINE => 'This is some data to be written to the file',
  OVERWRITE => 'REPLACE',
  END_OF_LINE => 'NONE');

-- Add lines to the output file
FOR SELECT OBJNAME AS LIBNAME FROM TABLE(QSYS2.OBJECT_STATISTICS('*ALLSIMPLE', 'LIB')) DO
  CALL QSYS2.IFS_WRITE(PATH_NAME => '/tmp/library_names',
  LINE => LIBNAME, END_OF_LINE => 'CRLF');
END FOR;

This would write the string "This is some data to be written to the file" to the file located at /tmp/mystuff.txt in the IFS.

RPG Example

Let's read a file and write the contents into an IFS location with each row of data on a new line.

The file is MYFILE which contains one field called MYDATA

SQLRPGLE example writing to IFS with SQL 1

A simple RPG to read all the rows in this file, writing to an IFS location might look like this:

**free
/title Simple RPG program to write to the IFS using SQL

// ----------------------------------------------------------
//
// Service - WRITEIFS.RPGLE
//
// Function - Simple RPG program to write to the IFS using SQL
//
// COMPILE NOTES:
//
// Obviously change the source location to match yours:
//
// CRTSQLRPGI OBJ(NICKLITTEN/WRITEIFS)
// SRCSTMF('/home/nicklitten/source/writeifs.sqlrpgle')
// COMMIT(*NONE)
// OBJTYPE(*PGM)
// DBGVIEW(*SOURCE)
// CVTCCSID(*JOB)
//
// Modification History:
// 2020-06-31 V1.0 Created by Nick Litten
// ----------------------------------------------------------
ctl-opt
  main(WRITEIFS)
  option(*srcstmt:*nodebugio:*noshowcpy)
  /if defined(*CRTSQLRPGI)
    dftactgrp(*no) actgrp('NICKLITTEN')
  /endif
  copyright('WRITEIFS.SQLRPGLE: Version 1.0 June 2020');

dcl-proc WRITEIFS;
dcl-pi WRITEIFS end-pi;

// Status Message string for humans
dcl-s stsMsg char(50);

// IFS Location where the data will be written
dcl-s ifsFile varchar(255) inz('/home/nicklitten/myfile.txt');
dcl-s ifsData varchar(255);
dcl-s overwrite varchar(10) inz('REPLACE');

monitor;

// Set SQL option, mainly to force cursor to close at endmodule
exec sql
  set option naming = *sys,
  commit = *none,
  usrprf = *user,
  dynusrprf = *user,
  datfmt = *iso,
  closqlcsr = *endmod;

exec sql
  declare c1 cursor for
  select MYDATA
  from MYFILE;

exec sql open c1;

// Keep reading until end of file (or an error occurs)
dou sqlCode <> 0;

 exec sql fetch c1 into :ifsData;

 Select;
  when sqlcode = 0;

    exec sql CALL QSYS2.IFS_WRITE
             (PATH_NAME =>:ifsFile,
              LINE => :ifsData,
              OVERWRITE => :overwrite,
              FILE_CCSID => 1208,
              END_OF_LINE => 'CRLF');

    // the first write is OVERWRITE to clear the file
    // but all others lets APPEND so we add new lines
    overwrite = 'APPEND';

    stsMsg = 'Completed normally - read next row';

  when sqlcode = 100;
    stsMsg = 'No data found';
    leave;

  when sqlcode > 0;
    stsMsg = 'Completed with warning';
    leave;

  when sqlcode < 0;
    stsMsg = 'Did not complete normally';
    leave;

 endsl;

enddo;

// close the cursor
exec sql close c1;

on-error ;
 dump(a);
 dsply ('*** WRITEIFS has failed!');
endmon ;

return;

end-proc;

Resources

Resource 1

WRITEIFS.SQLRPGLE

 Download
Open
Done?
 Mark lesson complete
Previous Lesson
To Course Page
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
>