DB2 10.5 for Linux, UNIX, and Windows

Progressive streaming with the IBM Data Server Driver for JDBC and SQLJ

If the data source supports progressive streaming, also known as dynamic data format, the IBM® Data Server Driver for JDBC and SQLJ can use progressive streaming to retrieve data in LOB or XML columns.

DB2® for z/OS® Version 9.1 and later supports progressive streaming for LOBs and XML objects. DB2 for Linux, UNIX, and Windows Version 9.5 and later, IBM Informix® Version 11.50 and later, and DB2 for i V6R1 and later support progressive streaming for LOBs.

With progressive streaming, the data source dynamically determines the most efficient mode in which to return LOB or XML data, based on the size of the LOBs or XML objects.

Progressive streaming is the default behavior in the following environments:

MinimumIBM Data Server Driver for JDBC and SQLJ version	Minimum data server version	Types of objects
3.53	DB2 for i V6R1	LOB, XML
3.50	DB2 for Linux, UNIX, and Windows Version 9.5	LOB
3.50	IBM Informix Version 11.50	LOB
3.2	DB2 for z/OS Version 9	LOB, XML

You set the progressive streaming behavior on new connections using the IBM Data Server Driver for JDBC and SQLJ progressiveStreaming property.

For DB2 for z/OS Version 9.1 and later data sources, or DB2 for Linux, UNIX, and Windows Version 9.5 and later data sources, you can set the progressive streaming behavior for existing connections with the DB2Connection.setDBProgressiveStreaming(DB2BaseDataSource.YES) method. If you call DB2Connection.setDBProgressiveStreaming(DB2BaseDataSource.YES), all ResultSet objects that are created on the connection use progressive streaming behavior.

When progressive streaming is enabled, you can control when the JDBC driver materializes LOBs with the streamBufferSize property. If a LOB or XML object is less than or equal to the streamBufferSize value, the object is materialized.

A LOB object is also materialized when an application program performs any of the following actions, regardless of the progressiveStreaming or streamBufferSize settings:

Invokes a user-defined function with a LOB value as an argument
Calls a stored procedure with a LOB value as an input or output argument
Assigns a LOB host variable to a LOB locator host variable

Important: With progressive streaming, when you retrieve a LOB or XML value from a ResultSet into an application variable, you can manipulate the contents of that application variable until you move the cursor or close the cursor on the ResultSet. After that, the contents of the application variable are no longer available to you. If you perform any actions on the LOB in the application variable, you receive an SQLException. For example, suppose that progressive streaming is enabled, and you execute statements like this:

… 
ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM MY_TABLE");
rs.next();                // Retrieve the first row of the ResultSet 
Clob clobFromRow1  = rs.getClob(1); 
                          // Put the CLOB from the first column of
                          // the first row in an application variable
String substr1Clob = clobFromRow1.getSubString(1,50);
                          // Retrieve the first 50 bytes of the CLOB
rs.next();                // Move the cursor to the next row.
                          // clobFromRow1 is no longer available.
// String substr2Clob = clobFromRow1.getSubString(51,100);
                          // This statement would yield an SQLException
Clob clobFromRow2  = rs.getClob(1); 
                          // Put the CLOB from the first column of 
                          // the second row in an application variable
rs.close();               // Close the ResultSet. 
                          // clobFromRow2 is also no longer available.

After you execute rs.next() to position the cursor at the second row of the ResultSet, the CLOB value in clobFromRow1 is no longer available to you. Similarly, after you execute rs.close() to close the ResultSet, the values in clobFromRow1 and clobFromRow2 are no longer available.

If you disable progressive streaming, the way in which the IBM Data Server Driver for JDBC and SQLJ handles LOBs depends on the value of the fullyMaterializeLobData property.

Use of progressive streaming is the preferred method of LOB or XML data retrieval.