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

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 or later, progressive streaming, which is also known as dynamic data format, behavior is the default for LOB retrieval, for connections to Db2® on Linux®, UNIX, and Windows systems Version 9.5 or later.

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

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

… 
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.

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 or 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

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 on Linux, UNIX, and Windows systems 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.

Changes to the default value for Connection and DataSource property timerLevelForQueryTimeOut

The default value for Connection and DataSource property timerLevelForQueryTimeOut changes in version 4.26 of the IBM Data Server Driver for JDBC and SQLJ. The following table lists the old and new defaults.

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.

Change to automatic client reroute for a connection to a Db2 for z/OS data sharing group (driver versions 3.72.52 and 4.25.13)

Starting with IBM Data Server Driver for JDBC and SQLJ versions 3.72.52 and 4.25.13, for a connection to a Db2 for z/OS data sharing group, during automatic client reroute, the connection is retried once, using the IP address that is associated with the data sharing group.

Changes to the default values for Connection and DataSource properties maxRetriesForClientReroute and retryIntervalForClientReroute for connections to a Db2 for z/OS data sharing group

For a connection to a Db2 for z/OS data sharing group, the default values for the Connection and DataSource properties maxRetriesForClientReroute and retryIntervalForClientReroute differ depending on the IBM Data Server Driver for JDBC and SQLJ version, as indicated in the following table.

Changes to the default values for Connection and DataSource properties maxRetriesForClientReroute and retryIntervalForClientReroute for connections to Db2 on Linux, UNIX, and Windows systems

For a connection to a Db2 on Linux, UNIX, and Windows systems, the default values for the Connection and DataSource properties maxRetriesForClientReroute and retryIntervalForClientReroute differ depending on the IBM Data Server Driver for JDBC and SQLJ version, as indicated in the following table.

Changes to the meaning of a retry for Connection and DataSource property maxRetriesForClientReroute for connections to a Db2 for z/OS data sharing group

The meaning of a retry change in versions 3.66 and 4.16, and again in version 4.25 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 meaning of a retry for different IBM Data Server Driver for JDBC and SQLJ versions.

Change to the meaning of a retry for connections to a Db2 on Linux, UNIX, and Windows systems 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 on Linux, UNIX, and Windows systems Db2 pureScale® instance.

The following table shows the old and new meanings.

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

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.

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 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 on Linux, UNIX, and Windows systems, 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
• true, for any other driver versions.

If the driver version is 3.67 or 4.17, or later, or 3.64 , 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.

Change to trace polling default

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

Changes to driver behavior on failure of seamless failover for connections to Db2 for z/OS

Before version 4.32 of the IBM Data Server Driver for JDBC and SQLJ, when seamless failover for connections to a Db2 for z/OS data sharing group failed, SQL errors such as -4498, -30108, or -20542 were returned.

Starting with Version 4.32 of the driver, property enableSeamlessFailoverErrorCodes controls the SQL error code that is returned. The default behavior is that SQL error -4499 is returned for all seamless failover errors.

Changes to the security mechanism in driver version 4.33

Before version 4.33 of the IBM Data Server Driver for JDBC and SQLJ, the default security mechanism is CLEAR_TEXT_PASSWORD_SECURITY (3). If the data server does not support CLEAR_TEXT_PASSWORD_SECURITY (3), but supports ENCRYPTED_USER_AND_PASSWORD_SECURITY (9), the driver changes the security mechanism to ENCRYPTED_USER_AND_PASSWORD_SECURITY (9) and attempts to connect to the data server. Any other mismatch in security mechanism support between the requester and the data server results in an error.

Changes to the security mechanism in driver version 4.34

Change to the default encryption algorithm

Before version 4.33 of the IBM Data Server Driver for JDBC and SQLJ, the default encryption algorithm is 56-bit DES encryption (1).

Starting with version 4.33 of the IBM Data Server Driver for JDBC and SQLJ, the default encryption is 256-bit AES encryption (2).

Changes to driver behavior for a data type mismatch between a host variable and a column

Before version 4.33 of the IBM Data Server Driver for JDBC and SQLJ, when the SQLJ profile customizer (db2sqljcustomize) bound a host variable to a column, and there was a mismatch between the host variable data type and the column data type, db2sqljcustomize took no action.

Starting with Version 4.33 of the driver, when there is a data type mismatch, and db2sqljcustomize option -onTypeMismatch is not set, db2sqljcustomize issues a warning and continues the customization process.

Changes to driver behavior on truncation of input data

Before version 4.33 of the IBM Data Server Driver for JDBC and SQLJ, when a value was inserted into a column, and the value was too large for the column, the driver took no action.

Starting with Version 4.33 of the driver, when data that is too large for a column is inserted into the column, the default behavior is that the driver issues a warning. To cause the same behavior as in earlier versions of the driver, set Connection and DataSource property onDataTruncation to DB2BaseDataSource.ON_DATA_TRUNCATION_NO_ACTION_VALUE (3), or set configuration property db2.jcc.onDataTruncation or db2.jcc.override.onDataTruncation to 3.

Change to automatic connection checking when sysplex workload balancing is enabled at the data server

Before version 4.34 of the IBM Data Server Driver for JDBC and SQLJ, automatic connection checking when an application released its connection to the connection pool was performed when com.ibm.db2.jcc.DB2ConnectionPool.testConnectionOnCheckin was set to true, regardless of whether sysplex workload balancing was enabled at the data server. Automatic connection checking in that environment could lead to excessive network activity.

Change to support for the keepAliveTimeOut property

Before version 4.34 of the IBM Data Server Driver for JDBC and SQLJ, keepAliveTimeOut was not supported if the Java runtime environment was Java SE 9 or later.

Starting with version 4.34 of the IBM Data Server Driver for JDBC and SQLJ driver, keepAliveTimeOut is supported when the Java runtime environment is Java SE 9 or later, as well as earlier versions of the Java runtime environment. The IBM Data Server Driver for JDBC and SQLJ supports keepAliveTimeOut regardless of the JDK level if the JDK supports keepAliveTimeOut.

Change to the default for property sslClientHostnameValidation

Before version 4.34 of the IBM Data Server Driver for JDBC and SQLJ, the default for sslClientHostnameValidation was OFF. Starting with version 4.34 of the IBM Data Server Driver for JDBC and SQLJ, the default is BASIC.

Change to the minimum Java Runtime Environment requirement

Change to the license files that are required for connecting to Db2 for z/OS or Db2 for IBM i

Starting with version 4.34 of the IBM Data Server Driver for JDBC and SQLJ and Db2 Connect Version 12.1, the license server that is required for connecting to Db2 for z/OS or Db2 for IBM i must be downloaded from Passport Advantage when the user upgrades from one major version of Db2 Connect to another (for example, from Db2 Connect 11.5 to Db2 Connect 12.1).

Also starting with version 4.34 of the IBM Data Server Driver for JDBC and SQLJ and Db2 Connect Version 12.1, the license file that is required depends on the Db2 Connect edition.