RPG And The BLOB

I’m trying to use a SELECT/INTO embedded SQL statement in an RPG program that accesses a table that includes a BLOB(20K) column. The INTO target is an externally defined data structure based on the table. However, I get compiler errors unless I remove the BLOB column from the table. What’s up with that?

—Four Hundred Guru reader

The problem is that RPG doesn’t have a native data type equivalent of a BLOB (or the other large object types CLOB/DBCLOB, for that matter.) The reason for this is that large object types can be up to 2GB in size, far greater than RPG’s current maximum variable size of 16MB.

Consider the following table definition containing a BLOB:

CREATE OR REPLACE TABLE QGPL.TEST (
MY_KEY INT NOT NULL PRIMARY KEY,
MY_DATA VARCHAR(32) NOT NULL,
MY_IMAGE BLOB(20K) NOT NULL)
RCDFMT TESTR 

Within the RPG program is an externally described data structure dsTest (referencing the above table) and the following embedded SQL statement:

SELECT *
  INTO :dsTest
  FROM Test
 WHERE My_Key=:My_Key;

When using an externally described data structure containing a BLOB, the RPG compiler gives an ugly warning:

RNF7575 The data structure has fields with an unknown datatype.
The fields are ignored.

This warning indicates that the RPG compiler doesn’t know what to do with the BLOB data type column so it ignores it. The data structure definition in the compiler listing doesn’t contain MY_IMAGE. This means that the embedded SQL statement is going to retrieve three column values but the externally described data structure only offers two values for storage. Because the data structure doesn’t match the SELECT statement’s field list, the RPG compiler may generate any number of errors related to data type mismatches and the like, depending on the table’s definition.

If you don’t need to access the BLOB data in the RPG program, one way around this is to simply code the data structure and the SELECT INTO by hand and omit and large object data type columns. However, if there are a large number of columns in the table, this option is unattractive.

Another option is to create a view on the table that omits the BLOB column and then reference the view in the externally described data structure. Yet another option is move the BLOB into its own table so that developers can happily continue to work with their externally defined data structures.

If the RPG program needs the BLOB data, you will have to manually define your data structure. This example only uses a 20K length BLOB, so it can be loaded into a single RPG variable (defined using the special SQLTYPE):

DdsTest           DS
D  My_Key                       10I 0
D  My_Data                      32    VARYING
D  MY_IMAGE                           SQLTYPE(VARBINARY:20480)

Technically, you don’t have to use the SQLTYPE as shown above, in reality the compiler just defines MY_IMAGE as 20480A VARYING CCSID(65535).

If your BLOB size is larger than 16MB, then you will have to use a “locator” variable to reference the BLOB data. A locator variable is just a pointer to a location within DB2’s vast world of memory management where DB2 keeps track of the large object data so that the RPG program doesn’t have to:

DdsTest           DS
D  My_Key                       10I 0
D  My_Data                      32    VARYING
D  MY_IMAGE                           SQLTYPE(BLOB_LOCATOR)

Using locators is not allowed unless the transaction isolation level is set to a value other than *NONE. The SET OPTION statement is one way to change this setting:

Exec SQL
    SET OPTION COMMIT=*CHG;

Since DB2 is “housing” the large object data on behalf of the RPG program, all operations on the BLOB need to be done using embedded SQL:

Exec SQL
    SET :IMAGE_SIZE=LENGTH(:MY_IMAGE);

In summary, that is the unattractive manner in which an RPG program can work with a large object data column up to 2GB in size.

–Michael Sansoterra

RELATED STORY

Native Regular Expressions In DB2 For i 7.1 And 7.2