Developer > General

BLOBs, CLOBs and RPG

In the last few months, we've fielded several questions about how to deal with large objects in the database from within an RPG program. Often these are related to accessing data that exists on or has been ported from other databases. Since other folks may need this information, we decided to take a look at the answers to these questions.

First of all, what are "large objects" and why would they appear in our databases? The data types we're talking about here are known as BLOBs (binary large objects) or CLOBs (character large objects). A third large object type, DBCLOB (double-byte character large object), is only used for languages that require a double-byte character set. Another related data type is a DataLink. All of these data types can only be created and accessed using SQL. There's no DDS or native RPG support for them.

A CLOB is similar to a character field except that it is, well, large. How large, you ask? A character field defined using DDS or SQL is limited to 32K. By contrast, a character field in RPG IV can be defined up to 64K. So if you were to have a long product description, for example, that might exceed 32K in size, you could deal with it in RPG (up to the 64K limit), but to store that description in a database record, you would need to use a CLOB.

You may wonder what you would do if your product description were longer than 64K. You could still store it in a CLOB, as long as it wasn't larger than 2 GB. Hopefully that will be large enough for even the largest of your product descriptions. Of course, there's now the issue of how to deal with those larger fields in your RPG programs. We'll come to that later.

The difference between CLOBs and BLOBs is that CLOBs, like other character fields, have a Coded Character Set Identifier (CCSID) associated with them. This can be used to translate the data from the character set in which it's stored into the character set in which it's being used. In the product description example we're using here, perhaps the lengthy product description is developed and maintained using a workstation-based program, which would typically be using an ASCII character set. The description could be stored in the database file in ASCII format, even though the rest of the "ordinary" character fields in the file were in EBCDIC format. If that description, or a portion of it, were to be used in an iSeries host-based application, it would be translated to EBCDIC automatically by the database. Typically, the CCSID of all character fields, including CLOBs, would be the same and defaults to the CCSID of the file.

This brings us to BLOBs, which may also be up to 2 GB in size, but which have no CCSID. Therefore, no character translation is done on the data in these fields, because it isn't character data. Images, music, compiled programs, compressed data, etc., are typically stored in BLOBs. It's far more likely that we will need to deal with CLOBs from an RPG program. But, since the techniques used to deal with both types are similar, we'll deal with CLOBs in our examples.

We also mentioned a DataLink as an alternative way of storing this type of data. Using a DataLink, a reference to a file in the IFS is stored in the database rather than storing the actual data itself. While using DataLinks in outside the scope of this article, be aware that they are another option for storage and management of this type of data.

To create a CLOB, we'll create a product description file with a product code, a short description and a long description. The long description is defined to be up to 70K with 1000 bytes allocated initially. (Note: We specified "Not Null" for all of the fields to simplify our program examples. In reality, CLOBs are more likely than many of the traditional data types to require null capability.)

CREATE TABLE PARTNER400/PRODUCT
(PRODCODE DEC (5 ) NOT NULL WITH DEFAULT,
PRODDESC CHAR (30 ) NOT NULL WITH DEFAULT,
LONGDESC CLOB (70K ) ALLOCATE(1000) NOT NULL WITH DEFAULT)

Next page: >>