LOB host variable, LOB locator, and LOB file reference variable declarations

When you write applications to manipulate LOB data, you need to declare host variables to hold the LOB data or LOB locator. Alternatively, you need to declare LOB file reference variables to point to the LOB data.

You can declare LOB host variables and LOB locators in assembler, C, C++, COBOL, Fortran, and PL/I. Additionally, you can declare LOB file reference variables in assembler, C, C++, COBOL, and PL/I. For each host variable, locator, or file reference variable of SQL type BLOB, CLOB, or DBCLOB that you declare, DB2® generates an equivalent declaration that uses host language data types. When you refer to a LOB host variable, LOB locator, or LOB file reference variable in an SQL statement, you must use the variable that you specified in the SQL type declaration. When you refer to the host variable in a host language statement, you must use the variable that DB2 generates.

DB2 supports host variable declarations for LOBs with lengths of up to 2 GB - 1. However, the size of a LOB host variable is limited by the restrictions of the host language and the amount of storage available to the program.

Declare LOB host variables that are referenced by the precompiler in SQL statements by using the SQL TYPE IS BLOB, SQL TYPE IS CLOB, or SQL TYPE IS DBCLOB keywords.

LOB host variables that are referenced only by an SQL statement that uses a DESCRIPTOR should use the same form as declared by the precompiler. In this form, the LOB host-variable-array consists of a 31-bit length, followed by the data, followed by another 31-bit length, followed by the data, and so on. The 31-bit length must be fullword aligned.

Example: Suppose that you want to allocate a LOB array of 10 elements, each with a length of 5 bytes. You need to allocate the following bytes for each element, for a total of 120 bytes:
  • 4 bytes for the 31-bit integer
  • 5 bytes for the data
  • 3 bytes to force fullword alignment

The following examples show you how to declare LOB host variables in each supported language. In each table, the left column contains the declaration that you code in your application program. The right column contains the declaration that DB2 generates.

Declarations of LOB host variables in assembler

The following table shows assembler language declarations for some typical LOB types.

Table 1. Example of assembler LOB variable declarations
You declare this variable DB2 generates this variable
clob_var SQL TYPE IS CLOB 40000K 
clob_var DS 0FL4
clob_var_length DS FL4
clob_var_data DS CL655351
ORG clob_var_data +(40960000-65535)
dbclob_var SQL TYPE IS DBCLOB 4000K 
dbclob_var DS 0FL4
dbclob_var_length DS FL4
dbclob_var_data DS GL655342
ORG dbclob_var_data+(8192000-65534)
blob_var SQL TYPE IS BLOB 1M 
blob_var DS 0FL4
blob_var_length DS FL4
blob_var_data DS CL655351
ORG blob_var_data+(1048476-65535)
clob_loc SQL TYPE IS CLOB_LOCATOR clob_loc DS FL4
dbclob_loc SQL TYPE IS DBCLOB_LOCATOR dbclob_loc DS FL4
blob_loc SQL TYPE IS BLOB_LOCATOR blob_loc DS FL4
clob_file SQL TYPE IS CLOB_FILE clob_file DS FL4
dbclob_file SQL TYPE IS DBCLOB_FILE dbclob_file DS FL4
blob_file SQL TYPE IS BLOB_FILE blob_file DS FL4
Notes:
  1. Because assembler language allows character declarations of no more than 65535 bytes, DB2 separates the host language declarations for BLOB and CLOB host variables that are longer than 65535 bytes into two parts.
  2. Because assembler language allows graphic declarations of no more than 65534 bytes, DB2 separates the host language declarations for DBCLOB host variables that are longer than 65534 bytes into two parts.

Declarations of LOB host variables in C

The following table shows C and C++ language declarations for some typical LOB types.

Table 2. Examples of C language variable declarations
You declare this variable DB2 generates this variable
SQL TYPE IS BLOB (1M) blob_var; 
struct {
   unsigned long length;
   char data[1048576];
} blob_var;
SQL TYPE IS CLOB(400K) clob_var; 
struct {
   unsigned long length;
   char data[409600];
} clob_var;
SQL TYPE IS DBCLOB (4000K) dbclob_var; 
struct {
   unsigned long length;
   sqldbchar data[4096000];
} dbclob_var;
SQL TYPE IS BLOB_LOCATOR blob_loc; unsigned long blob_loc;
SQL TYPE IS CLOB_LOCATOR clob_loc; unsigned long clob_loc;
SQL TYPE IS DBCLOB_LOCATOR dbclob_loc; unsigned long dbclob_loc;
SQL TYPE IS BLOB_FILE FBLOBhv; 
#pragma pack(full) 
struct {                         
unsigned long name_length;       
unsigned long data_length;       
unsigned long file_options;      
char name??(255??);              
} FBLOBhv        ;   
#pragma pack(reset)
SQL TYPE IS CLOB_FILE FCLOBhv; 
#pragma pack(full) 
struct {                         
unsigned long name_length;       
unsigned long data_length;       
unsigned long file_options;      
char name??(255??);              
} FCLOBhv        ;   
#pragma pack(reset)
SQL TYPE IS DBCLOB_FILE FDBCLOBhv; 
#pragma pack(full) 
struct {                         
unsigned long name_length;       
unsigned long data_length;       
unsigned long file_options;      
char name??(255??);              
} FDBCLOBhv        ;   
#pragma pack(reset)

