Moving data through fixed-size buffers when fetching XML and LOB data

If you use the WITH CONTINUE clause, Db2 returns information about which data does not fit in the buffer. Your application can then use repeated FETCH CURRENT CONTINUE operations to effectively stream large XML and LOB data through a fixed-size buffer, one piece at a time.

Procedure

To use fixed buffer allocation for LOB and XML data, perform the following steps:

  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 as many FETCH CONTINUE statements as necessary to process all of the data through a fixed buffer.

    After each FETCH operation, check whether a column was truncated by first examining the SQLWARN1 field in the returned SQLCA. If that field contains a 'W' value, at least one column in the returned row has been truncated. To then determine if a particular LOB or XML column was truncated, your application must compare the value that is returned in the length field with the declared length of the host variable. If a column is truncated, continue to use FETCH CONTINUE statements until all of the data has been retrieved.

    After you fetch each piece of the data, move it out of the buffer to make way for the next fetch. Your application can write the pieces to an output file or reconstruct the entire data value in a buffer above the 2-GB bar.

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 C2 contains 10 MB of data.

Now, suppose that you declare a 32 KB section CLOBHV: 
EXEC SQL BEGIN DECLARE SECTION
  DECLARE CLOBHV SQL TYPE IS CLOB(32767);
EXEC SQL END DECLARE SECTION.
Next, suppose that you use the following statements to declare and open CURSOR1 and to FETCH WITH CONTINUE: 
EXEC SQL DECLARE CURSOR1 CURSOR FOR SELECT C2 FROM T1;
EXEC SQL OPEN CURSOR1;
EXEC SQL FETCH WITH CONTINUE CURSOR1 INTO :CLOBHV;

As each piece of the data value is fetched, move it from the buffer to the output file.

Because the 10 MB value in C2 does not fit into the 32 KB buffer, some of the data is truncated. Your application can loop through the following FETCH CURRENT CONTINUE: 
EXEC SQL FETCH CURRENT CONTINUE CURSOR1 INTO :CLOBHV;

After each FETCH operation, you can determine if the data was truncated by first checking if the SQLWARN1 field in the returned SQLCA contains a 'W' value. If so, then check if the length value, which is returned in CLOBHV_LENGTH, is greater than the declared length of 32767. (CLOBHV_LENGTH is declared as part of the precompiler expansion of the CLOBHV declaration.) If the value is greater, that value has been truncated and more data can be retrieved with the next FETCH CONTINUE operation.

When all of the data has moved to the output file, you can close the cursor: 
EXEC SQL CLOSE CURSOR1;