DB2 Version 9.7 for Linux, UNIX, and Windows
DB2 Version 9.7 for Linux, UNIX, and Windows
The IBM® Data Server Driver for JDBC and SQLJ contains a number of major enhancements for Version 9.7.
The following enhancements are available in version 3.57 or version 4.7 of the driver. Version 3.57 contains JDBC 3.0 and earlier functions, and version 4.7 contains JDBC 4.0 and earlier functions.
The IBM Data Server Driver for JDBC and SQLJ adds the following new methods to support named parameter markers.
In previous versions of the IBM Data Server Driver for JDBC and SQLJ, only forms of CallableStatement.registerOutParameter, CallableStatement.setXXX, and CallableStatement.getXXX methods that used parameterIndex were supported. With versions 3.57 and 4.7 of the driver, parameterName is also supported in those methods. parameterName is a name that is specified for a parameter in the stored procedure definition.
Alternatively, for JDBC applications, new syntax allows the application to map parameter markers in the CALL statement to the parameter names in the stored procedure definition. For example, in a JDBC application, CALL MYPROC (A=>?) maps a parameter marker to stored procedure parameter A.
For SQLJ applications, new syntax allows the application to map host variable names in the CALL statement to the parameter names in the stored procedure definition. For example, in an SQLJ application, CALL MYPROC (A=>:INOUT x) maps host variable x to stored procedure parameter A.
With the new syntax, you do not need to specify all parameters in the CALL statement. Unspecified parameters take the default values that are specified in the stored procedure definition.
The IBM Data Server Driver for JDBC and SQLJ adds the concurrentAccessResolution Connection or DataSource property. That property specifies whether the IBM Data Server Driver for JDBC and SQLJ requests that a read transaction can access a committed and consistent image of rows that are incompatibly locked by write transactions, if the data source supports accessing currently committed data, and the application isolation level is cursor stability (CS) or read stability (RS).
In addition, the IBM Data Server Driver for JDBC and SQLJ adds the DB2Connection.setDBConcurrentAccessResolution method, which lets you override the concurrentAccessResolution setting for new statements that are created on an existing Connection. The driver also provides the DB2Connection.getDBConcurrentAccessResolution method, which lets you check the concurrent access resolution setting.
Stored procedures that are created on DB2 for Linux, UNIX, and Windows can have OUT parameters of the cursor type. The IBM Data Server Driver for JDBC and SQLJ supports retrieval of data from OUT parameters of the cursor type in JDBC and SQLJ applications. For registration of OUT parameters with the cursor type, the IBM Data Server Driver for JDBC and SQLJ adds the DB2Types.CURSOR data type.
DB2 for Linux, UNIX, and Windows statement concentrator support is the ability to bypass preparation of a statement when it is the same as a statement in the dynamic statement cache, except for literal values. If statement concentrator support is enabled on a DB2 for Linux, UNIX, and Windows data source, you can use the statementConcentrator Connection or DataSource property to specify whether the IBM Data Server Driver for JDBC and SQLJ uses the statement concentrator support.
In addition, the IBM Data Server Driver for JDBC and SQLJ adds the DB2Connection.setDBStatementConcentrator method, which lets you override the statementConcentrator setting for new statements that are created on an existing Connection. The driver also provides the DB2Connection.getDBStatementConcentrator method, which lets you check the statement concentrator setting.
DB2 for Linux, UNIX, and Windows supports timestamp columns of the form TIMESTAMP(p), where the precision of the timestamp value, p, is between 0 and 12. The IBM Data Server Driver for JDBC and SQLJ adds support for update and retrieval of values in a TIMESTAMP(p) column in JDBC and SQLJ applications. To retrieve timestamp values with precision greater than 9, you need to use the constructors and methods in the IBM Data Server Driver for JDBC and SQLJ-only DBTimestamp class.
The maximum precision of a Java™ timestamp value is 9, so there can be a loss of precision during data retrieval if p>9.
For connections to DB2 for z/OS® Version 8 or later, DB2 for Linux, UNIX, and Windows Version 8 or later, or DB2 UDB for iSeries® V5R3 or later, the IBM Data Server Driver for JDBC and SQLJ adds the DB2Connection.getJccSpecialRegisterProperties method. This method lets you retrieve the current special register settings for the data source, for special registers that the IBM Data Server Driver for JDBC and SQLJ supports.
The decimalStringFormat Connection or DataSource property lets you choose the string format in which data from a DECIMAL or DECFLOAT column is retrieved, for an application that runs with the SDK for Java Version 1.5 or later. You can retrieve the data in the format that java.math.BigDecimal.toString method uses, which is the default. Alternatively, you can retrieve the data in the format that java.math.BigDecimal.toPlainString uses.
SQLJ statement clauses in SQLJ applications or SQL statements in JDBC applications can now include compound statements. A compound statement is a BEGIN-END block that includes SQL and procedural statements. All compound statements are executed dynamically, including those in SQLJ applications.
The IBM Data Server Driver for JDBC and SQLJ supports setting of savepoints for connections to IBM Informix® data servers.
The IBM Data Server Driver for JDBC and SQLJ adds the atomicMultiRowInsert Connection or DataSource property for connections to DB2 for Linux, UNIX, and Windows Version 8 and later data servers, DB2 for z/OS Version 8 and later data servers, or IBM Informix V11.10 and later data servers. The atomicMultiRowInsert property lets you specify whether batch insert operations that use the PreparedStatement interface have atomic or non-atomic behavior. Atomic behavior means that a batch operation succeeds only if all insert operations in the batch succeed. Non-atomic behavior, which is the default, means that insert operations succeed or fail individually.
The queryCloseImplicit Connection or DataSource property specifies whether cursors are closed immediately after all rows are fetched. A new value of QUERY_CLOSE_IMPLICIT_COMMIT (3) is added, to specify that cursors are closed after all rows are fetched, and in addition, if the application is in autocommit mode, a commit request is sent to the data source.
Client reroute support is enhanced in the following ways:
During client reroute, if a connection is in a clean state, you can use the enableSeamlessFailover property to suppress the SQLException with error code -4498 that the IBM Data Server Driver for JDBC and SQLJ issues to indicate that a failed connection was re-established.
For cascaded failover, you can use the enableClientAffinitiesList property to control the order in which primary and alternate server reconnections are attempted after a connection failure.
For connections to DB2 for z/OS servers, the Statement.setMaxRows method has been modified to provide better performance.
For connections to Informix servers, the following enhancements are added:
For connections to Informix V11.11 and later, database names can be up to 128 bytes.
For connections to Informix V11.10 and later, ISAM errors are reported as SQLException objects, so SQLException methods can be used to obtain the error code and the message description. In addition, SQLException.printStackTrace calls display information about the cause of the ISAM errors.
This support requires the existence of one or more Connection Managers, a primary server, and one or more alternate servers at Informix 11.50 or later.
For workload balancing to Informix, JDBC and SQLJ applications connect to a Connection Manager. They set the enableSysplexWLB property to indicate that Informix workload balancing is used.
This support requires Informix 11.50 or later.
As of Informix 11.50, Informix supports the BIGINT and BIGSERIAL data types. The IBM Data Server Driver for JDBC and SQLJ lets you access columns with those data types.
For retrieving automatically generated keys from a BIGSERIAL column, the IBM Data Server Driver for JDBC and SQLJ adds the DB2Statement.getIDSBigSerial method.
The IBM Data Server Driver for JDBC and SQLJ supports setting of savepoints for connections to IBM Informix data servers.
For connections to DB2 for z/OS or DB2 for Linux, UNIX, and Windows servers, the DB2Connection.setDBProgressiveStreaming method can be used to change progressive streaming behavior after a connection to a data source is established. The DB2Connection.getDBProgressiveStreaming method can be used to determine the current progressive streaming behavior.
Global trace settings can be changed without shutting down the driver.
ResultSet.next behavior for DB2 connections can be more compatible with ResultSet.next behavior for connections with other database managers.
The allowNextOnExhaustedResultSet property can be set so that ResultSet.next behavior for a connection to DB2 for z/OS or DB2 for Linux, UNIX, and Windows is the same as ResultSet.next behavior for applications that are connected to an Oracle or MySQL data source. When the allowNextOnExhaustedResultSet property is set to DB2BaseDataSource.YES (1), and a forward-only cursor is positioned after the last row of a result set, a call to ResultSet.next returns false, instead of throwing an SQLException.
Batched INSERT statements can return automatically generated keys.
If batch execution of a PreparedStatement object returns automatically generated keys, you can call the DB2PreparedStatement.getDBGeneratedKeys method to retrieve an array of ResultSet objects that contains the automatically generated keys. If a failure occurs during execution of a statement in a batch, you can use the DBBatchUpdateException.getDBGeneratedKeys method to retrieve any automatically generated keys that were returned.
To enable registration of stored procedure OUT parameters as DECFLOAT, the com.ibm.db2.jcc.DB2Types.DECFLOAT JDBC data type is added.
In addition to previously mentioned properties, the following Connection and DataSource properties are added:
You can use new DB2 Java sample programs as templates to create your own application programs.
In DB2 for Linux, UNIX, and Windows Version 9.7 Fix Pack 1, the following enhancements are available in version 3.58 or version 4.8 of the driver. Version 3.58 contains JDBC 3.0 and earlier functions, and version 4.8 contains JDBC 4.0 and earlier functions.
Diagnostic information is traced to the Java standard error output stream when an exception is thrown with an SQL error code of -805. In Java database applications, -805 often indicates that all available IBM Data Server Driver for JDBC and SQLJ packages have been used because there are too many concurrently open statements. The diagnostic information contains a list of SQL strings that contributed to the exception.
JDBC named parameter marker support is enhanced to statement strings that contain SQL/PL blocks with named parameter markers.
Methods are added to the DB2DatabaseMetaData class that let you retrieve information about procedures, functions, and user-defined types that are in modules.
In DB2 for Linux, UNIX, and Windows Version 9.7 Fix Pack 2, the following enhancements are available in version 3.59 or version 4.9 of the driver. Version 3.59 contains JDBC 3.0 and earlier functions, and version 4.9 contains JDBC 4.0 and earlier functions.
Methods and constants are added to the DB2PreparedStatement interface, and methods are added to the DB2ResultSet interface that let you assign the default value or no value to a table column or a result set row. The data server must support extended indicators before you can use these methods and constants.
The following Connection and DataSource property support is changed:
The new DB2ParameterMetaData.getProcedureParameterName method lets you retrieve the defined name of a parameter in an SQL CALL statement.
The following Connection and DataSource properties are added:
Previously, 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. This restriction no longer exists, and the exception is no longer thrown.
SQLJ program preparation now supports the SQLERROR(CHECK) bind option.
For connections to DB2 for Linux, UNIX, and Windows or DB2 for z/OS, searched UPDATE, searched DELETE, and MERGE statements can return automatically generated keys (also called auto-generated keys). For UPDATE, DELETE, or MERGE statements, an automatically generated key can be any column in the table that you are updating, regardless of whether the column is generated by the data server.
In DB2 for Linux, UNIX, and Windows Version 9.7 Fix Pack 3, the following enhancements are available in version 3.61 or version 4.11 of the driver. Version 3.61 contains JDBC 3.0 and earlier functions, and version 4.11 contains JDBC 4.0 and earlier functions.
The following Connection and DataSource property is added:
The IBM Data Server Driver for JDBC and SQLJ adds support for connections to DB2 for i 7.1.
Two new DB2PreparedStatement methods are added.
New configuration property db2.jcc.outputDirectory lets you define a location in which the IBM Data Server Driver for JDBC and SQLJ stores the following files:
The IBM Data Server Driver for JDBC and SQLJ now supports PARAMETER STYLE DB2GENERAL for Java table UDFs.
For connections to Informix servers, the following enhancements are added:
Trusted connections are supported for IBM Data Server Driver for JDBC and SQLJ type 4 connectivity to Informix V11.70 and later.
Method DB2Connection.setDB2ClientDebugInfo can be called to notify the Informix data server that stored procedures and user-defined functions that are using the connection are running in debug mode.
You can collect core driver time, network I/O time, server time, and application time for connections to Informix servers.
For connections to DB2 for z/OS Version 10 servers, the following enhancements are added:
Starting with DB2 for z/OS Version 10, DRDA command and reply message parameters are sent to and received from the data server in Unicode. This support helps decrease CPU cost and character conversion errors. The IBM Data Server Driver for JDBC and SQLJ supports this enhancement for type 4 connectivity.
Extended parameter information support, which was added to the IBM Data Server Driver for JDBC and SQLJ in DB2 for Linux, UNIX, and Windows Version 9.7 Fix Pack 2, can now be used for connections to DB2 for z/OS.
DB2 for z/OS Version 10 adds support for temporal tables. This support enables automatic maintenance of historical information as a table is updated. Applications that use the IBM Data Server Driver for JDBC and SQLJ can access temporal tables. The ResultSetMetaData.isAutoIncrement method returns true for table columns that are defined as ROW BEGIN, ROW END, or TRANSACTION START ID.
DB2 for z/OS Version 10 supports binary XML format (Extensible Dynamic Binary XML DB2 Client/Server Binary XML Format). The IBM Data Server Driver for JDBC and SQLJ can send XML data to the data server or retrieve XML data from the data server as binary XML data.
The Connection and DataSource property xmlFormat specifies the format that is used to send XML data to the data server or retrieve XML data from the data server.
The IBM Data Server Driver for JDBC and SQLJ adds support for update and retrieval of values in columns with these data types in JDBC and SQLJ applications.
DB2 for z/OS Version 10 adds support for the CURRENT EXPLAIN MODE special register, which controls the behavior of EXPLAIN with regard to eligible dynamic SQL statements. The Connection and DataSource property currentExplainMode, which sets CURRENT EXPLAIN MODE, now applies to connections to DB2 for z/OS.
DB2 for z/OS Version 10 allows a read transaction to access a committed and consistent image of rows that are incompatibly locked by write transactions. The Connection and DataSource concurrentAccessResolution property, which controls this support, now applies to connections to DB2 for z/OS.
DB2 for z/OS Version 10 can return XML data to the client without having to materialize the data. This enhancement can decrease the amount of virtual storage that is needed. The IBM Data Server Driver for JDBC and SQLJ is enhanced so that Java applications automatically take advantage of this support.
DB2 for z/OS Version 10, some dynamic SQL statements can be shared with cached statements if the only difference between the dynamic SQL statements and the cached statements is literal constants. The Connection and DataSource statementConcentrator property, which controls whether this type of statement sharing is possible, now applies to connections to DB2 for z/OS.
In DB2 for Linux, UNIX, and Windows Version 9.7 Fix Pack 4, the following enhancements are available in version 3.62 or version 4.12 of the driver. Version 3.62 contains JDBC 3.0 and earlier functions, and version 4.12 contains JDBC 4.0 and earlier functions.
Stored procedures that are created on DB2 for Linux, UNIX, and Windows can have parameters of the BOOLEAN data type. IBM Data Server Driver for JDBC and SQLJ type 4 connectivity supports IN, OUT, or INOUT parameters of the BOOLEAN type in JDBC applications.
Stored procedures that are created on DB2 for Linux, UNIX, and Windows can have parameters of the ROW type or as an ARRAY type, in which the array elements have the ROW type. The IBM Data Server Driver for JDBC and SQLJ supports IN, OUT, or INOUT parameters of types ROW or ARRAY of ROW in JDBC applications. Applications use the Java java.sql.Struct objects for ROW parameters, and java.sql.Array objects for ARRAY of ROW parameters.
The IBM Data Server Driver for JDBC and SQLJ also introduces the DBStruct interface and DBStruct.getMetaData method for retrieving information about java.sql.Struct objects that are used for ROW parameters.
The following diagnosis and trace enhancements are added:
The recommended method for retrieval of data from DECFLOAT columns is to retrieve the values into java.math.BigDecimal variables. However, you cannot use the ResultSet.getBigDecimal or ResultSet.getObject method to retrieve the value NaN, Infinity, or -Infinity from a DECFLOAT column in a JDBC program, or retrieve a DECFLOAT column value into a java.math.BigDecimal variable in an SQLJ clause of an SQLJ program.
Error code -4231 is introduced to indicate that NaN, Infinity, or -Infinity was retrieved from a DECFLOAT column using the ResultSet.getBigDecimal or ResultSet.getObject method. You can test for -4231 in your applications, and retry data retrieval with the ResultSet.getDouble method.
The following Connection and DataSource properties are added:
The following global configuration properties are added:
In DB2 for Linux, UNIX, and Windows Version 9.7 Fix Pack 5, the following enhancements are available in version 3.63 or version 4.13 of the driver. Version 3.63 contains JDBC 3.0 or earlier functions. Version 4.13 contains JDBC 4.0 or later functions, and JDBC 3.0 or earlier functions.
IBM Data Server Driver for JDBC and SQLJ version 4.13 supports the following new JDBC 4.1 methods:
| Class | Method |
|---|---|
| java.sql.CallableStatement | getObject(int parameterIndex, |
getObject(java.lang.String parameterName, |
|
| java.sql.Connection | abort(java.util.concurrent.Executor executor) |
| setSchema((java.lang.String schema) | |
setNetworkTimeout(java.util.concurrent.Executor executor, |
|
| getSchema() | |
| getNetworkTimeout() | |
| java.sql.DatabaseMetaData | generatedKeyAlwaysReturned() |
getPseudoColumns (java.lang.String catalog, |
|
| java.sql.Driver | getParentLogger() |
| java.sql.Statement | abort(java.util.concurrent.Executor executor) |
| closeOnCompletion() | |
| isCloseOnCompletion() | |
| javax.sql.CommonDataSource | getParentLogger() |
IBM Data Server Driver for JDBC and SQLJ version 4.13 supports the following JDBC 4.1 changes to JDBC methods:
| Class | Method | Change |
|---|---|---|
| java.sql.DatabaseMetaData | getColumns | In JDBC 4.0 or earlier, the result set that getColumns returns contains a column named SCOPE_CATLOG. In JDBC 4.1 or later, the name of that column is SCOPE_CATALOG. |
IBM Data Server Driver for JDBC and SQLJ version 4.13 supports the following JDBC 4.1 changes to data type mappings for updating table columns:
| Java data type | Database data type |
|---|---|
| java.math.BigInteger | BIGINT |
| java.util.Date | CHAR, VARCHAR, DATE, TIME or TIMESTAMP |
| java.util.Calendar | CHAR, VARCHAR, DATE, TIME or TIMESTAMP |
IBM Data Server Driver for JDBC and SQLJ version 4.13 supports the following JDBC 4.1 escape syntax, which you can use to limit the number of rows that are retrieve from a table:
{limit integer}For example the excape clause in the following query tells JDBC to return at most 20 rows from the EMPLOYEE table:
stmt.executeQuery("SELECT EMPNO FROM EMPLOYEE {limit 20}");Circular tracing is introduced for the IBM Data Server Driver for JDBC and SQLJ. Circular tracing means that there are a fixed number of trace output data sets, and that each data set has a fixed size. New trace data overwrites old trace data when all data sets are full. Circular tracing is an alternative to sequential tracing, which results in trace output files that grow indefinitely.
IBM Data Server Driver for JDBC and SQLJ internal statement caching can improve the performance of Java database applications. Internal statement caching is introduced for connections that use the java.sql.DriverManager or com.ibm.db2.jcc.DB2SimpleDataSource interfaces. Previously, internal statement caching was available only for connections that used the javax.sql.ConnectionPoolDataSource or javax.sql.XADataSource interfaces.
In DB2 Version 9.7 Fix Pack 4, the IBM Data Server Driver for JDBC and SQLJ added support for IN, OUT, or INOUT parameters of types ROW or ARRAY of ROW in JDBC applications. In DB2 Version 9.7 Fix Pack 5, the IBM Data Server Driver for JDBC and SQLJ adds support for the following types of nesting:
The following Connection and DataSource properties are added:
The IBM Data Server Driver for JDBC and SQLJ uses the TCP/IP protocol to communicate with data servers. The keepAliveTimeout property is used to adjust the TCP/IP KeepAlive parameters on the client, to prevent potential failover issues caused by timeouts within the TCP/IP layer.
The following global configuration properties are added:
For connections to DB2 for z/OS, the following enhancement is added:
In DB2 for Linux, UNIX, and Windows Version 9.7 Fix Pack 6, the following enhancements are available in version 3.64 or version 4.14 of the driver. Version 3.64 contains JDBC 3.0 or earlier functions. Version 4.14 contains JDBC 4.0 or later functions, and JDBC 3.0 or earlier functions.
For connections to DB2 for z/OS, the following enhancements are added:
The following Connection and DataSource properties are added:
In DB2 for Linux, UNIX, and Windows Version 9.7 Fix Pack 7, the following enhancements are available in version 3.65 or version 4.15 of the driver. Version 3.65 contains JDBC 3.0 or earlier functions. Version 4.15 contains JDBC 4.0 or later functions, and JDBC 3.0 or earlier functions.
For connections to DB2 for z/OS, the following enhancements are added:
The following Connection and DataSource properties are added: