LOBs with DB2 for z/OS: Stronger and Faster

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