As the IFS grows in popularity on the iSeries, it is becoming important to access text file content programmatically. INI files, XML files, and delimited files store important application settings and data. In this two-part series, I'll offer two mechanisms whereby SQL can query IFS file data.
The first technique utilizes a user-defined function (UDF) to read the entire contents of an IFS file and return it as a single column in a SELECT statement. The function receives a qualified IFS file name and returns the file's contents in a character large object (CLOB) variable.
Program FileToClob is an embedded SQL RPG program that serves as an external UDF to place file contents in a CLOB. The concept is illustrated by the following SELECT:
From QSQPTABL /* QSQPTABL will only return one row */
Before using FileToClob, register it with SQL by using the Create Function statement:
(parmIFSFile VarChar(255))
Returns Clob(1M) As Locator
Language RPGLE
Parameter Style SQL
External
Reads SQL Data
Deterministic
Returns Null on Null Input
CLOBs are superior to VARCHARs in that they can hold up to 16 MB of data, whereas VARCHARs are limited to about 32 K. (Note that some older querying tools may not recognize large object data types.) Further, green-screen tools such as STRSQL will display a value of '*POINTER' for a CLOB. In these cases, a portion of the CLOB's contents must first be cast to VARCHAR.
Let's look at this small program. The reading of the IFS file into a CLOB is handled by a peculiar data type called a CLOB_FILE that is only available to high-level language (HLL)programs using embedded SQL:
The CLOB_FILE data type definition is translated by the SQL pre-compiler into the following data structure:
D CLOBFILE_NL 10U
D CLOBFILE_DL 10U
D CLOBFILE_FO 10U
D CLOBFILE_NAME 255A
Notice that subfields are included that contain the original variable name followed by a suffix. The _fo (file option) suffix defines whether the file will be opened for read, append, or write. The _nl and _name variables represent the file name and file name length. To use the CLOB_FILE data type, these subfields need to be populated.
Don't be alarmed when SEU or CODE/400 flags you with an error on the SQLTYPE keyword. For some reason, neither editor recognizes it (since V4R4!). Ignore the editor's error, as the program will compile.
One other thing to note is the use of the "locator" data type:
With the Clob_File's attributes set, we simply assign a locator to point to its contents with the following statement:
C+ Set :ClobData=:ClobFile
C/End-Exec
A locator is a pointer or "handle" to a large object. This allows a client to read through a resultset containing a large object column without necessarily having to retrieve all of the large object data. The locator allows the large object data to be retrieved only when the client application needs it.
Here are a few other tidbits:
- For older OS/400 versions, you may have to change the Set Option's Commit value from *NONE to *CHG.
- The function will not return files larger than 1 MB.
- CCSID conversions will be performed automatically (but don't attempt to read a binary file; use a BLOB for that).
- The CLOB_FILE data type can also be used to write a file to the IFS. SQL will give you programmatic access to read and write IFS files!
For practical use, I've been using the FileToClob function as an easy way to search for data in a large number of XML files in the IFS. Qshell provides a mechanism for doing this type of search as well, but I prefer the tight programmatic integration that SQL offers, even if it does mean extra overhead. This SQL interface also allows client/server and Web applications to access IFS data without having to open up NetServer to potentially unsecured situations.
For another use, FileToClob can read source members using the /QSYS.LIB file system. I recently needed to find all CL programs on a system that used the RUNQRY command without specifying the QRYFILE parameter. To do this, I dumped a list of source members to an outfile called Qtemp/MbrList using the "DSPFD lib/file *MBRLIST" command. I then used FileToClob to hunt through each source member:
Select MLLib As SrcLib, MLFile As SrcFile,
MLName As SrcMbr, UCase(FileToClob(
'/qsys.lib/'||Trim(MLLib)||'.lib/'||
Trim(MLFile)||'.file/'||
Trim(MLName)||'.mbr')) As SrcText
From QTEMP/MbrList
Where MLSEU2 In ('CLP','CLLE'))
Select SrcLib, SrcFile, SrcMbr
From CL_Members
Where SrcText Like '%RUNQRY%'
And SrcText Not Like '%QRYFILE%'
In Part 2 of this series, I'll illustrate a table function that can read a delimited text file and return each row as a row in the result set.
References
FileToClob is a simple re-engineering of a UDF called FileToBlob, which served the purpose of reading binary data rather than text data. For more information on this related UDF, see "Return of the Blob."
"Invasion of the Blobs" contains an intro to BLOBs and has VBA code illustrating how to work with large object data using ActiveX Data Objects.
For more information on large objects, see SQL Programming Concepts and SQL Host Language Programming.
Code for this article can be downloaded here.
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online