You want to use LOBs in your RPG program but don't know where to start; read this article to find out how!

In the first article in this series, I identified situations where it is preferable to store stream data in a database rather than in the IFS. Specifically in my case, I had literally millions of stream files proliferating in the IFS and causing some real processing issues. My alternative was to store the stream files in the database. Implementing LOBs is not difficult, but it does require a number of steps, many of which may be unfamiliar to most of us.

Getting Started: Creating the Database

You need to do two separate things: define the database and access the data. I know this sounds simplistic, but they really are two different steps. The first step, creating a database in which to store the data, is relatively straightforward, but it does mean you have to use DDL to create the file rather than traditional DDS; this is the first break from tradition for many RPG programmers. If you're unfamiliar with SQL or DDL, you can find tons of articles right here on this site, including one or two of my own. Those and many more excellent SQL-related articles can be found in the Programming / SQL articles section.

Let's take a look at the DDL for a very simple file:

CREATE TABLE MYLIB/MYCLOBS

(MCKEY CHAR (20) NOT NULL WITH DEFAULT,

   MCDATA CLOB(1G) NOT NULL WITH DEFAULT)

Nothing to it! This creates a file with a 20-character key and a data field. The data field in this case is CLOB, and the length is nominally set to one gigabyte (1G). Don't worry too much about the size of the field; DB2 will allocate only the size of the data that you write. If you write a bunch of little CLOBs to the file, it won't get very big. On the other hand, one of my applications adds another gigabyte to its CLOB file every two or three days.

You may be wondering why I chose CLOB as my data type. Today, we have three data types available for large objects: BLOB, CLOB, and XML. In many ways, CLOBs are a subset of BLOBs: while BLOBs can contain any data type (including binary data like images), CLOBs are intended for pure character data and because of that they have a CCSID associated with them. This allows the data to be translated when it's accessed (especially between EBCDIC and ASCII or Unicode). XML data is an even more restrictive type: it expects the data to be well-formed XML and if it's not, you'll get an error when attempting to write to the database. An interesting thing about XML: you don't specify the length when you define the file:

CREATE TABLE MYLIB/MYXML

(MXKEY CHAR (20) NOT NULL WITH DEFAULT,

   MXXML XML NOT NULL WITH DEFAULT)

Okay, that's the database. One last thing: files with LOB data columns (that is, either BLOB, CLOB, or XML data types) must be journaled. No exceptions. You will get an error if you try to write to a file with LOBs and it's not journaled.

Adding a Record

When adding a record to a LOB database, you need to have your LOB data. There are two basic options: either the data is in a stream file on the IFS or the data is in memory. Interestingly enough, it's actually a little easier to add the data when it's already in a file on the IFS. You'll use an embedded SQL statement that looks something like this:

d iUnique       s             20a          

d iFileName     s           128a varying

/free

exec sql                                  

insert into MYLIB/MYCLOBS              

   (MCKEY, MCDATA)                        

values(                                

     :iUnique, get_clob_from_file(:iFileName))

with ur;
/end-free

In the code above, iUnique is the field used to store the unique key for the new record, while iFileName is the full qualified name of the data file in the IFS. The function get_clob_from_file is a built-in function provided by DB2, and corresponding functions exist for both BLOBs and XML. "But Joe," you might be saying, "don't you still have an IFS file?" And you would be correct! But in many cases you'll start with an IFS file. An example might be an EDI package in which the EDI communication portion just drops a file into your IFS. In that case, you would take the received file, write it to your database file, and then delete it from the IFS. Your IFS is de-cluttered, and the data is available to your application.

Adding data from memory is a little trickier, but only because you have to use the built-in SQL data type CLOB. Here's an example:

d iUnique       s             20a          

d iBufferPtr   s              *          

d iBufferLen   s             10i 0        

d MsgObj       s                  SQLTYPE( CLOB: 16000000)    

d Buffer       s               a len(16000000) based(pBuffer)

/free

pBuffer = iBufferPtr;                    

MsgObj_Data = %subst(Buffer:1:iBufferLen);

MsgObj_Len = iBufferLen;                

exec sql                                

   insert into MYLIB/MYCLOBS              

     (MCKEY, MCDATA)                        

   values(                              

     :wUnique,:MsgObj)    

   with ur;
/end-free

In this case, rather than passing in a file name, we instead pass in a pointer to the data and the length. This way, the data can be anywhere: in a variable in your RPG program, in a user space, whatever makes sense for your application. You build the LOB data however you want and then just pass in a pointer to the data and the data length. The first three lines of /free code then use those parameters to stuff the data into the CLOB variable.

SQLTYPE variables are a little unusual; when you define an SQLTYPE variable, it ends up creating a data structure with subfields that have odd naming conventions. Basically, it takes the name you define and then appends a suffix to identify the subfields. In the case above, the declaration of SQLTYPE MsgObj generates two variables, MsgObj_Data and MsgObj_Len. It's the program's responsibility to fill those generated fields with the CLOB data and the length. Personally, I would have preferred that the data structure be generated as a qualified data structure with subfields, but what's done works as long as you don't create your own fields ending in _Data or _Len (if you do, you'll get compile errors).

So, if you review the code, you'll see that I use a variable Buffer with a basing pointer pBuffer. I set the basing pointer to the address passed in and then use the %subst built-in function to extract the calling program's data and put it into the CLOB data field MsgObj_Data. I also store the length and then execute the SQL insert statement, which will perform the functional equivalent of the previous example. In both cases, you'll end up with a record in the file, and if you dump the file using DSPPFM, you'll see something like this:

MYKEYVAL           *POINTER

The first 20 characters are the key value, while the CLOB field is represented by the word *POINTER. One annoyance for green-screen programmers is that you cannot directly see CLOB data using traditional tools like DSPPFM or even DBU. Instead, you'll need to use either STRSQL and the CAST function or else view it through the IBM i Navigator. Generally speaking, as you move more into LOBs and SQL in general, I recommend getting acquainted with Navigator.

That's it for creating LOB data. In the next installment, we'll look at using that data and building an infrastructure for a practical application of the technology.