DB2 Version 9.7 for Linux, UNIX, and Windows

JDBC differences between the current IBM Data Server Driver for JDBC and SQLJ and earlier DB2 JDBC drivers

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

Important: The DB2® JDBC Type 2 Driver for Linux, UNIX and Windows (DB2 JDBC Type 2 Driver) is deprecated. This information is provided to assist you in moving your applications to the IBM Data Server Driver for JDBC and SQLJ.

Supported methods

For a comparison of method support by the JDBC drivers, see "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, use of progressive streaming 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, use of progressive streaming 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 and the DB2 JDBC Type 2 Driver 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 version 4.0 and later

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.

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.

Support for scrollable and updatable ResultSets

The IBM Data Server Driver for JDBC and SQLJ supports scrollable and updatable ResultSets.

The DB2 JDBC Type 2 Driver supports scrollable ResultSets but not updatable ResultSets.

Difference in URL syntax

The syntax of the url parameter in the DriverManager.getConnection method is different for each driver. See the following topics for more information:

"Connect to a data source using the DriverManager interface with the IBM Data Server Driver for JDBC and SQLJ"
"Connect to a data source using the DriverManager interface with the DB2 JDBC Type 2 Driver"

Difference in error codes and SQLSTATEs returned for driver errors

The IBM Data Server Driver for JDBC and SQLJ does not use existing SQLCODEs or SQLSTATEs for internal errors, as the other drivers do. See "Error codes issued by the IBM Data Server Driver for JDBC and SQLJ" and "SQLSTATEs issued by the IBM Data Server Driver for JDBC and SQLJ".

The JDBC/SQLJ driver for OS/390® and z/OS return ODBC SQLSTATEs when internal errors occur.

How much error message text is returned

With the IBM Data Server Driver for JDBC and SQLJ, when you execute SQLException.getMessage(), formatted message text is not returned unless you set the retrieveMessagesFromServerOnGetMessage property to true.

With the DB2 JDBC Type 2 Driver, when you execute SQLException.getMessage(), formatted message text is returned.

Security mechanisms

The JDBC drivers have different security mechanisms.

For information on IBM Data Server Driver for JDBC and SQLJ security mechanisms, see "Security under the IBM Data Server Driver for JDBC and SQLJ".

For information on security mechanisms for the DB2 JDBC Type 2 Driver, see "Security under the DB2 JDBC Type 2 Driver".

Support for read-only connections

With the IBM Data Server Driver for JDBC and SQLJ, you can make a connection read-only through the readOnly property for a Connection or DataSource object.

The DB2 JDBC Type 2 Driver uses the Connection.setReadOnly value when it determines whether to make a connection read-only. However, setting Connection.setReadOnly(true) does not guarantee that the connection is read-only.

Results returned from ResultSet.getString for a BIT DATA column

The IBM Data Server Driver for JDBC and SQLJ returns data from a ResultSet.getString call for a CHAR FOR BIT DATA or VARCHAR FOR BIT DATA column as a lowercase hexadecimal string.

The DB2 JDBC Type 2 Driver returns the data as an uppercase hexadecimal string.

Results returned from ResultSet.getString for a TIMESTAMP column

By default, the IBM Data Server Driver for JDBC and SQLJ truncates trailing zeroes when it returns data for a ResultSet.getString call for a TIMESTAMP column value. You can change this behavior with the timestampPrecisionReporting property.

The DB2 JDBC Type 2 Driver does not truncate trailing zeroes when it returns data for a ResultSet.getString call for a TIMESTAMP column value.

Result of an executeUpdate call that affects no rows

The IBM Data Server Driver for JDBC and SQLJ generates an SQLWarning when an executeUpdate call affects no rows.

The DB2 JDBC Type 2 Driver does not generate an SQLWarning.

Result of a getDate or getTime call for a TIMESTAMP column

