138 LOBs with DB2 for z/OS: Stronger and Faster
sqlccrecv( ulBytes - 92 ) - rc - 0, time elapsed - +2.895000E-003
( Row=1, iPar=2, fCType=SQL_C_LONG, rgbValue=79057 )
( Row=1, iPar=1, fCType=SQL_C_BLOB_LOCATOR, rgbValue=BD09179B )
( Row=1, iPar=2, fCType=SQL_C_LONG, rgbValue=1 )
( Row=1, iPar=3, fCType=SQL_C_LONG, rgbValue=9 )
sqlccsend( Handle - 0000004609406048 )
sqlccsend( ulBytes - 148 )
sqlccsend( ) rc - 0, time elasped - +8.000000E-006
sqlccrecv( ulBytes - 111 ) - rc - 0, time elapsed - +1.802000E-003
( Row=1, iPar=4, fCType=SQL_C_BINARY, rgbValue=x'FFA42B0400121F9D02', pcbValue=9,
These APIs can only be used with the new MERGE statement available with DB2 9 for z/OS.
In DB2 V8, the MERGE statement has to be emulated by a series of Update and Insert
statements. The SQLPutData is not suited for this, because the data has to be sent prior to
the insert and update.
Progressive streaming with DB2 Connect CLI
With DB2 Connect 9.1, the SQLGetData API implicitly uses progressive references against
DB2 9 for z/OS. The default setting is that any LOB smaller than 1 MB is buffered back to the
client during the fetch; anything larger uses the progressive references. This value is
controlled with the LobCacheSize keyword or the SQL_ATTR_LOB_CACHE_SIZE
connection or statement attribute. See also “Progressive streaming with the Java Universal
Driver” on page 143 and 4.3, “DRDA LOB flow optimization” on page 81 for detailed
explanations about progressive streaming.
With progressive streaming, we expect the CLI streaming interface to be as fast as or better
than the currently used interface. Once this is implemented and lib_dbsl has switched to CLI
streaming, the interface will be considerably less complex and easier to maintain.
5.4 Optimization techniques and query rewrite
Many problems with the performance of LOBs result from the fact that most applications or
application developers regard LOBs not so much as large objects but rather as objects with
unknown size. With LOB objects, the responsibility to handle the size of an object is shifted
from the application code to the database. In particular, this is the case in the Java world
where the Java String object is treated in the persistency layer as a CLOB. Obviously, there is
a price to pay for this commodity in the form of performance. Ideally, the performance of small
LOBs should not differ from that of a VARCHAR type field. This can be achieved by some
programming techniques explained below. Some of these techniques have been
implemented in DB2, and they are also discussed in this section.
5.4.1 Local LOB buffer
The SAP database interface can retrieve and write LOB data in several pieces or in one
piece. Typically, very large LOBs should be retrieved and written piece-wise (streaming). For
every piece-wise operation, a locator statement is executed, causing a network flow. To avoid
the execution of locator statements for small pieces (typically less than 64 KB), these pieces
are buffered in a local LOB buffer.
When fetching, the LOB buffer is filled up ahead to its total length or to the maximum length of
the LOB, so that fetches which follow can be served from the buffer.
(c) SAP AG; 2006