DB2 Version 9.7 for Linux, UNIX, and Windows

JDBC differences between versions of the IBM Data Server Driver for JDBC and SQLJ

Before you can upgrade your JDBC applications from older to newer versions of the IBM® Data Server Driver for JDBC and SQLJ, you need to understand the differences between those drivers.

Supported methods

For a list of methods that the IBM Data Server Driver for JDBC and SQLJ supports, see the information on driver support for JDBC APIs.

Use of progressive streaming by the JDBC drivers

For IBM Data Server Driver for JDBC and SQLJ, Version 3.50 and later, progressive streaming, which is also known as dynamic data format, behavior is the default for LOB retrieval, for connections to DB2® for Linux, UNIX, and Windows Version 9.5 and later.

Progressive streaming is supported in the IBM Data Server Driver for JDBC and SQLJ Version 3.1 and later, but for IBM Data Server Driver for JDBC and SQLJ version 3.2 and later, progressive streaming behavior is the default for LOB and XML retrieval, for connections to DB2 for z/OS® Version 9.1 and later.

Previous versions of the IBM Data Server Driver for JDBC and SQLJ did not support progressive streaming.

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.

To avoid errors that are due to this changed behavior, you need to take one of the following actions:

Modify your applications.
Applications that retrieve LOB data into application variables can manipulate the data in those application variables only until the cursors that were used to retrieve the data are moved or closed.
Disable progressive streaming by setting the progressiveStreaming property to DB2BaseDataSource.NO (2).

ResultSetMetaData values for IBM Data Server Driver for JDBC and SQLJ

For the IBM Data Server Driver for JDBC and SQLJ version 4.0 and later, the default behavior of ResultSetMetaData.getColumnName and ResultSetMetaData.getColumnLabel differs from the default behavior for earlier JDBC drivers.

If you need to use IBM Data Server Driver for JDBC and SQLJ version 4.0 or later, but your applications need to return the ResultSetMetaData.getColumnName and ResultSetMetaData.getColumnLabel values that were returned with older JDBC drivers, you can set the useJDBC4ColumnNameAndLabelSemantics Connection and DataSource property to DB2BaseDataSource.NO (2).

Batch updates with automatically generated keys have different results in different driver versions

With the IBM Data Server Driver for JDBC and SQLJ version 3.52 or later, preparing an SQL statement for retrieval of automatically generated keys is supported.

With the IBM Data Server Driver for JDBC and SQLJ version 3.50 or version 3.51, preparing an SQL statement for retrieval of automatically generated keys and using the PreparedStatement object for batch updates causes an SQLException.

Versions of the IBM Data Server Driver for JDBC and SQLJ before Version 3.50 do not throw an SQLException when an application calls the addBatch or executeBatch method on a PreparedStatement object that is prepared to return automatically generated keys. However, the PreparedStatement object does not return automatically generated keys.

Batch updates of data on DB2 for z/OS servers have different results in different driver versions

After you successfully invoke an executeBatch statement, the IBM Data Server Driver for JDBC and SQLJ returns an array. The purpose of the array is to indicate the number of rows that are affected by each SQL statement that is executed in the batch.

If the following conditions are true, the IBM Data Server Driver for JDBC and SQLJ returns Statement.SUCCESS_NO_INFO (-2) in the array elements:

The application is connected to a subsystem that is in DB2 for z/OS Version 8 new-function mode, or later.
The application is using Version 3.1 or later of the IBM Data Server Driver for JDBC and SQLJ.
The IBM Data Server Driver for JDBC and SQLJ uses multi-row INSERT operations to execute batch updates.

This occurs because with multi-row INSERT, the database server executes the entire batch as a single operation, so it does not return results for individual SQL statements.

If you are using an earlier version of the IBM Data Server Driver for JDBC and SQLJ, or you are connected to a data source other than DB2 for z/OS Version 8 or later, the array elements contain the number of rows that are affected by each SQL statement.

Batch updates and deletes of data on DB2 for z/OS servers have different size limitations in different driver versions

