Reading a file in the IFS with SQL

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.