LOBs with DB2 for z/OS: Stronger and Faster

138 LOBs with DB2 for z/OS: Stronger and Faster

sqlccrecv( )

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( )

sqlccrecv( ulBytes - 111 ) - rc - 0, time elapsed - +1.802000E-003

( Row=1, iPar=4, fCType=SQL_C_BINARY, rgbValue=x'FFA42B0400121F9D02', pcbValue=9,

piIndicatorPtr=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