The IBM Data Server Driver for JDBC and SQLJ does not generate an SQLWarning when a getDate or getTime call is made against a TIMESTAMP column.

The DB2 JDBC Type 2 Driver generates an SQLWarning when a getDate or getTime call is made against a TIMESTAMP column.

Date and time adjustment for input and output values that to do not correspond to real dates and times

During update or retrieval of data in SQL DATE, TIME, or TIMESTAMP columns, the IBM Data Server Driver for JDBC and SQLJ adjusts date and time values that do not correspond to real dates and times. For example, if you update a TIMESTAMP column with the value 2007-12-31 24:00:00.0, the IBM Data Server Driver for JDBC and SQLJ adjusts the value to 2008-01-01 00:00:00.0. If you update a TIMESTAMP column with the value 9999-12-31 24:00:00.0, the IBM Data Server Driver for JDBC and SQLJ throws an exception because the adjusted value, 10000-01-01 00:00:00.0, is invalid.

The DB2 JDBC Type 2 Driver for Linux, UNIX and Windows does no adjustment of date or time values that do not correspond to real dates or times. That driver passes the values to and from the database as they are. For example, if you update a TIMESTAMP column with the value 9999-12-31 24:00:00.0 under the DB2 JDBC Type 2 Driver for Linux, UNIX and Windows, no exception is thrown. See the information on date, time, and timestamp values that can cause problems in JDBC and SQLJ applications for more information.

Default format for retrieval of DATE, TIME, or TIMESTAMP column data into strings

For the DB2 JDBC Type 2 Driver for Linux, UNIX and Windows, the default format for retrieval of DATE, TIME, or TIMESTAMP column data into strings is JIS, unless the default format is changed by the CLI keyword DateTimeStringFormat.

For the IBM Data Server Driver for JDBC and SQLJ, the default format for retrieval of DATE, TIME, or TIMESTAMP column data into strings is ISO, unless the default format is changed by the dateFormat or timeFormat Connection or DataSource property.

When an exception is thrown for PreparedStatement.setXXXStream with a length mismatch

When you use the PreparedStatement.setBinaryStream , PreparedStatement.setCharacterStream, or PreparedStatement.setUnicodeStream method, the length parameter value must match the number of bytes in the input stream.

If the numbers of bytes do not match, the IBM Data Server Driver for JDBC and SQLJ does not throw an exception until the subsequent PreparedStatement.executeUpdate method executes. Therefore, for the IBM Data Server Driver for JDBC and SQLJ, some data might be sent to the server when the lengths to not match. That data is truncated or padded by the server. The calling application needs to issue a rollback request to undo the database updates that include the truncated or padded data.

The DB2 JDBC Type 2 Driver throws an exception after the PreparedStatement.setBinaryStream, PreparedStatement.setCharacterStream, or PreparedStatement.setUnicodeStream method executes.

Default mappings for PreparedStatement.setXXXStream

With the IBM Data Server Driver for JDBC and SQLJ, when you use the PreparedStatement.setBinaryStream , PreparedStatement.setCharacterStream, or PreparedStatement.setUnicodeStream method, and no information about the data type of the target column is available, the input data is mapped to a BLOB or CLOB data type.

For the DB2 JDBC Type 2 Driver, the input data is mapped to a VARCHAR FOR BIT DATA or VARCHAR data type.

Differences in character conversion

When character data is transferred between a client and a server, the data must be converted to a form that the receiver can process.

For the IBM Data Server Driver for JDBC and SQLJ, character data that is sent from the data source to the client is converted using Java's built-in character converters. The conversions that the IBM Data Server Driver for JDBC and SQLJ supports are limited to those that are supported by the underlying JRE implementation.

A IBM Data Server Driver for JDBC and SQLJ client using type 4 connectivity sends data to the data source as Unicode UTF-8.

For the DB2 JDBC Type 2 Driver, character conversions can be performed if the conversions are supported by the DB2 server.

