LOBs with DB2 for z/OS: Stronger and Faster

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 application’s 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.