Start of change

IFS_READ, IFS_READ_BINARY, and IFS_READ_UTF8 table functions

The IFS_READ, IFS_READ_BINARY, and IFS_READ_UTF8 table functions read an integrated file system stream file identified by path-name. The file's data is returned as character, binary, or UTF-8 data. It can be returned as one string of data, or it can be broken into multiple lines using a specified length or end of line characters.

Authorization: The caller must have:

  • For objects not in the QSYS.LIB file system:
    • Execute (*X) data authority to each directory preceding the stream file being read and
    • Read (*R) data authority to the stream file
  • For objects in the QSYS.LIB file system:
    • Execute (*X) data authority to each directory preceding the object being read and
      • For a *SAVF object, Read, Write, and Execute (*RWX) data authority to the object
      • For all other object types, Read (*R) data authority to the object
Read syntax diagramSkip visual syntax diagramIFS_READIFS_READ_BINARYIFS_READ_UTF8( PATH_NAME => path-name,MAXIMUM_LINE_LENGTH => maximum-line-length,END_OF_LINE => end-of -line,IGNORE_ERRORS => ignore-errors )

The schema is QSYS2.

path-name
An expression that returns the path name identifying the stream file to read. A relative path name is relative to the current directory. 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 object is not a stream file, an error is issued.
maximum-line-length
An integer value that specifies the maximum number of characters returned for each line. It must be greater than 0. The default is 2 gigabytes.
If end-of-line is NONE or if no end of line sequence is found in the stream file, the number of characters returned for each line will be limited to this value.
If an end of line sequence is encountered before this length is reached, the line will end at that point. The next line returned will start with the character directly after the end of line sequence.
end-of-line
A character or graphic string that specifies the end of line characters to be recognized in the stream file. Each occurrence of an end of line sequence determines a line which is returned. The end of line character sequence is not returned with the line. When using IFS_READ_BINARY, end of line characters are never processed, 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 read, the line feed character is X'25' for an EBCDIC CCSID and X'0A' for ASCII and UTF-8 CCSIDs.
ANY
Any of the four end of line sequences indicate the end of a line. This is the default for IFS_READ and IFS_READ_UTF8.
CR
A carriage return indicates the end of a line.
CRLF
A carriage return and line feed indicate the end of a line.
LF
A line feed indicates the end of a line.
LFCR
A line feed and carriage return indicate the end of a line.
NONE
No end of line characters are recognized. maximum-line-length determines the number of characters to be returned. This is the default for IFS_READ_BINARY.
ignore-errors

A character or graphic string expression that identifies what to do when an error is encountered.

NO

An error is returned.

YES
A warning is returned.
No row is returned when an error is encountered. This is the default.

The result of the function is a table containing rows with the format shown in the following table. All the columns are nullable.

Table 1. IFS_READ table function
Column Name Data Type Description
LINE_NUMBER INTEGER Relative position of this line in the stream file.
LINE
For IFS_READ:
CLOB(2G)

For IFS_READ_BINARY:
BLOB(2G)

For IFS_READ_UTF8:
CLOB(2G) CCSID 1208
 The data for this line.
  • For IFS_READ, the data from the stream file will be returned in the job CCSID.
  • For IFS_READ_BINARY, the data from the stream file will be returned exactly as it is stored without conversion.
  • For IFS_READ_UTF8, the data from the stream file will be returned in CCSID 1208.

Example

  • Read the data from stream file /usr/file1. Break lines when a carriage return/line feed sequence is encountered. The result will be in the job's CCSID.
    SELECT * FROM TABLE(QSYS2.IFS_READ(PATH_NAME => '/usr/file1',
                                   END_OF_LINE => 'CRLF'));
  • Read the data from stream file /usr/file2. If the file size is less than 2 gigabytes, the result will be a single row containing the entire file. If the file size is greater than 2 gigabytes, multiple rows will be returned.
    SELECT * FROM TABLE(QSYS2.IFS_READ_BINARY(PATH_NAME => '/usr/file2'));
End of change