In Part 1 of this series, I began a discussion on the growing need to have programmatic mechanisms to access the text data within IFS files. I illustrated a scalar user-defined function (UDF) called FileToClob that returns the contents of an IFS file as a single character large object (CLOB) column. While this method has the advantage of granting access to the stream file's entire contents in a single column, there may be occasions when you'd want to process a file one line at a time. A user-defined table function (UDTF) is a perfect fit for this task (V5R2 is required).

Table functions offer the ability to return a two-dimensional array structure as though it were a set of rows and columns from a table. The column structure of a table function must be pre-defined, but there is no limit to the number of rows that may be returned. RPG program ReadIFSFR is designed to serve as a table function that reads an IFS text file and returns its contents one row at a time.

Here is a sample Select statement using ReadIFSFR:

Here is the CREATE FUNCTION statement required to register the program with SQL:

Table function ReadIFSFR accepts two parameters: file name and row delimiter. Parameter "file name" is a fully qualified file name to a particular file on the IFS. (If you're using the QNTC file system, this can even include reading text files directly from a Windows share.) The "row delimiter" parameter is a value that informs the program how to parse the data into rows. The following table shows a list of the three most common row delimiters used in a text file:

In case you're wondering why I've shown the EBCDIC delimiter values rather than the ASCII values (since most IFS text files will probably be encoded in some form of ASCII), the answer is because the IFS APIs will automatically return the data to the RPG program in EBCDIC format. Therefore, the RPG program will need to know the EBCDIC value for the required delimiter.

When executed in a Select, the table function returns three columns: line number (Line_No), continuation number (Cont_No), and line text (LineText). The line number is a simple counter indicating where particular data is located in a file. The actual file data is returned in column line text. A maximum width of 2048 characters per line is allowed. If a single line exceeds this limit, the data will be "wrapped" to a second line. The continuation number column is used to indicate if a line has been wrapped so that the line counter column remains accurate.

How It Works

Programs designed to act as SQL table functions have logic that can be divided into three phases: open, fetch, and close. Each phase is done in response to a request from the database manager.

During the open phase, an IFS file is opened using the Open API. During the fetch phase, the table function calls the Read API to read the data. The function's output columns are populated with data representing a single row being returned. The fetch phase is repeated until the program indicates to SQL that there is no more data. It does this by setting the SQL state variable to '02000' (end of data). Finally, during the close phase, the IFS file is closed, and the LR indicator is set on so that the program will end.

I often use this table function for easily reading import files and application setting (INI) files. For example, a user may create a simple text file containing a list of item numbers to be updated in a file. Rather than creating a temporary table and copying the IFS file into the table, I simply query the text file directly:

Don't forget that a table function needs to be assigned a correlation name (A).

Interestingly, a source member can be read like a text file using the qsys.lib file system and specifying a CR/LF delimiter:

Note that table functions are read only so you can't issue an update against this member!
When using multi-membered source files with SELECT statements, this method provides an alternative to using the CREATE ALIAS command or issuing an OVRDBF command.

Don't stop with this example. UDTFs can be designed to work with other types of text files--such as fixed record length or markup (XML, HTML) files--and return the data in a tabular format. A further complement to these text functions would be a directory API wrapped with a table function so that SQL has dynamic access to all the entries on the IFS.

The IFS' growing popularity will cause programmers to seek easier ways to access its data. Indeed, Microsoft has long offered the Windows world programmatic access to many kinds of text files with its ODBC text driver. The ability to extend SQL with scalar and table functions allows iSeries programmers to join the game by applying SQL's searching, joining, and sorting capabilities to IFS data.

References

For a detailed explanation of how to write an external table function, see "Query Remote Database Tables from the iSeries using SQL and Java." Although the article uses Java, the programming concepts are the same, and explanations of the SQL keywords are relevant to other languages.

Also see chapter 13, "Writing User-Defined Functions," of the SQL Programming Concepts guide.

Also Read

SQL 101: DDL Recap—From Physical Files to SQL Tables, Starting the Journey

TechTip: Generate and Email a Spreadsheet with SQL

Practical SQL: Journal Forensics Using SQL