If a GRAPHIC column contains single-byte characters (an error condition), the DB2 JDBC Type 2 Driver performs character conversion during data retrieval without issuing an error, but the results might be incorrect. The IBM Data Server Driver for JDBC and SQLJ issues an error.

If character data other than ASCII data is retrieved from a CLOB column with getAsciiStream, the DB2 JDBC Type 2 Driver performs character conversion correctly during data retrieval. The IBM Data Server Driver for JDBC and SQLJ does not perform character conversion correctly. getAsciiStream should be used only for retrieval of ASCII data. For other types of character data, use getCharacterStream or getString instead.

Those drivers use CCSID information from the data source if it is available. The drivers convert input parameter data to the CCSID of the data source before sending the data. If target CCSID information is not available, the drivers send the data as Unicode UTF-8.

Implicit or explicit data type conversion for input parameters

If you execute a PreparedStatement.setXXX method, and the resulting data type from the setXXX method does not match the data type of the table column to which the parameter value is assigned, the driver returns an error unless data type conversion occurs.

With the IBM Data Server Driver for JDBC and SQLJ, conversion to the correct SQL data type occurs implicitly if the target data type is known and if the deferPrepares and sendDataAsIs connection properties are set to false. In this case, the implicit values override any explicit values in the setXXX call. If the deferPrepares connection property or the sendDataAsIs connection property is set to true, you must use the PreparedStatement.setObject method to convert the parameter to the correct SQL data type.

For the DB2 JDBC Type 2 Driver, if the data type of a parameter does not match its default SQL data type, you must use the PreparedStatement.setObject method to convert the parameter to the correct SQL data type.

Support for String to BINARY conversions for input parameters

The IBM Data Server Driver for JDBC and SQLJ does not support PreparedStatement.setObject calls of the following form when x is an object of type String:

setObject(parameterIndex, x, java.sqlTypes.BINARY)

The DB2 JDBC Type 2 Driver supports calls of this type. The driver interprets the value of x as a hexadecimal string.

Result of PreparedStatement.setObject with a decimal scale mismatch

With the IBM Data Server Driver for JDBC and SQLJ, if you call PreparedStatement.setObject with a decimal input parameter, and the scale of the input parameter is greater than the scale of the target column, the driver truncates the trailing digits of the input value before assigning the value to the column.

The DB2 JDBC Type 2 Driver rounds the trailing digits of the input value before assigning the value to the column.

Valid range for ResultSet.getBigDecimal scale parameter

The deprecated form of ResultSet.getBigDecimal has a scale parameter as the second parameter. The IBM Data Server Driver for JDBC and SQLJ allows a range of 0 to 32 for the scale parameter.

The DB2 JDBC Type 2 Driver allows a range of -1 to 32.

Support for conversions from the java.lang.Character data type for input parameters

For the following form of PreparedStatement.setObject, the IBM Data Server Driver for JDBC and SQLJ supports the standard data type mappings of Java™ objects to JDBC data types when it converts x to a JDBC data type:

setObject(parameterIndex, x)

The DB2 JDBC Type 2 Driver supports the non-standard mapping of x from java.lang.Character to CHAR.

Support for ResultSet.getBinaryStream against a character column

The IBM Data Server Driver for JDBC and SQLJ supports ResultSet.getBinaryStream with an argument that represents a character column only if the column has the FOR BIT DATA attribute.

For the DB2 JDBC Type 2 Driver, if the ResultSet.getBinaryStream argument is a character column, that column does not need to have the FOR BIT DATA attribute.

Data returned from ResultSet.getString against a binary column

With the IBM Data Server Driver for JDBC and SQLJ, when you execute ResultSet.getString against a binary column, the returned data is in the form of lowercase, hexadecimal digit pairs.

With the DB2 JDBC Type 2 Driver, when you execute ResultSet.getString against a binary column, the returned data is in the form of uppercase, hexadecimal digit pairs.

Result of using setObject with a Boolean input type and a CHAR target type

