How to read data from IFS file using SQL?

How to read data from IFS file using SQL?

We have the stream files available in the IFS(Integrated File System) on IBM i and there are different ways of reading data from these stream files.

We can use CPYFRMSTMF or CPYFRMIMPF command to copy the data from IFS stream file to Database file.

We can use 'open' and 'read' C functions to read data from ifs stream file in an RPGLE program.

Now, apart from above ways we do have other ways too to read data from ifs stream file like using SQL table function 'IFS_READ'. There are some more table functions like 'IFS_READ_BINARY' and 'IFS_READ_UTF8' to read the data and return in Binary and UTF8 formats respectively.

Here, we will discuss about SQL table function 'IFS_READ' to read data from ifs file and others.

Using SQL Table function IFS_Read

select * from table(qsys2.ifs_read('/home/easyclass/pf20'))

select * from table(qsys2.ifs_read(PATH_NAME => '/home/easyclass/pf20'))

above SQL query is similar and here we just passed the ifs stream file path along with the file name normally or against the parameter PATH_NAME. We will get the data for this file that gets displayed when running from the Run SQL Scripts.

from the above attached result screenshot of the SQL queries using table function 'IFS_Read' it is clear that there are only columns associated with the IFS_Read table function

LINE_NUMBER - it indicates the position of the line in the ifs stream file.

LINE - it indicates the actual data in the ifs stream file. Also, the data returned is in the CLOB(character large object type) data type.

Using SQL Table function IFS_Read_Binary

select * from table(qsys2.ifs_read_Binary('/home/easyclass/pf20'))

select * from table(qsys2.ifs_read_Binary(PATH_NAME => '/home/easyclass/pf20'))

above SQL query is similar and here we just passed the ifs stream file path along with the file name normally or against the parameter PATH_NAME. We will get the data in the binary format for this file that gets displayed when running from the Run SQL Scripts.

from the above attached result screenshot of the SQL queries using table function 'IFS_Read_Binary' it is clear that there are only columns associated with the IFS_Read_Binary table function same as IFS_Read table function. Also, the data returned is in the BLOB(Binary large object type) data type.

Using SQL Table function IFS_Read_UTF8

select * from table(qsys2.ifs_read_UTF8('/home/easyclass/pf20'))

select * from table(qsys2.ifs_read_UTF8(PATH_NAME => '/home/easyclass/pf20'))

above SQL query is similar and here we just passed the ifs stream file path along with the file name normally or against the parameter PATH_NAME. We will get the data in the binary format for this file that gets displayed when running from the Run SQL Scripts.

from the above attached result screenshot of the SQL queries using table function 'IFS_Read_UTF8' it is clear that there are only columns associated with the IFS_Read_UTF8 table function same as IFS_Read table function. IFS_READ_UTF8 returns data in the UTF8 format.

What happens if an incorrect path is passed to any of the IFS_Read table function

No data received and SQL query is completed with the SQL code 100.

here, starting slash(/) is missing from the path passed to the SQL table function ifs_read.