Performance Features in DB2 V9 for z/OS – Part 6

As I talked about in the previous weeks, DB2 Version 9 for z/OS has over 50+ great new performance features. Many of these features can really benefit your standard DBA operations and improve application performance and overall availability of your systems and data.

FETCH WITH CONTINUE SQL

Another one of the enhancements with DB2 Version 9 for z/OS helps with the expanding role of XML within our systems.  The enhancement is the FETCH WITH CONTINUE SQL key words for assisting with XML large data type cursor processing.

This new phrase provides the ability to continue processing the same data row containing XML, CLOB, or any unstructured data type.  Through this new capability of repeatedly retrieving data from a cursor positioned on a row with one or more large data types, the processing can be better buffered and tuned to sizes of these large data types.

For example if there are multiple XML or CLOB columns retrieved, the cursor processing does not usually know the exact size of these unstructured data types.  The XML data sizes can vary tremendously, especially pictures; one picture may be 2 megabytes and a new picture can now be 10 megabytes. Since the process doesn’t know the size, allocating the correct receiving data size is difficult.  Allocate too small and the data is truncated; allocate too large an area and there is wasted space.

The FETCH WITH CONTINUE phrase allows the process to retrieve the current picture and then evaluate the SQLCA SQLWARN1 and see if it contains a “W”.  The W indicates or warns that the data has been truncated and the more space is needed to be allocated for the current picture.  Over allocating 10 megabytes for every picture will quickly cause the process to run out of memory and abend.  So allocating just what is needed is very important and FETCH WITH CONTINUE is the key to processing these large XML, CLOB and other unstructured data types.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>