Practical RPG: BLOBs, CLOBs, and XML Part 1

  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

The IFS is a great place to store stream files but it has some serious limitations. This series of articles presents an alternative.


Recently, several different requirements ended up colliding in a way that led me to do a lot of research on using Large Objects (LOBs) in my RPG programs. While they require a little bit of extra up-front investment, LOBs provide a way to integrate large-scale use of stream file data into your enterprise system.

Relating to LOBs

In the world of data processing (and I'm using that term very specifically), we see data in two very different ways: stream files and relational data. Most of us were familiar with relational data before we ever heard the term. That's especially true when using the larger umbrella definition of relational data that includes both standard indexed access data (what is called "native access" on the IBM i and ISAM, or indexed sequential access method to the rest of the computing world) and fully relational databases that support SQL access and all that entails, such as commitment control and database constraints.


Stream files, on the other hand, are the staple of both the PC and the UNIX world. Stream files derive their name from the fact that the data is usually processed in a stream, from beginning to end, rather than the random access of relational data. This is due to the fact that stream files have variable-length records; there's no easy way to position yourself to the beginning of the seventh line when each line can vary in length. Many stream files contain what is essentially human readable form: strings of data, sometimes delimited with commas or tabs, with a carriage-return and/or linefeed at the end of each record. Other stream files may include images or other binary data, such as programs or compressed files.


LOBs are the way that traditional relational databases deal with the variable-length data. Relational databases have VARCHAR fields, which support smaller amounts of data, but for anything larger than about 32K (depending on the database), relational databases turn to LOBs. DB2 supports three kinds of LOBs: BLOBs, CLOBs, and DBCLOBs. Each is designed for different content. BLOBs are Binary Large Objects and are primarily intended for things like images and other binary data. CLOBs are used to store traditional character data like email messages or text documents. DBCLOBs are like CLOBs except that they support double-byte data. We'll see in a moment where these data types come into play.

The IFS and the Incredible Unshrinkable User Profile

This first article will focus on the downside of a high-volume IFS implementation. The key attribute here is high-volume. As long as your use is reasonable, the IFS can serve your purposes just fine. It acts like any other directory system you may be used to, whether it's Windows-based or a UNIX variant. In fact, the IFS can be used just like any other folder system, and mapped drives allow you to copy files and folders and basically just run your system. If you're used to copying files to a folder and processing them in batches, the IFS provides the location, and the IBM i has all the APIs to allow access from high-level languages like RPG. You can loop through a folder and process the files one at a time, opening them and reading them into your program. You can also write stream files to be used by other systems, whether it's a simple comma-delimited file or a full-blown Excel spreadsheet.


This may not be news to you; many shops have been using these capabilities for quite some time now. But the whole idea became much more interesting to me with the explosion of XML processing in the industry. XML is a perfect example of a stream file: the data not only doesn't have fixed-length records, but the data is hierarchical in nature, with different data in each line. The data in a given XML tag is context-sensitive, based on where in the document it is located, so you can't really look at a single tag in isolation even if you could jump to it with a single read. So the only way to really store XML data (for now) is as a stream file.


And as I said already, this is a perfectly adequate method, until you get to higher volumes. At that point, the IFS becomes a less-friendly environment.


How unfriendly? Well, there are two stages. The first is predictable, readily diagnosed, and mostly just annoying. In many load-related problems, you only see issues only when the system reaches some unspecified threshold and the wheels start to come off the machine. In this case, there's a very specific point where things break down: 16383. To paraphrase Jeff Foxworthy, if you recognize that number you may be a geek. It's 2^14 – 1, but what it represents is the maximum numbers of files in a folder before it begins to lose functionality. After this point, many system functions begin to fail. This occurs most noticeably in the Qshell and PASE environments; any system function you might attempt to run over that folder will terminate with an error message related to too many files. This happens when you try to move them, delete them, ZIP them up using the jar command, you name it. Basically anything you try to do inside the shell environments fails. You can still execute the native IBM i commands such as MOV and DEL, but that doesn't help if you're trying to archive them.


And why would you do that?


Well, that brings us to the more significant of the two problems: the ever-expanding user profile and the never-ending SAVSECDTA. You see, every object you own or have specific authority to gets an entry in your user profile. Files, programs, data areas, you name it, there's an entry in the user profile. Which means that if you own or are authorized to thousands of objects, you have thousands of entries in your user profile. Note the magnitude, though: thousands of objects, or maybe tens of thousands, is the high end for a typical IBM i shop.


However, it's nothing to add hundreds of thousands or even millions of stream files in a high-volume transaction shop. In that case, the user profile that adds those files grows and grows. The more files, the bigger the user profile. If you have millions of files, the user profile can grow to a gigabyte or more in size, and SAVSECDTA starts to get measured in hours. Not only that, but thanks to the way the folders work, anybody who has authorization to the folder where these files are created also gets authority to the files, so those profiles grow as well.


And the real problem is that no way exists to shrink those user profiles. Even after you've removed all the entries, the index space remains and there's no way to reclaim it short of deleting the user profile. In fact, unless you plan to get rid of that user profile completely, you'll need to create a second temporary user profile, delete the first profile and transfer ownership to the temporary folder, recreate the offending user profile, and then delete the temporary user and transfer ownership back to the original. Not the sort of thing you want to do on a regular basis.


To alleviate this problem, start out by assigning an authorization list to your high-volume IFS folders. This will at least help to minimize the authority-related entries for your IFS files. However, the only long-term solution is to get away from the IFS for your stream files entirely, and that's where LOBs come in. I'll show you more about those in a later article.