Dynamically allocating buffers when fetching XML and LOB data

If you specify FETCH WITH CONTINUE, Db2 returns information about which data does not fit in the buffer. Your application can then use the information about the truncated data to allocate an appropriate target buffer and execute a fetch operation with the CURRENT CONTINUE clause to retrieve the remaining data.

Procedure

To use dynamic buffer allocation for LOB and XML data:

  1. Use an initial FETCH WITH CONTINUE to fetch data into a pre-allocated buffer of a moderate size.
  2. If the value is too large to fit in the buffer, use the length information that is returned by Db2 to allocate the appropriate amount of storage.
  3. Use a single FETCH CURRENT CONTINUE statement to retrieve the remainder of the data.

Example

Suppose that table T1 was created with the following statement: 
CREATE TABLE T1 (C1 INT, C2 CLOB(100M), C3 CLOB(32K), C4 XML);
A row exists in T1 where C1 contains a valid integer, C2 contains 10MB of data, C3 contains 32KB of data, and C4 contains 4MB of data.
Now, suppose that you declare CURSOR1, prepare and describe statement DYNSQLSTMT1 with descriptor sqlda, and open CURSOR1 with the following statements: 
EXEC SQL DECLARE CURSOR1 CURSOR FOR DYNSQLSTMT1;
EXEC SQL PREPARE DYNSQLSTMT1 FROM 'SELECT * FROM T1';
EXEC SQL DESCRIBE DYNSQLSTMT1 INTO DESCRIPTOR :SQLDA;
EXEC SQL OPEN CURSOR1;
Next, suppose that you allocate moderately sized buffers (32 KB for each CLOB or XML column) and set data pointers and lengths in SQLDA. Then, you use the following FETCH WITH CONTINUE statement: 
EXEC SQL FETCH WITH CONTINUE CURSOR1 INTO DESCRIPTOR :SQLDA;
Because C2 and C4 contain data that do not fit in the buffer, some of the data is truncated. Your application can use the information that Db2 returns to allocate large enough buffers for the remaining data and reset the data pointers and length fields in SQLDA. At that point, you can resume the fetch and complete the process with the following FETCH CURRENT CONTINUE statement and CLOSE CURSOR statement: 
EXEC SQL FETCH CURRENT CONTINUE CURSOR1 INTO DESCRIPTOR :SQLDA;
EXEC SQL CLOSE CURSOR1;

The application needs to concatenate the two returned pieces of the data value. One technique is to move the first piece of data to the dynamically-allocated larger buffer before the FETCH CONTINUE. Set the SQLDATA pointer in the SQLDA structure to point immediately after the last byte of this truncated value. Db2 then writes the remaining data to this location and thus completes the concatenation.