With the IBM Data Server Driver for JDBC and SQLJ, when you execute PreparedStatement.setObject(parameterIndex,x,CHAR), and x is Boolean, the value "0" or "1" is inserted into the table column.

With the DB2 JDBC Type 2 Driver, the string "false" or "true" is inserted into the table column. The table column length must be at least 5.

Result of using getBoolean to retrieve a value from a CHAR column

With the IBM Data Server Driver for JDBC and SQLJ, when you execute ResultSet.getBoolean or CallableStatement.getBoolean to retrieve a Boolean value from a CHAR column, and the column contains the value "false" or "0", the value false is returned. If the column contains any other value, true is returned.

With the DB2 JDBC Type 2 Driver, when you execute ResultSet.getBoolean or CallableStatement.getBoolean to retrieve a Boolean value from a CHAR column, and the column contains the value "true" or "1", the value true is returned. If the column contains any other value, false is returned.

Result of executing ResultSet.next on a closed cursor

With the IBM Data Server Driver for JDBC and SQLJ, when all rows have been retrieved from a ResultSet, the cursor is automatically closed. When you execute ResultSet.next after the cursor is closed, an SQLException is thrown. This behavior conforms with the JDBC standard.

With the DB2 JDBC Type 2 Driver, when all rows have been retrieved from a ResultSet, the cursor is not closed. When ResultSet.next is executed after all rows have been retrieved, a value of false is returned, and no exception is thrown.

Result of specifying null arguments in DatabaseMetaData calls

With the IBM Data Server Driver for JDBC and SQLJ, you can specify null for an argument in a DatabaseMetaData method call only where the JDBC specification states that null is allowed. Otherwise, an exception is thrown.

With the DB2 JDBC Type 2 Driver, null means that the argument is not used to narrow the search.

Result of ResultSetMetaData.getColumnTypeName calls for CHAR FOR BIT DATA or VARCHAR FOR BIT DATA columns

With the IBM Data Server Driver for JDBC and SQLJ, ResultSetMetaData.getColumnTypeName returns CHAR FOR BIT DATA for a CHAR FOR BIT DATA column, and VARCHAR FOR BIT DATA for a VARCHAR FOR BIT DATA column.

With the DB2 JDBC Type 2 Driver, ResultSetMetaData.getColumnTypeName returns CHAR () FOR BIT DATA for a CHAR FOR BIT DATA column, and VARCHAR () FOR BIT DATA for a VARCHAR FOR BIT DATA column.

Folding of method arguments to uppercase

The IBM Data Server Driver for JDBC and SQLJ does not fold any arguments in method calls to uppercase.

The DB2 JDBC Type 2 Driver folds the argument of a Statement.setCursorName call to uppercase. To prevent the cursor name from being folded to uppercase, precede and follow the cursor name with the characters \". For example:

Statement.setCursorName("\"mycursor\"");

Support for timestamp escape clauses

The IBM Data Server Driver for JDBC and SQLJ supports the standard form of an escape clause for TIME:

{t 'hh:mm:ss'}

In addition to the standard form, the DB2 JDBC Type 2 Driver supports the following form of a TIME escape clause:

{ts 'hh:mm:ss'}

Including a CALL statement in a statement batch

The IBM Data Server Driver for JDBC and SQLJ supports CALL statements in a statement batch.

The DB2 JDBC Type 2 Driver does not support CALL statements in a statement batch.

Removal of extra characters from SQL statement text

The IBM Data Server Driver for JDBC and SQLJ does not remove white-space characters, such as spaces, tabs, and new-line characters, from SQL statement text before it passes that text to the data source.

The DB2 JDBC Type 2 Driver removes white-space characters from SQLstatement text before it passes that text to the data source.

Result of executing PreparedStatement.executeBatch

When a PreparedStatement.executeBatch statement is executed under the IBM Data Server Driver for JDBC and SQLJ, the driver returns an int array of update counts. Each element of the array contains the number of rows that were updated by a statement in the batch.

