Disabling progressive streaming with CLI/JDBC applications

Troubleshooting

Problem

Problems may occur when retrieving LOB or XML data when the data size is larger than 32K after migrating to DB2 servers that support the progressive streaming interface. You may want to disable this feature so that LOB / XML data is processed the way it was before the progressive streaming interface was implemented

Environment

DB2 v9.1 FP1+ JDBC and CLI applications running against DB2 v9.5/v9.7 for Linux, Unix, Windows (LUW) or DB2 v9 for zOS

Resolving The Problem

If the data source supports progressive streaming, also known as dynamic data format (DDF), then CLI and JDBC based applications starting from DB2 v9.1 FP1 are using progressive streaming/DDF by default.

This feature can be disabled either by the application driver that is being used, or on the database server. By default, if the database server supports progressive streaming, then all application drivers starting from v9.1 FP1 will support progressive streaming by default as well.

Disabling progressive streaming / DDF by the application driver:

- for CLI based application by using the CLI/ODBC keyword DynamicDataFormat=0

You can either modify the db2cli.ini file and add DynamicDataFormat=0 under the data source section that is used by the CLI/ODBC application, or you can add DynamicDataFormat=0 to the connection string used by the application.

- for applications that use the Universal JDBC driver by setting the progressiveStreaming property value to 2.

We have several methods in place to disable progressive streaming for application using the Universal JDBC driver :

1) Simply set the DataSource / Connection URL property progressiveStreaming=2.
2) Use a global properties file on the client. The link to the folowing article covers taking a JCC trace, but the method of using a properties file is applicable to progressive streaming as well:http://www.ibm.com/developerworks/db2/library/techarticle/dm-0506fechner/

Disabling progressive streaming / DDF on the database server:
- for DB2 v9.5/v9.7 LUW servers by setting the DB2_RESTRICT_DDF db2set environment variable to TRUE.

db2set DB2_RESTRICT_DDF=TRUE
db2stop
db2start

- for DB2 for zOS v9.1 servers by setting the PRGSTRIN ZPARM value to DISABLE.
More information about this ZPARM can be found here:

http://ibmsystemsmag.blogs.com/db2utor/2008/01/db2-9-system-pa.html

Note:
For JCC based applications before DB2 v9.1 FP2, if progressiveStreaming is NOT_SET on the client side it is still enabled even if the DB2 for zOS ZPARM PRGSTRIN=DISABLE.