DB2 10.5 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.

Special processing for java.sql.Timestamp input data

Before version 3.63 or 4.13 of the IBM Data Server Driver for JDBC and SQLJ, if the target data type is not known, the target data server supports TIMESTAMP WITH TIME ZONE, and the input data type is java.sql.Timestamp, the driver chooses TIMESTAMP WITH TIME ZONE as the target type. Starting with version 3.63 or 4.13, if the target data type is not known, the target data server supports TIMESTAMP WITH TIME ZONE, and the input data type is java.sql.Timestamp, the driver chooses TIMESTAMP WITH TIME ZONE as the target type, except when the input object has a value of 0001-01-01-00:00:00.000000 or 9999-12-31-23:59:59.999999. In those cases, the driver chooses the TIMESTAMP type, without a time zone. Use of the TIMESTAMP data type in those two cases prevents an overflow condition from occurring because of adjustment of the value for the implied time zone. The implied time zone is the time zone of the Java™ virtual machine (JVM). Starting with version 3.65 or 4.15, the timestamps for which the driver chooses the TIMESTAMP type, without the time zone, are 0001-01-01, with any time, or 9999-12-31, with any time.

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 value for Connection and DataSource properties maxRetriesForClientReroute and retryIntervalForClientReroute for connections to a DB2 for z/OS data sharing group (driver versions 3.64, 4.14)

The default value for Connection and DataSource properties maxRetriesForClientReroute and retryIntervalForClientReroute change in version 3.64 and 4.14, of the IBM Data Server Driver for JDBC and SQLJ for connections to a DB2 for z/OS data sharing group. The following table lists the new defaults.

Connection and DataSource property	Default value before versions 3.64 and 4.14	Default value for versions 3.64 and 4.14, or later
maxRetriesForClientReroute	Retry for 10 minutes, with a wait time between retries that increases as the length of time from the first retry increases.	5
retryIntervalForClientReroute	Retry for 10 minutes, with a wait time between retries that increases as the length of time from the first retry increases.	0

Changes to the default value and meaning of a retry for Connection and DataSource property maxRetriesForClientReroute for connections to a DB2 for z/OS data sharing group (driver versions 3.66 and 4.16)

The default value for Connection and DataSource property maxRetriesForClientReroute and the meaning of a retry change in version 3.66 and 4.16 of the IBM Data Server Driver for JDBC and SQLJ for connections to a DB2 for z/OS data sharing group.

The following table shows the old and new defaults.

Change for property	Before versions 3.66 and 4.16	Versions 3.66 and 4.16, or later
Meaning of a retry	One attempt to connect to one member of the data sharing group.	One attempt to connect to all members of the data sharing group other than the failed member, and to the group IP address. This change might make it necessary to lower the value of maxRetriesForClientReroute.
Default value	For connections to a DB2 for z/OS data server: For the first connection to a data sharing group, if maxRetriesForClientReroute and retryIntervalForClientReroute are not set, and the enableSysplexWLB property is set to `true`, the default is to retry five times with a retry interval of 0. For a failover during a subsequent connection to a data sharing group, if maxRetriesForClientReroute and retryIntervalForClientReroute are not set, the enableSysplexWLB property is set to `true`, and a cached server list or an alternate server is specified, the default is to retry the connection for 10 minutes, with a wait time between retries that increases as the length of time from the first retry increases.	If the enableSysplexWLB property is set to `true`, the default is 1.

Change to the meaning of a retry for connections to a DB2 for Linux, UNIX, and Windows DB2 pureScale instance (driver versions 3.67 and 4.17)

The meaning of a retry for automatic client reroute changes in version 3.67 and 4.17 of the IBM Data Server Driver for JDBC and SQLJ for connections to a DB2 for Linux, UNIX, and Windows DB2 pureScale® instance.

The following table shows the old and new meanings.

Meaning of a retry before versions 3.67 and 4.17	Meaning of a retry for versions 3.67 and 4.17, or later
One attempt to connect to one member of the DB2 pureScale instance.	One attempt to connect to all members of the DB2 pureScale instance. This change might make it necessary to lower the value of maxRetriesForClientReroute.

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.

Changes to maximum lengths for client info properties for DB2 for z/OS

The maximum lengths 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.66 and 4.16 of the IBM Data Server Driver for JDBC and SQLJ. The following table lists the old and new lengths.

Client into property	Maximum length before versions 3.68 and 4.18	Maximum length for versions 3.68 and 4.18, or later
ApplicationName	32	255
ClientAccountingInformation	22	255
ClientHostname	18	255
ClientUser	16	128

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.

Changes to driver behavior for failure of seamless failover during automatic client reroute

Before versions 3.67 and 4.17 of the IBM Data Server Driver for JDBC and SQLJ, seamless failover error behavior was that after 10 attempts to reconnect to a failover server and execute the SQL statement that failed previously, the driver issued an SQLException with SQL error code -20542.

Starting with versions 3.67 and 4.17 of the IBM Data Server Driver for JDBC and SQLJ, seamless failover error behavior is that after one attempt to reconnect to a failover server and execute the SQL statement that failed previously, the driver issues an SQLException with SQL error code -4228.

Changes to trace polling default

The default for db2.jcc.tracePolling is false before Version 3.69 of IBM Data Server Driver for JDBC and SQLJ, and the default is true for Version 3.69 and later.