Using CLOBS on the iSeries in v5r4
CLOBS and BLOBS
The term Large Object and the generic acronym LOB refer to BLOB, and CLOB data types. Large object support was added to the IBM iSeries in v5r4. Large objects types can be defined within RPG programs using program source type SQLRPGLE. Large objects cannot be defined in DDS defined physical files. You have to use the DDL (SLQ) "CREATE TABLE" command.
Character string limits in RPG, iSeries v5r4
| String Type | Description | Maximum Length (in Bytes) |
|---|---|---|
| char | character in DB2 file | 32,766 |
| char | character in RPG program | 65,535 |
Maximum record length
The maximum length of a single byte not NULL capable character field in a physical file (or SQL table) is 32766 Bytes. If you try to exceed 32766 characters in a file, you will get error: "SQL statement too long or complex."
Example
CREATE TABLE MYLIB/MYFILE (MYCHAR1 CHAR (32766) NOT NULL WITH DEFAULT, MYCHAR2 CHAR (1 ) NOT NULL WITH DEFAULT) SQL statement too long or complex.
To overcome this limit, use CLOB (Character Large Objects) or DBLOB (Double Byte Large Object). The maximum length of a LOB is 2 Gig.
Field size limits in iSeries v5r4
| String Type | Description | Maximum Length (in Bytes) |
|---|---|---|
| CLOB | Character Large Object String | 15,728,640 |
| DBCLOB | Double Byte Character Large Object String | 7,864,320 |
| BLOB | Binary Large Object String | 15,728,640 |
Note that DB2 database will only allocate only the size of the data that you write. Also note that these files should be journalled.
Maximum record length
The maximum length of a single byte not NULL capable character field in a physical file (or SQL table) is 32766 Bytes. If you try to exceed 32766 characters in a file, you will get error: "SQL statement too long or complex."
Example
CREATE TABLE MYLIB/MYFILE (MYCHAR1 CHAR (32766) NOT NULL WITH DEFAULT, MYCHAR2 CHAR (1 ) NOT NULL WITH DEFAULT) SQL statement too long or complex.
To overcome this limit, use CLOB (Character Large Objects) or DBLOB (Double Byte Large Object) fields.
Using Large Objects in RPG programs
To use Large Objects in RPG, program source must be defines as type SQLRPGLE. To define Large Objects in RPG, use SQLTYPE keyword as in examples below.
* Large Object Definitions D myCLOB S SQLTYPE(CLOB:500) D myDBCLOB S SQLTYPE(DBCLOB:500) D myBLOB S SQLTYPE(BLOB:500)
In the example above, the SQL pre-processor will automatically create variables that correspond to the string content (_DATA) and length of the string (_LEN) in your CLOB. Your compiled listing will show a data structure like this:
DMYCLOB DS DMYCLOB_LEN 10U 0 DMYCLOB_DATA 500A
To create an SQL table containing CLOBS and BLOBS
CREATE TABLE MYLIB/MYFILE (MYDEC DEC (5) NOT NULL WITH DEFAULT, MYCHAR CHAR (30) NOT NULL WITH DEFAULT, MYCLOB CLOB (65531) ALLOCATE(1000) NOT NULL WITH DEFAULT, MYCLOB2 CLOB (65531) NOT NULL WITH DEFAULT, MYCLOB3 CLOB (65531) NOT NULL WITH DEFAULT, MYCBLOB BLOB (65531) ALLOCATE(1000) NOT NULL WITH DEFAULT)
The ALLOCATE parameter indicates number of characters generally contained in the specified column. This parameter helps the database manager to obtain the best performance for the normal use of this column. It does not limit you do this number. In the above example, we created a CLOB with a maximum of 65,531 bytes, where up to 15,728,640 bytes was possible.
Write Clob field from RPG to database
Here is program that writes data to a CLOB field in a DDL physical file based on a value in program memory.
* ---------------------------------------------------------------------- *
* PROGRAM: CLOBTEST1 *
* DESCRIPTION: Demonstrates how to write data to a *
* CLOB (Character Large Object) *
* *
* ---------------------------------------------------------------------- *
D i S 10i 0
D wkValue S 65531a varying
D wkClob S sqltype(CLOB:65531)
D* use external file definition to define fields MyDec and MyChar:
D File_Dta E DS EXTNAME(MYFILE) INZ
D* Note that variables wkClob_data and wkClob_len
D* get create automatocally by SQL pre-processor.
/free
// this will produce a 54,00 character string
For i = 1 to 2000 ;
Eval wkValue = %trim(wkvalue) + ' '+ %char(%TimeStamp());
Endfor;
// the trick is to assign wkValue to wkClob_data
Eval wkClob_data = wkValue;
Eval wkClob_len = %len(%trim(wkValue));
Exec SQL set option commit = *none;
Exec SQL
INSERT INTO MYFILE (MYDEC, MYCHAR, MYCLOB, MYCLOB2, MYCLOB3)
VALUES (123, 'Some Description',:wkClob,:wkClob,:wkClob);
If %subst(sqlstt:1:2) <> '00' ;
// an error occurred.
Endif;
Eval *INLR = *on;
Return;
/end-free
SQLTYPE variables are a little unusual. When you define an SQLTYPE variable, program will create a special data structure. Basically, it takes the name you define and then appends a suffix to identify the subfields. In the case above, the declaration of SQLTYPE MyClob generates two variables, MyClob_Data and MyClob_Len. It is the program's responsibility to fill those generated fields with the CLOB data and the length. The first four characters of the CLOB are reserved for the length of the CLOB, so it is safer to write to "the _data" field
So in above example, the SQL pre-processor will create the following data structure automatically.
WKCLOB DS WKCLOB_LEN 10U 0 WKCLOB_DATA 65531A
WKCLOB_Data keeps the data part of the CLOB data type. The other is WKCLOB_LEN keeps the length of data in MYCLOB01_Data. I can assign value to WKCLOB, but it is better to assign to WKCLOB_Len and WKCLOB_Data.
If you place a string to long in clob, you will get this error message: 22501 Length in a varying-length or LOB host variable not valid.
When we look at the data using as400 SQL or Query/400, we see only pointer keyword.
However using iSeries Navigator or other ODBC connection, we are able to see data and even concatenate data without error!
Read Clob field from database to RPG
* ----------------------------------------------------------------------
* PROGRAM: CLOBTEST2
* DESCRIPTION: Demonstrates how to read CLOB (Character Large Object)
* from a database file back into memory variable.
* ----------------------------------------------------------------------
D wkValue S 65531a varying
D wkClob S sqltype(CLOB:65531)
D* use external file definition to define fields MyDec and MyChar:
D File_Dta E DS EXTNAME(MYFILE) INZ
/free
Exec SQL
SELECT MYDEC, MYCHAR, MYCLOB
INTO :MYDEC, :MYCHAR, :wkClob
FROM MYFILE
WHERE MYDEC = 123
FETCH FIRST 1 ROWS ONLY;
wkValue = %subst(wkClob_data:1:wkClob_len);
If %subst(sqlstt:1:2) <> '00';
// an error occurred.
Endif;
// ...
Eval *INLR = *on;
Return;
/end-free