Before IBM Data Server Driver for JDBC and SQLJ version 3.59 or 4.9, a DisconnectException with error code -4499 was thrown for IBM Data Server Driver for JDBC and SQLJ type 4 connectivity to DB2 for z/OS if the size of an update or delete batch was greater than 32KB. Starting with version 3.59 or 4.9, this restriction no longer exists, and the exception is no longer thrown.

Initial value of the CURRENT CLIENT_ACCTNG special register

For a JDBC or SQLJ application that runs under the IBM Data Server Driver for JDBC and SQLJ version 2.6 or later, using type 4 connectivity, the initial value for the DB2 for z/OS CURRENT CLIENT_ACCTNG special register is the concatenation of the DB2 for z/OS version and the value of the clientWorkStation property. For any other JDBC driver, version, and connectivity, the initial value is not set.

Properties that control the use of multi-row FETCH

Before version 3.7 and version 3.51 of the IBM Data Server Driver for JDBC and SQLJ, multi-row FETCH support was enabled and disabled through the useRowsetCursor property, and was available only for scrollable cursors, and for IBM Data Server Driver for JDBC and SQLJ type 4 connectivity to DB2 for z/OS. Starting with version 3.7 and 3.51:

For IBM Data Server Driver for JDBC and SQLJ type 2 connectivity on DB2 for z/OS, the IBM Data Server Driver for JDBC and SQLJ uses only the enableRowsetSupport property to determine whether to use multi-row FETCH for scrollable or forward-only cursors.
For IBM Data Server Driver for JDBC and SQLJ type 4 connectivity to DB2 for z/OS or DB2 for Linux, UNIX, and Windows, or IBM Data Server Driver for JDBC and SQLJ type 2 connectivity on DB2 for Linux, UNIX, and Windows, the IBM Data Server Driver for JDBC and SQLJ uses the enableRowsetSupport property to determine whether to use multi-row FETCH for scrollable cursors, if enableRowsetSupport is set. If enableRowsetSupport is not set, the driver uses the useRowsetCursor property to determine whether to use multi-row FETCH.

JDBC 1 positioned updates and deletes and multi-row FETCH

Before version 3.7 and version 3.51 of the IBM Data Server Driver for JDBC and SQLJ, multi-row FETCH from DB2 for z/OS tables was controlled by the useRowsetCursor property. If an application contained JDBC 1 positioned update or delete operations, and multi-row FETCH support was enabled, the IBM Data Server Driver for JDBC and SQLJ permitted the update or delete operations, but unexpected updates or deletes might occur.

Starting with version 3.7 and 3.51 of the IBM Data Server Driver for JDBC and SQLJ, the enableRowsetSupport property enables or disables multi-row FETCH from DB2 for z/OS tables or DB2 for Linux, UNIX, and Windows tables. The enableRowsetSupport property overrides the useRowsetCursor property. If multi-row FETCH is enabled through the enableRowsetSupport property, and an application contains a JDBC 1 positioned update or delete operation, the IBM Data Server Driver for JDBC and SQLJ throws an SQLException.

Valid forms of prepareStatement for retrieval of automatically generated keys from a DB2 for z/OS view

Starting with version 3.57 or version 4.7 of the IBM Data Server Driver for JDBC and SQLJ, if you are inserting data into a view on a DB2 for z/OS data server, and you want to retrieve automatically generated keys, you need to use one of the following methods to prepare the SQL statement that inserts rows into the view:

Connection.prepareStatement(sql-statement, String [] columnNames);
Connection.prepareStatement(sql-statement, int [] columnIndexes);
Statement.executeUpdate(sql-statement, String [] columnNames);
Statement.executeUpdate(sql-statement, int [] columnIndexes);

Data loss for TIMESTAMP(p) column updates using setString

If you use a setString call to pass an input value to a TIMESTAMP(p) column, it is possible to send a value with a precision of greater than nine to the column.

Before version 3.59 or version 4.9 of the IBM Data Server Driver for JDBC and SQLJ, data loss could occur if the sendDataAsIs property was set to false, and the precision of the input value was greater than nine.

