Pages

Tuesday, November 24, 2020

Reading a file in the IFS with SQL

read ifs file using new sql table functions ifs_read

One the latest additions to Db2 for i in the latest Technology Refreshes, IBM i 7.4 TR3 and 7.3 TR9, was a Table function offering us the promise of reading a file in the IFS.

In the past I gave an example of how to read a file in the IFS in a RPG program using UNIX-type APIs, but the promise of this Table function makes this new approach look so much easier.

There are three new Table functions that basically do the same thing, which one to use depends upon which format you want the data returned in:

  1. IFS_READ:  Returns the data as plain text
  2. IFS_READ_UF8:  Returns the data in UTF8 format
  3. IFS_READ_BINARY:  Returns the data in a binary string

I have my text file, created using Window's Notepad, and saved in ANSI text format as test.txt. The file contains five records:

First line
Second line
Third line
Fourth line
Fifth line

I uploaded this to my IFS folder, /home/MyFolder, using ACS's IFS tool.

These table functions all have the same parameters:

  1. PATH_NAME:  This one is mandatory as it must contain the path and file name of the file to be read
  2. MAXIMUM_LINE_LENGTH:  Optional. Maximum number of the characters for each line in the IFS file, and must be greater than zero. The default is 2 gigabytes
  3. END_OF_LINE:  Character that denotes the end of the line. Can be:
    • CR Carriage return
    • LF Line feed
    • CRLF Carriage return followed by a line feed
    • LFCR Line feed followed by a carriage return
    • NONE No end of line character. Line length determined by the MAXIMUM_LINE_LENGTH

Now I can show these three Table functions. Let me start with IFS_READ.

01  SELECT * FROM TABLE(QSYS2.IFS_READ(PATH_NAME => 
                                       '/home/MyFolder/test.txt'))

02  SELECT * FROM TABLE(QSYS2.IFS_READ('/home/MyFolder/test.txt'))

03  SELECT * FROM TABLE(QSYS2.IFS_READ('/HOME/MYFOLDER/TEST.TXT'))

04  SELECT * FROM TABLE(QSYS2.IFS_READ('/home/myfolder/test.txt'))

All four of the statements produce the same results.

Line 1: I can use what I call the "parameter descriptor": PATH_NAME =>

Line 2: As the path name is the first parameter I can just give it, without the descriptor.

Lines 3 and 4: The parameter is case insensitive, therefore, I can give the path name is upper, line 3, lower, line 4, or even mixed case, line 2, and the returned results are all the same.

LINE_NUMBER  LINE
-----------  ------------
          1  First line
          2  Second line
          3  Third line
          4  Fourth line
          5  Fifth line

There are just two columns in the results:

  1. Calculated line number. This is equivalent of a relative record number for native IBM i files and tables.

  2. Line. Depending upon the Table function used this will be different data types. As this is the IFS_READ this column is a CLOB type of 2 gigabytes.

Below I am going to show the effect of the maximum record length parameter.

01  SELECT * FROM TABLE(QSYS2.IFS_READ('/home/MyFolder/test.txt',
                                       MAXIMUM_LINE_LENGTH => 2))

02  SELECT * FROM TABLE(QSYS2.IFS_READ('/home/MyFolder/test.txt',2))

Both of these statements produce the same results.

LINE_NUMBER  LINE
-----------  ------------
          1  Fi
          2  rs
          3  t
          4  li
          5  ne
          6  Se

As the maximum line length parameter was two the original lines have been "chopped" into multiple two character lines.

Next up is the UTF8 version of the Table function.

SELECT * FROM TABLE(QSYS2.IFS_READ_UTF8('/home/MyFolder/test.txt'))

The results look identical to the ones from the IFS_READ. With the IFS_READ_UTF8 the Line column is a 2 gigabyte CLOB with a CCSID 1208.

Last one is very different from the other two. IFS_READ_BINARY does not obey the end of line character, it returns a single row of binary characters for the entire contents of the IFS file.

SELECT * FROM TABLE(QSYS2.IFS_READ_BINARY('/home/MyFolder/test.txt'))

The Line column is 2 gigabyte BLOB.

LINE_NUMBER  LINE
-----------  ---------------------------
          1  4669727374206C696E650D0A...

Looking at the data within an IFS file using ACS's "Run SQL Scripts" is useful to determine which file I would need to use. But I could also use this to download the contents of the IFS file to an IBM i file or table.

In this example I want to download the contents of my IFS file into a table in QTEMP. I am going to do this in a CL program.

