Chapter 4. Using LOBs 121
The precompiler-generated code does not use the SQLDATALEN field, so the required
buffer length is reported (in bytes for CLOB and BLOB, in characters for DBCLOB) in
the first 4 bytes of the buffer pointed at by the SQLDATA field in the base SQLVAR. The
required buffer length is the length of buffer space required to hold the entire data
value, therefore, it includes the amount of data already written to the data buffer.
5. The application checks for a successful fetch and then enters a loop in which it writes the
buffer contents out to an external file, then checks if truncation occurred. To check for
truncation, the application first checks the SQLWARN1 field to see if it is set to 'W'. If so,
that means that at least one column was truncated. To check each column, the application
must compare the length returned in the first 4 bytes of the output data with the length of
the buffer that it provided (this is still set in SQLLONGLEN). If there was truncation, it
executes the FETCH CURRENT CONTINUE statement to get the next piece of data. This
is repeated until the LOB column is completely fetched. The check for truncation involves
comparing the integer value in the first 4 bytes of the data buffer with the length of the
input host variable.
6. When doing the FETCH CURRENT CONTINUE, the application uses a direct host
variable reference in the INTO clause. If there had been other host variables in the original
SELECT list, those would have had to have been specified in the INTO clause as well.
To process the FETCH CURRENT CONTINUE statement, DB2 writes data to the output
host variables in the same way that FETCH does, but beginning at the truncation point.
DB2 only writes out data for LOB or XML columns that were previously truncated. Other
columns are ignored. The application processes the returned data in the data buffers. In
this case, the application allocated the required sizes for the FETCH CURRENT
CONTINUE to be successful. However, if the LOB data buffer is still too small, DB2 would
again set the truncation warnings and lengths as described on the FETCH step. One
difference is that the length returned in the first 4 bytes on the FETCH CURRENT
CONTINUE statement is equal to the length of the data from the truncation point to the
end.
7. After the loop, the application closes the cursor. If there had been truncated columns with
unfetched data remaining, the unfetched data would have been discarded.
4.6.3 Finding the n
th
occurrence of a string
Since the POSSTR function provides you with the ability to find the first occurrence of a
string, you probably want to find the second or third position of your search string. DB2 allows
you to use POSSTR function to succeed anyway, but you have to combine it with SUBSTR,
because with both functions, you are able to quickly find the position you need. Except that
from the application’s point of view, it is a bit more difficult than finding the first position.
Example 4-20 provides a possible solution for searching a LOB value for the position of a
search string you really need.
Example 4-20 Finding a specific occurrence of a string
EXEC SQL
SET :POS = POSSTR (:LOB-LOCATOR, :SEARCH-STRING)
END-EXEC
[determine if correct position is returned]
IF WRONG-POSITION THEN
MOVE 0 TO FINAL-POS
START-POS
PERFORM UNTIL CORRECT-POSITION
ADD POS TO FINAL-POS

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.