Starting with version 3.59 and version 4.9 of the IBM Data Server Driver for JDBC and SQLJ, data loss does not occur if the TIMESTAMP(p) column is large enough to accommodate the input value.

Change to result set column name for getColumns

In version 4.12 or earlier of the IBM Data Server Driver for JDBC and SQLJ, the DatabaseMetaData.getColumns method returned a result set that contained a column named SCOPE_CATLOG. In version 4.13 or later of the IBM Data Server Driver for JDBC and SQLJ, the name of that column is SCOPE_CATALOG. If you want the IBM Data Server Driver for JDBC and SQLJ to continue to use the column name SCOPE_CATLOG, set DataSource or Connection property useJDBC41DefinitionForGetColumns to DB2BaseDataSource.NO (2).

Changes to defaults for global configuration properties db2.jcc.maxRefreshInterval, db2.jcc.maxTransportObjects, and db2.jcc.maxTransportObjectWaitTime

The default values for global configuration properties db2.jcc.maxRefreshInterval, db2.jcc.maxTransportObjects, and db2.jcc.maxTransportObjectWaitTime change in version 3.63 and 4.13 of the IBM Data Server Driver for JDBC and SQLJ. The following table lists the old and new defaults.

Configuration property	Default before versions 3.63 and 4.13	Default for versions 3.63 and 4.13 or later
db2.jcc.maxRefreshInterval	30 seconds	10 seconds
db2.jcc.maxTransportObjects	-1 (unlimited)	1000
db2.jcc.maxTransportObjectWaitTime	-1 (unlimited)	1 second

Changes to default values for Connection and DataSource property maxTransportObjects

The default value for Connection and DataSource properties maxTransportObjects changes in version 3.63 and 4.13 of the IBM Data Server Driver for JDBC and SQLJ. The following table lists the old and new defaults.

Connection and DataSource property	Default value before versions 3.63 and 4.13	Default value for versions 3.63 and 4.13 or later
maxTransportObjects	-1 (unlimited)	1000

Changes to default values for client info properties for IBM Data Server Driver for JDBC and SQLJ type 2 connectivity on DB2 for z/OS

The default values for client info properties for IBM Data Server Driver for JDBC and SQLJ type 2 connectivity on DB2 for z/OS change in version 3.64 and 4.14 of the IBM Data Server Driver for JDBC and SQLJ. The following table lists the old and new defaults.

Client into property	Default value before versions 3.64 and 4.14	Default value for versions 3.64 and 4.14 or later
ApplicationName	Empty string	The string "db2jcc_application"
ClientAccountingInformation	Empty string	Empty string
ClientHostname	Empty string	The string "RRSAF".
ClientUser	Empty string	The user ID that was specified for the connection. If no user ID was specified, the RACF® user ID is used.

Change to default for global configuration property db2.jcc.enableInetAddressGetHostName

Starting with versions 3.65 and 4.15 of the IBM Data Server Driver for JDBC and SQLJ, the default for db2.jcc.enableInetAddressGetHostName is false. For versions 3.64 and 4.14 or earlier, the default is true.

Changes to the behavior of the xmlFormat property

Starting with version 4.15 of the IBM Data Server Driver for JDBC and SQLJ, the xmlFormat Connection and DataSource property applies only to XML data retrieval, instead of to XML data update and retrieval. In addition, the default behavior has changed to retrieval of XML data in textual XML format, regardless of whether the data server supports binary XML format.

For update of data in XML columns, xmlFormat has no effect. If the input data is binary XML data, and the data server does not support binary XML data, the input data is converted to textual XML data. Otherwise, no conversion occurs.

Changes to the default value of Connection and DataSource property useCachedCursor

For connections to DB2 for Linux, UNIX, and Windows, the default value of the useCachedCursor Connection and DataSource property has changed.

The default is:

false, if the driver is at one of the following levels:
- Version 3.67 or 4.17, or later
- Version 3.64 or 4.14
true, for any other driver versions.

If the driver version is 3.67 or 4.17, or later, or 3.64 or 4.14, and the deferPrepares property is set to true, the driver behaves as if useCachedCursor is set to false, regardless of the useCachedCursor setting.