Accessing XML or LOB data quickly by using FETCH WITH CONTINUE

FETCH WITH CONTINUE breaks XML and LOB values into manageable pieces and processes the pieces one at a time to avoid the following buffer allocation problems:

• Allocating overly large or unnecessary space for buffers. If some LOB values are shorter than the maximum length for values in a column, you can waste buffer space if you allocate enough space for the maximum length. The buffer allocation problem can be even worse for XML data because an XML column does not have a defined maximum length. If you use FETCH WITH CONTINUE, you can allocate more appropriate buffer space for the actual length of the XML and LOB values.
• Truncating very large XML and LOB data. If a very large XML or LOB value does not fit in the host variable buffer space that is provided by the application program, Db2 truncates the value. If the application program retries this fetch with a larger buffer, two problems exist. First, when using a non-scrollable cursor, you cannot re-fetch the current row without closing, reopening, and repositioning the cursor to the row that was truncated. Second, if you do not use FETCH WITH CONTINUE, Db2 does not return the actual length of the entire value to the application program. Thus, Db2 does not know how large a buffer to reallocate. If you use FETCH WITH CONTINUE, Db2 preserves the truncated portion of the data for subsequent retrieval and returns the actual length of the entire data value so that the application can reallocate a buffer of the appropriate size.

Db2 provides two methods for using FETCH WITH CONTINUE with LOB and XML data:

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