Declarations of LOB host variables in COBOL

The declarations that are generated for COBOL depend on whether you use the DB2 precompiler or the DB2 coprocessor. The following table shows COBOL declarations that the DB2 precompiler generates for some typical LOB types. The declarations that the DB2 coprocessor generates might be different.

Start of change
Table 3. Examples of COBOL variable declarations by the DB2 precompiler
You declare this variable DB2 precompiler generates this variable 
01  BLOB-VAR
    SQL TYPE IS BLOB(1M).
 
01  BLOB-VAR.
    49 BLOB-VAR-LENGTH PIC S9(9) COMP-5.
    49 BLOB-VAR-DATA PIC X(1048576).
 
01  CLOB-VAR
    SQL TYPE IS CLOB(40000K).
 
01  CLOB-VAR.
    49 CLOB-VAR-LENGTH PIC S9(9) COMP-5.
    49 CLOB-VAR-DATA PIC X(40960000).
 
01  DBCLOB-VAR
    SQL TYPE IS DBCLOB(4000K).
 
01  DBCLOB-VAR.
    49 DBCLOB-VAR-LENGTH PIC S9(9) COMP-5
    49 DBCLOB-VAR-DATA PIC G(40960000)
    DISPLAY-1.
 
01  BLOB-LOC
    SQL TYPE IS BLOB-LOCATOR.
 
01  BLOB-LOC PIC S9(9) COMP-5.
 
01  CLOB-LOC
    SQLTYPE IS CLOB-LOCATOR.
 
01  CLOB-LOC PIC S9(9) COMP-5.
 
01  DBCLOB-LOC
    SQLTYPE IS DBCLOB-LOCATOR.
 
01  DBCLOB-LOC PIC S9(9) COMP-5.
 
01  BLOB-FILE
    SQLTYPE IS BLOB-FILE.
 
01  BLOB-FILE.                              
  49 BLOB-FILE-NAME-LENGTH PIC S9(9) COMP-5 SYNC.
  49 BLOB-FILE-DATA-LENGTH PIC S9(9) COMP-5.
  49 BLOB-FILE-FILE-OPTION PIC S9(9) COMP-5.
  49 BLOB-FILE-NAME PIC X(255) .
 
01  CLOB-FILE
    SQLTYPE IS CLOB-FILE.
 
01  CLOB-FILE.                              
  49 CLOB-FILE-NAME-LENGTH PIC S9(9) COMP-5 SYNC.
  49 CLOB-FILE-DATA-LENGTH PIC S9(9) COMP-5.
  49 CLOB-FILE-FILE-OPTION PIC S9(9) COMP-5.
  49 CLOB-FILE-NAME PIC X(255) .
 
01  DBCLOB-FILE
    SQLTYPE IS DBCLOB-FILE.
 
01  DBCLOB-FILE.                              
  49 DBCLOB-FILE-NAME-LENGTH PIC S9(9) COMP-5 SYNC.
  49 DBCLOB-FILE-DATA-LENGTH PIC S9(9) COMP-5.
  49 DBCLOB-FILE-FILE-OPTION PIC S9(9) COMP-5.
  49 DBCLOB-FILE-NAME PIC X(255) .
End of change

Declarations of LOB host variables in Fortran

The following table shows Fortran declarations for some typical LOB types.

Table 4. Examples of Fortran variable declarations
You declare this variable DB2 generates this variable 
SQL TYPE IS BLOB(1M) blob_var
 
CHARACTER blob_var(1048580)
INTEGER*4 blob_var_LENGTH
CHARACTER blob_var_DATA
EQUIVALENCE( blob_var(1),
+            blob_var_LENGTH )
EQUIVALENCE( blob_var(5),
+            blob_var_DATA )
 
SQL TYPE IS CLOB(40000K) clob_var
 
CHARACTER clob_var(4096004)
INTEGER*4 clob_var_length
CHARACTER clob_var_data
EQUIVALENCE( clob_var(1),
+            clob_var_length )
EQUIVALENCE( clob_var(5),
+            clob_var_data )
 
