Reading IFS Text Files with SQL

For years we've been using the C runtime library to open, read from and write to IFS files. Typically only text files are processed using RPG, but that is more of a statistic than a restriction.

One limitation that keeps people from using IFS text files to store information or simply read data produced on another system and stored on the IFS is the complexity of these APIs. While they are not very difficult to master, they do have a number of flags and switchs that often cause confusion and frustration while using them.

SQL UDTF for IFS Files

To overcome the issue of complexity, I've created an SQL Table Function or UDTF that accepts the name of an IFS text file and allows your RPG program to read that text file just like any other DB2 for i database table. The UDTF is named IFSFILE and is shipped free with our SQL Query File package.

To illustrate how to use it, I've written a little "WRKIFS" (Work with IFS Text File Data) program. This program accepts any IFS text file name and loads it into a subfile using SQL. Certainly WRKIFS is no more useful than Option 5 in the WRKLNK command's interface and is used here for illustration purposes only.

The syntax for IFSFILE is as follows:

SELECT TEXTDATA from TABLE( ifsfile( '/home/cozzi/countryCode.txt' ) ) X

The UDTF IFSFILE accepts the qualified or unqualified IFS file name. I use a vaiable in the RPG example below, but you can pass in a hardcoded name as well. The trailing "X" is an SQL idiosyncrasy and is required when using UDTF (you can try it without the X but it won't work). Note the letter X is a correlation name and may be anything you want, I just used X for this example.

IFSFILE UDTF returns one column (field) named TEXTDATA, therefore using SELECT TEXTDATA or SELECT * produces the same results. Internally, TEXTDATA is defined as VARCHAR(2048) but if you know the data is shorter than that (and if usually is) you can define your host variable at the appropriate length. In the example that follows, I settled on a 1024A VARYING field, but the results written to the subfile are truncated to 128 characters (maximum visible line length in a subfile). I also created the program using *DS4 mode only for the display file as virtually no one configures Client Access restricted to *DS3 mode (do they?).

Here's the example illustrating how to call IFSFILE within an SQL statement to open, read and process an IFS Text file without using the C runtime library.

     P loadIFSFile...
     P                 B
     D loadIFSFile...
     D                 PI
     D textFile                     640A   Varying Const
     D ifsData         S           1024A   Varying
      /free
           rrn = 0;
           indds.dspsfl = *off;
           indds.dspsflctl = *off;
           write header;  // Clear subfile

            exec sql Declare IFS Cursor for
                      SELECT textdata
                      FROM   TABLE( ifsfile(:textFile) ) X ;
            exec sql OPEN IFS;
            exec sql FETCH IFS INTO :ifsData;
            IF  (sqlState >= '02000'); // Nothing returned?
                 resultSet = 'No data returned. SQLSTATE(' + sqlState + ')';
            else;
              DOW (SQLState < '02000');
                 rrn += 1;
                 textLine = %trimR(ifsData);
                 write  detail;
                 exec sql FETCH IFS INTO :ifsData;
              enddo;
            endif;
            exec sql CLOSE IFS;
      /end-free
     P loadIFSFile...
     P                 E