01  PGM

02  DLTF FILE(QTEMP/OUTFILE)
03  MONMSG MSGID(CPF0000)

04  RUNSQL SQL('CREATE TABLE QTEMP.OUTFILE (RECORD) AS +
05              (SELECT LINE FROM TABLE +
06                (QSYS2.IFS_READ(''/home/RPGPGM/test.txt''))) +
07              WITH DATA') +
08          COMMIT(*NC)

13  ENDPGM

If I see the following in the output file:

RECORD
-----------
*POINTER
*POINTER
*POINTER
*POINTER
*POINTER

I need to change line 5 to cast the column LINE to character.

05              (SELECT CAST(LINE AS CHAR(50)) FROM TABLE +

The results now show the data:

RECORD
-----------
First line
Second line
Third line
Fourth line
Fifth line

I could use the SQL Substring to break the line data apart into columns if I wanted to.

04  RUNSQL SQL('CREATE TABLE QTEMP.OUTFILE +
05              (COLUMN_1,COLUMN_2) AS +
06              (SELECT CAST(LINE AS CHAR(3)), +
07                      SUBSTR(CAST(LINE AS CHAR(40)),4) +
08                 FROM TABLE +
09                (QSYS2.IFS_READ(''/home/RPGPGM/test.txt''))) +
11              WITH DATA') +
12           COMMIT(*NC)

Line 6: Make a new column of the first three characters of the line.

Line 7: The new second column contains the data from the fourth place to the end of the line.

Which gives me the following in the output file:

COLUMN_1  COLUMN_2
--------  --------
  Fir     st line
  Sec     ond line
  Thi     rd line
  Fou     rth line
  Fif     th line

If the contents of the IFS file was CSV I would still use the CPYFRMIMPF command.

 

You can learn more about the SQL IFS_READ Table functions from the IBM website here.

 

This article was written for IBM i 7.4 TR3 and 7.3 TR9.

13 comments:

  1. I like these functions, however, many of us have been able to (at least) read from the IFS in SQL.

    select CSV.*
    from
    table(values GET_CLOB_FROM_FILE('/folder/path/to/IFS_FILE.csv',1)) SF(CSVDOC)
    , table(split(CSVDOC, x'0d25') ) CSV
    with cs;

    Some of these functions also need to be installed and may require additional PTFS. Also, you would need to know the line separator characters.

    -Matt

    ReplyDelete
    Replies
    1. I was researching this topic yesterday, so i thought i would post what i came up with:
      CREATE TABLE PAJKM.@@ROD AS (
      SELECT
      CAST(REGEXP_SUBSTR( LINE, '("[^"]*"|[^,]+)', 1, 1) AS VARCHAR(50)) AS column1
      ,CAST(REGEXP_SUBSTR( LINE, '("[^"]*"|[^,]+)', 1, 2) AS VARCHAR(50)) AS column2
      ,CAST(REGEXP_SUBSTR( LINE, '("[^"]*"|[^,]+)', 1, 3) AS VARCHAR(50)) AS column3
      ,CAST(REGEXP_SUBSTR( LINE, '("[^"]*"|[^,]+)', 1, 4) AS VARCHAR(50)) AS column4
      FROM TABLE(QSYS2.IFS_READ('/home/pajkm/rod.csv'))
      ) WITH DATA

      I just used varchar since i was just doing a proof of concept. But you can cast to any valid format.

      Delete
  2. Very usefull...and simple way to access to a IFS file...

    Thanks for sharing

    ReplyDelete
  3. I have used this and it's very handy!

    ReplyDelete
  4. Thank you for sharing this Simon

    ReplyDelete
  5. Very useful, thanks.

    ReplyDelete
  6. I need to read a file listing from an IFS folder (*.result) which are text files, can i somehow grab the text files with "wildcard" syntax, because the file name has a time stamp in it. so i don't know the exact name at the time I want to read it.

    ReplyDelete
  7. Can use in PATH_NAME a variable?

    ReplyDelete
    Replies
    1. In RPG you cannot use a variable as a path name, the compiler will not allow it.
      I used a CREATE TABLE statement to create a file in QTEMP and if I used the EXECUTE IMMEDIATE I could get it work.

      Delete
  8. is there any way to read json format file using sql query
    my file is in following format
    {"one" : "1"}
    {"two" : "2"}
    {"three" : "3"}

    I have this content in file.txt which is in json format and wanted to read this data using sql query, can you give me the query. Here each line is ending with new line character

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.