When a PreparedStatement.executeBatch statement is executed under the DB2 JDBC Type 2 Driver, the driver cannot determine the update counts, so it returns -3 for each update count.

Support for compound SQL

The IBM Data Server Driver for JDBC and SQLJ driver does not support compound SQL blocks.

Compound SQL allows multiple SQL statements to be grouped into a single executable block. For example:

EXEC SQL BEGIN COMPOUND ATOMIC STATIC
  UPDATE ACCOUNTS SET ABALANCE = ABALANCE + :delta
    WHERE AID = :aid;
  UPDATE TELLERS SET TBALANCE = TBALANCE + :delta
    WHERE TID = :tid;
  INSERT INTO TELLERS (TID, BID, TBALANCE) VALUES (:i, :branch_id, 0);
  COMMIT;
END COMPOUND;

The DB2 JDBC Type 2 Driver supports execution of compound SQL blocks with PreparedStatement.executeUpdate or Statement.executeUpdate.

Result of not setting a parameter in a batched update

The IBM Data Server Driver for JDBC and SQLJ driver throws an exception after a PreparedStatement.addBatch call if a parameter is not set.

The DB2 JDBC Type 2 Driver throws an exception after the PreparedStatement.executeBatch call if a parameter is not set for any of the statements in the batch.

Ability to call uncatalogued stored procedures

The IBM Data Server Driver for JDBC and SQLJ driver does not let you call stored procedures that are not defined in the DB2 catalog.

The DB2 JDBC Type 2 Driver lets you call stored procedures that are not defined in the DB2 catalog.

Specification of data types for stored procedure parameters

With the IBM Data Server Driver for JDBC and SQLJ driver, if the data source does not support dynamic execution of the CALL statement, you must specify CALL statement parameters exactly as they are specified in the stored procedure definition.

For example, DB2 for z/OS data sources do not support dynamic execution of CALL statements. Suppose that the first parameter of a stored procedure on a DB2 for z/OS server is defined like this in the CREATE PROCEDURE statement:

OUT PARM1 DECIMAL(3,0)

In the calling application, a statement like cs.registerOutParameter(1, Types.DECIMAL) is not correct. You need to use the form of the registerOutParameter method that specifies the scale as well as the data type: cs.registerOutParameter (1, Types.DECIMAL, 0).

The DB2 JDBC Type 2 Driver does not require that the parameter data types in a calling application match the data types in the CREATE PROCEDURE statement.

Result of preparation of an SQL statement with a semicolon

When the IBM Data Server Driver for JDBC and SQLJ prepares an SQL statement, it sends the statement to the data server with no extra processing. Therefore, if you prepare an SQL statement that ends with a semicolon with the IBM Data Server Driver for JDBC and SQLJ, you receive an error.

When the DB2 JDBC Type 2 Driver prepares an SQL statement that contains a final semicolon, it strips the semicolon. Therefore, if the SQL statement has valid syntax, it executes successfully.

For example, this code fails under the IBM Data Server Driver for JDBC and SQLJ but executes successfully under the DB2 JDBC Type 2 Driver.

PreparedStatement pstmt1 = con.prepareStatement("SELECT c1,c2 FROM testtab;");

Maximum number of concurrently open Statement or PreparedStatement objects

The number of Statement or PreparedStatement objects that you can leave open concurrently is greater for the DB2 Type 2 Driver for Linux, UNIX, and Windows than for the IBM Data Server Driver for JDBC and SQLJ. With the IBM Data Server Driver for JDBC and SQLJ, you might receive error code -805 after you have opened fewer Statement or PreparedStatement objects than with the DB2 Type 2 Driver for Linux, UNIX, and Windows. The difference is due to the way that the drivers handle the underlying resources for those objects.

Recommendation: Close Statement or PreparedStatement objects when you have finished using them.