Chapter 4. Using LOBs 69
In a host application, starting with DB2 9, you can use a file reference variable of type
BLOB_FILE, CLOB_FILE, or DBCLOB_FILE to insert a LOB from a file into a DB2 table or to
select a LOB from a DB2 table into a file. When you use a file reference variable, you can
select or insert an entire LOB value without contiguous application storage to contain the
entire LOB. In other words, LOB file reference variables move LOB values from the database
server to an application or from an application to the database server without going through
the applications memory.
Furthermore, LOB file reference variables bypass the host language limitation on the
maximum size allowed for dynamic storage to contain a LOB value. You can declare a LOB
file reference variable or a LOB file reference array for applications that are written in C,
COBOL, PL/I, and Assembler. The LOB file reference variables do not contain LOB data; they
represent a file that contains LOB data. Database queries, updates, and inserts can use file
reference variables to store or retrieve column values. As with other host variables, a LOB file
reference variable can have an associated indicator variable.
4.1.2 Use of a double or triple SQLDA in dynamic SQL
If you write a dynamic SQL program, and you want to be able to retrieve LOB values using a
SQLDA, you must use a double SQLDA or triple SQLDA.
The SQLDA is a collection of variables that is required for the execution of the SQL
DESCRIBE statement, and can be optionally used by the PREPARE, OPEN, FETCH,
EXECUTE, and CALL statements. The meaning of the information in an SQLDA depends on
the context in which it is used. For DESCRIBE and PREPARE INTO, DB2 sets fields in the
SQLDA to provide information about the columns of the result set or table to the application
program. For OPEN, EXECUTE, FETCH, and CALL, the application program must set the
fields in the SQLDA to provide DB2 with information about the host variables of the program.
An SQLDA can contain a variable number of occurrences of SQLVAR, each of which is a set
of fields that describes one column in the result table of a SELECT statement. If your program
wants to be able to retrieve n columns, you should at least allocate 2*n SQLVARS if you want
to be able to retrieve LOB columns (double SQLDA) and 3*n SQLVARS (triple SQLDA) if you
want to be able to retrieve LOB columns and have both column names and column labels in
your SQLDA (by using the USING BOTH option in your PREPARE or DESCRIBE statement).
The base SQLVAR contains the following variables when set by DB2 during DESCRIBE or
PREPARE:
򐂰 SQLTYPE: Indicates the data type of the column and whether it can contain null values
򐂰 SQLLEN: The length attribute of the column
򐂰 SQLDATA: The CCSID of the column
򐂰 SQLIND: Reserved
򐂰 SQLNAME: The name of the column
For LOBs, the following SQLTYPES are returned in the base SQLVAR of the SQLDA as
shown in Table 4-1 on page 70. An even value of SQLTYPE means the column does not allow
nulls, and an odd value means the column does allow nulls. For LOB columns, the SQLLEN
field is always zero (only half word) and the actual length of the LOB columns can be found in
the SQLLONGLEN field of the extended SQLVAR.

Get LOBs with DB2 for z/OS: Stronger and Faster now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.