SQL TYPE IS BLOB_LOCATOR blob_loc
 
INTEGER*4 blob_loc
 
SQL TYPE IS CLOB_LOCATOR clob_loc
 
INTEGER*4 clob_loc

Declarations of LOB host variables in PL/I

The declarations that are generated for PL/I depend on whether you use the DB2 precompiler or the DB2 coprocessor. The following table shows PL/I declarations that the DB2 precompiler generates for some typical LOB types. The declarations that the DB2 coprocessor generates might be different.

Table 5. Examples of PL/I variable declarations by the DB2 precompiler
You declare this variable DB2 precompiler generates this variable 
DCL BLOB_VAR
  SQL TYPE IS BLOB (1M);
 
DCL 1 BLOB_VAR,
  2 BLOB_VAR_LENGTH FIXED BINARY(31),
  2 BLOB_VAR_DATA,1
    3 BLOB_VAR_DATA1(32)
        CHARACTER(32767),
    3 BLOB_VAR_DATA2
        CHARACTER(1048576-32*32767);
 
DCL CLOB_VAR
  SQL TYPE IS CLOB (40000K);
 
DCL 1 CLOB_VAR,
  2 CLOB_VAR_LENGTH FIXED BINARY(31),
  2 CLOB_VAR_DATA,1
    3 CLOB_VAR_DATA1(1250)
        CHARACTER(32767),
    3 CLOB_VAR_DATA2
        CHARACTER(40960000-1250*32767);
 
DCL DBCLOB_VAR
  SQL TYPE IS DBCLOB (4000K);
 
DCL 1 DBCLOB_VAR,
  2 DBCLOB_VAR_LENGTH FIXED BINARY(31),
  2 DBCLOB_VAR_DATA,2
    3 DBCLOB_VAR_DATA1(250)
        GRAPHIC(16383),
    3 DBCLOB_VAR_DATA2
        GRAPHIC(4096000-250*16383);
 
DCL blob_loc
  SQL TYPE IS BLOB_LOCATOR;
 
DCL blob_loc FIXED BINARY(31);
 
DCL clob_loc
  SQL TYPE IS CLOB_LOCATOR;
 
DCL clob_loc FIXED BINARY(31);
 
DCL dbclob_loc SQL TYPE IS 
  DBCLOB_LOCATOR;
 
DCL dbclob_loc FIXED BINARY(31);
 
DCL blob_file
  SQL TYPE IS BLOB_FILE;
 
DCL  1  blob_file,                                           
      2   blob_file_NAME_LENGTH BIN FIXED(31) 
ALIGNED,      
      2   blob_file_DATA_LENGTH BIN FIXED(31),              
      2   blob_file_FILE_OPTIONS BIN FIXED(31),             
      2   blob_file_NAME CHAR(255) ; 
DCL clob_file
  SQL TYPE IS CLOB_FILE;
 
DCL  1  clob_file,                                           
      2   clob_file_NAME_LENGTH BIN FIXED(31) 
ALIGNED,      
      2   clob_file_DATA_LENGTH BIN FIXED(31),              
      2   clob_file_FILE_OPTIONS BIN FIXED(31),             
      2   clob_file_NAME CHAR(255) ;
 
DCL dbclob_file SQL TYPE IS 
  DBCLOB_FILE;
 
DCL  1  dbclob_file,                                           
      2   dbclob_file_NAME_LENGTH BIN FIXED(31) 
ALIGNED,      
      2   dbclob_file_DATA_LENGTH BIN FIXED(31),              
      2   dbclob_file_FILE_OPTIONS BIN FIXED(31),             
      2   dbclob_file_NAME CHAR(255) ;
Notes:
  1. For BLOB or CLOB host variables that are greater than 32767 bytes in length, DB2 creates PL/I host language declarations in the following way:
    • If the length of the LOB is greater than 32767 bytes and evenly divisible by 32767, DB2 creates an array of 32767-byte strings. The dimension of the array is length/32767.
    • If the length of the LOB is greater than 32767 bytes but not evenly divisible by 32767, DB2 creates two declarations: The first is an array of 32767 byte strings, where the dimension of the array, n, is length/32767. The second is a character string of length length-n*32767.
  2. For DBCLOB host variables that are greater than 16383 double-byte characters in length, DB2 creates PL/I host language declarations in the following way:
    • If the length of the LOB is greater than 16383 characters and evenly divisible by 16383, DB2 creates an array of 16383-character strings. The dimension of the array is length/16383.
    • If the length of the LOB is greater than 16383 characters but not evenly divisible by 16383, DB2 creates two declarations: The first is an array of 16383 byte strings, where the dimension of the array, m, is length/16383. The second is a character string of length length-m*16383.