LOBS, CLOBS, and BLOBS can be as large as 2,147,483,647 bytes long (2 Gigabytes). Double Byte CLOBS can be 1,073,741,823 characters long (1 Gigabyte).
For example, consider the following declare:
DCL my-identifier-name SQL TYPE IS lob-type-name (length);
The SQL preprocessor would transform the declare into one of these structures, depending on the setting of the LOB() precompiler option. When LOB(DB2) is specified and the LOB size is 32767 bytes or less, the generated structure will look like this:
DCL
/*$*$*$
SQL TYPE IS lob-type-name (length)
$*$*$*/
1 my-identifier-name,
3 my-identifier-name_LENGTH FIXED BIN(31),
3 my-identifier-name_DATA CHAR(size1);When LOB(DB2) is specified and the size is greater than 32767 bytes, the generated structure will look like this:
DCL
/*$*$*$
SQL TYPE IS lob-type-name (length)
$*$*$*/
1 my-identifier-name,
3 my-identifier-name_LENGTH FIXED BIN(31),
3 my-identifier-name_DATA,
4 my-identifier-name_DATA1(size1) CHAR(32767),
4 my-identifier-name_DATA2 CHAR(size2);
When LOB(PLI) is specified and the LOB size is 32767 bytes or less, the generated structure will look like this:
DEFINE STRUCTURE
1 lob-type$$x,
2 my-identifier-name_LENGTH FIXED BIN(31),
2 my-identifier-name_DATA,
3 my-identifier-name_DATA1 CHAR(size1);
DCL my-identifier-name TYPE lob-type$$x;When LOB(PLI) is specified and the LOB size is greater than 32767 bytes, the generated structure will look like this:
DEFINE STRUCTURE
1 lob-type$$x,
2 my-identifier-name_LENGTH FIXED BIN(31),
2 my-identifier-name_DATA,
3 my-identifier-name_DATA1(size1) CHAR(32767),
3 my-identifier-name_DATA2 CHAR(size2),
DCL my-identifier-name TYPE lob-type$$x;In these structures, my-identifier-name is the name of your PL/I host identifier and lob-type$$x is a name generated by the preprocessor. size1 is an integer value that is the truncated value of length/32767. size2 is the remainder of length/32767.
For DBCLOB data types, the generated structure is a little different. Since double-byte characters are two bytes each, size1 is an integer value that is the truncated value of length/16383 and size2 is the remainder of length/16383.
For example, consider the following declare:
DCL my-identifier-name SQL TYPE IS lob-type_LOCATOR;
The SQL preprocessor would transform the declare into one of these structures, depending on the setting of the LOB() precompiler option. When LOB(DB2) is specified the generated code will look like this:
DCL /*$*$*$ SQL TYPE IS lob-type_LOCATOR $*$*$*/ my-identifier-name FIXED BIN(31);
When LOB(PLI) is specified the SQL preprocessor would transform this declare into the following code:
DEFINE ALIAS lob-type_LOCATOR FIXED BIN(31); Dcl my-identifier-name TYPE lob-type_LOCATOR;
In this case, my-identifier-name is your PL/I host identifier and lob-type_LOCATOR is a name generated by the preprocessor consisting of the LOB type and the string LOCATOR.
The following examples provide sample PL/I variable declarations and their corresponding transformations for LOB support. All of the examples are compiled with the default LOB(DB2) precompiler option.
DCL my_blob SQL TYPE IS BLOB(2000);
After transform:
DCL
/*$*$*$
SQL TYPE IS BLOB(2000)
$*$*$*/
1 MY_BLOB,
3 MY_BLOB_LENGTH FIXED BIN(31),
3 MY_BLOB_DATA CHAR(2000);
DCL my_dbclob SQL TYPE IS DBCLOB(4000K);
After transform:
DCL
/*$*$*$
SQL TYPE IS DBCLOB(4000K)
$*$*$*/
1 MY_DBCLOB,
3 MY_DBCLOB_LENGTH FIXED BIN(31),
3 MY_DBCLOB_DATA,
4 MY_DBCLOB_DATA1(250) GRAPHIC(16383),
4 MY_DBCLOB_DATA2 GRAPHIC(250);
DCL my_clob_locator SQL TYPE IS CLOB_LOCATOR;
After transform:
DEFINE ALIAS CLOB_LOCATOR FIXED BIN(31); DCL my_clob_locator TYPE CLOB_LOCATOR;