DB2 10.5 for Linux, UNIX, and Windows

JDBC and SQLJ support has been enhanced

The IBM® Data Server Driver for JDBC and SQLJ contains a number of major enhancements for Version 10.5.

The following enhancements are available in versions of the IBM Data Server Driver for JDBC and SQLJ that are shipped with DB2® for Linux, UNIX, and Windows.

Version 10.5 enhancements
Fix Pack 2 enhancements
DB2 Cancun Release 10.5.0.4 enhancements
Fix Pack 5 enhancements
Fix Pack 7 enhancements

DB2 V10.5 enhancements

The following IBM Data Server Driver for JDBC and SQLJ enhancements are available in version 3.66 or version 4.16, or later.

Support for retrieval of data from character-based columns

DB2 for Linux, UNIX, and Windows introduces support for an explicit string unit attribute in the definitions of string columns and parameters. In a Unicode database, columns with CHAR, VARCHAR, CLOB, GRAPHIC, VARGRAPHIC, and DBCLOB data types can be defined with a string unit.

The IBM Data Server Driver for JDBC and SQLJ provides corresponding support that allows you to access columns that are defined with string units in your applications:

New methods DB2ParameterMetaData.getMaxStringUnitBits and DB2ResultSetMetaData.getMaxStringUnitBits, which retrieve the maximum number of bits in a string unit for single-byte and double-byte character data types. For a character column that is defined with OCTETS, these methods return a value of 8. For a character column that is defined with CODEUNITS16, the methods return a value of 16. For a character column that is defined with CODEUNITS32, the methods return a value of 32.
The java.sql.DatabaseMetaData and java.sql.ParameterMetaData methods return ResultSet columns with information about string unit attributes. Those ResultSet columns are now populated for columns or parameters with string unit attributes.

Enhancements to global transport pool monitoring

The following additional information can be collected about a DB2 for Linux, UNIX, and Windows DB2 pureScale® instance and its members, or a DB2 for z/OS® data sharing group and its members:

Information for the entire data sharing group:
- The maximum number of pool objects that were created since a global transport pool was created.
- The number of times that the maxTransportWaitTime value was reached since a global transport pool was created.
- The number of times that the group IP address was used since a global transport pool was created.
- The number of times that a member of the data sharing group had a connection timeout when a new connection was established.
Information for each member of the data sharing group:
- The maximum number of transports that were in use for the member since a global transport pool was created.
- The maximum number of transports that allocated to the member since a global transport pool was created.
- The number of times that the memberConnectTimeout value was reached for the member since a global transport pool was created.
- The number of times that a read timeout occurred for the member since a global transport pool was created.

New and changed properties

The following Connection and DataSource properties are changed in V10.5:

maxRetriesForClientReroute: For connections to DB2 for z/OS, when enableSysplexWLB is set to true, the default for maxRetriesForClientReroute is now 1. In addition, the meaning of an attempt to access the data sharing group has changed, so the meaning of a retry has changed. Formerly, an attempt to connect was an attempt to connect to one data sharing member. With V10.5, a single attempt to access the data sharing group is one attempt to connect to each member of the data sharing group, except for the failed member, plus an attempt connect to the group IP address.

The following global configuration properties are new in V10.5:

db2.jcc.sslConnection: Specifies whether the IBM Data Server Driver for JDBC and SQLJ uses an SSL socket to connect to the data source. This property provides the default for Connection or DataSource property sslConnection.
db2.jcc.override.sslConnection: Specifies whether the IBM Data Server Driver for JDBC and SQLJ uses an SSL socket to connect to the data source. This property overrides the Connection or DataSource property sslConnection.
db2.jcc.sslTrustStoreLocation: Specifies the name of the Java truststore on the client that contains the server certificate for an SSL connection. This property provides the default for Connection or DataSource property sslTrustStoreConnection.
db2.jcc.override.sslTrustStoreLocation: Specifies the name of the Java truststore on the client that contains the server certificate for an SSL connection. This property overrides the Connection or DataSource property sslTrustStoreConnection.
db2.jcc.sslTrustStorePassword: Specifies the password for the Java truststore on the client that contains the server certificate for an SSL connection. This property provides the default for Connection or DataSource property sslTrustStorePassword.
db2.jcc.override.sslTrustStorePassword: Specifies the password for the Java truststore on the client that contains the server certificate for an SSL connection. This property overrides the Connection or DataSource property sslTrustStorePassword.

Enhancements for connections to DB2 for z/OS data servers

IBM Data Server Driver for JDBC and SQLJ versions 3.66 and 4.16 provide the following enhancements for connections to DB2 for z/OS data servers. For these versions of the driver to work properly with DB2 for z/OS Version 10, DB2 for z/OS PTF UK91146 for APAR PM79161 needs to be installed. If the PTF is not installed, connections fail with SQL error -1224. Contact your DB2 for z/OS system administrator to ensure that the PTF is installed.

Automatic client reroute enhancements: Non-seamless processing of automatic client reroute for DB2 for z/OS connections is modified to operate more efficiently. When a connection is re-established during non-seamless automatic client reroute, transport allocation is postponed until it is needed.

FP2: Driver enhancements

In DB2 for Linux, UNIX, and Windows V10.5 Fix Pack 2, the following enhancements are available in version 3.67 or version 4.17 of the driver. Version 3.67 contains JDBC 3.0 or earlier functions. Version 4.17 contains JDBC 4.0 or later functions, and JDBC 3.0 or earlier functions.

FP2: New and changed properties

The following Connection and DataSource properties are new in V10.5 Fix Pack 2:

enableTimeoutForCursors: For DatabaseMetaData or ResultSet methods that use Statement objects in their implementations, specifies whether the commandTimeout and queryTimeoutInterruptProcessing mode property values control the timeout behavior for those Statement objects.
enableTimeoutForCursors applies only to connections to DB2 for Linux, UNIX, and Windows data servers.

FP2: Enhanced support for timeout properties for DatabaseMetaData and ResultSet methods

Certain DatabaseMetaData and ResultSet methods invoke Statement methods as part of their implementations. Previously, the commandTimeout and queryTimeoutInterruptProcessingMode property values did not affect those Statement methods. The IBM Data Server Driver for JDBC and SQLJ is enhanced to let you choose whether the commandTimeout and queryTimeoutInterruptProcessingMode property values apply to Statement methods that are used in the implementation of DatabaseMetaData and ResultSet methods by setting new Connection or DataSource property enableTimeoutForCursors.

FP2: Smaller number of retries for seamless failover

Previously, if a transaction was eligible for seamless automatic client reroute, attempts at seamless failover could occur up to 10 times, which resulted in wasted resources. Seamless automatic client reroute processing is changed to try seamless failover only once. If seamless failover occurs, and the data sharing member or DB2 pureScale instance to which the connection fails over goes down, the connection fails over to another data sharing member or DB2 pureScale instance, but the failover is non-seamless.

FP2: Enhancements for connections to DB2 for z/OS data servers

Support for setting global variables

Besides setting global variables with the SQL SET assignment-statement statement, you can set DB2 for z/OS global variable values in a JDBC or SQLJ application using one of the following ways:

Use one of the DB2DataSource.void setGlobalSessionVariables methods:
```
public
void setGlobalSessionVariables (Properties prop)
public void setGlobalSessionVariables (String string)
```
The input to these methods are a java.util.Properties object or a String that contains the global variable and value pairs.

In a Connection URL, using this format:

globalSessionVariables=variable-value-pair,…variable-value-pair

Support for global variables with Sysplex workload balancing

With previous versions of the IBM Data Server Driver for JDBC and SQLJ, if an SQL statement referenced a global variable, Sysplex workload balancing was disabled. Now, Sysplex workload balancing continues to be enabled when applications that use global variables run.

Client info property improvements

The sizes of the ApplicationName, ClientAccoutingInformation, ClientHostname, and ClientUser client info properties have increased to match larger values for the corresponding special registers in DB2 for z/OS Version 11 new-function mode. With earlier versions of the driver, and before DB2 for z/OS Version 11 new-function mode, the driver truncates the client info property values to the maximum length of the corresponding special registers.

In addition, the new client info property ClientCorrelationToken is introduced.

Implicit commit for stored procedure work

When a Java application that uses IBM Data Server Driver for JDBC and SQLJ type 4 connectivity to DB2 for z/OS Version 11 has autocommit enabled, and that application calls a stored procedure, the IBM Data Server Driver for JDBC and SQLJ indicates to the DB2 data server that it should automatically commit the work when the call is complete. Automatic committing of stored procedure work can improve performance by reducing the number of times that the driver needs to send and receive messages on the network to request the commit operation.

Stored procedure calls with array parameters

Java applications that use IBM Data Server Driver for JDBC and SQLJ type 4 connectivity to DB2 for z/OS Version 11 can now call stored procedures with ARRAY parameters.

Support for DB2 for z/OS application compatibility

Starting with DB2 for z/OS Version 11, you can choose whether to use SQL behavior that is available in new-function mode of the new DB2 for z/OS version, or continue to use SQL behavior from the previous version. This feature is called application compatibility. You can set the application compatibility level for an entire DB2 for z/OS subsystem, or for individual application packages.

The IBM Data Server Driver for JDBC and SQLJ provides several ways to set the application static SQLJ applications or for dynamic JDBC or SQLJ applications.

Enhanced DB2Binder support

DB2Binder support for connections to DB2 for z/OS data servers has been enhanced in the following ways:

The -bindoptions parameter now applies to binding IBM Data Server Driver for JDBC and SQLJ packages, as well as to user packages.
The set of bind options that is supported for rebinding user packages (-action rebind) is expanded to match the set of bind options that is supported for binding of driver packages (-action add or -action replace).

Support for setting CURRENT QUERY ACCELERATION

Besides setting the DB2 for z/OS CURRENT QUERY ACCELERATION special register with the SQL SET assignment-statement statement, you can set this special register in a JDBC or SQLJ application in the following way:

In a Connection URL, using this format:
```
queryAcceleration=value
```

DB2 Cancun Release 10.5.0.4: Driver enhancements

In DB2 for Linux, UNIX, and Windows DB2 Cancun Release 10.5.0.4 (FP4), the following enhancements are available in version 3.68 or version 4.18 of the driver. Version 3.68 contains JDBC 3.0 or earlier functions. Version 4.18 contains JDBC 4.0 or later functions, and JDBC 3.0 or earlier functions.

DB2 Cancun Release 10.5.0.4: Alternate group support

Alternate group support allows the IBM Data Server Driver for JDBC and SQLJ to move an application workload to a DB2 for Linux, UNIX, and Windows alternative DB2 pureScale instance when the primary group is unavailable.

You enable alternate group support by providing the addresses of alternate groups in Connection or DataSource properties.

The Connection or DataSource properties are:

alternateGroupServerName
alternateGroupPortNumber
alternateGroupDatabaseName

In addition, you can enable or disable seamless failover behavior for alternate group support by setting the enableAlternateGroupSeamlessACR Connection or DataSource property.

Alternate group support includes support for high availability disaster recovery (HADR). When an automatic client reroute attempt cycles through all alternate groups, a connection failure does not occur when the last group among the alternate groups has a failure. Instead, the driver retries the first group in the alternate group list.

DB2 Cancun Release 10.5.0.4: Methods for converting ResultSet objects to JSON format

You can query a relational table, and then convert the ResultSet that you retrieve to a com.ibm.db2.jcc.json.DB2JSONResultSet object, which holds the data in JSON format. After you create the DB2JSONResultSet object, you can use methods on that object to retrieve the JSON documents or JSON snippets.

The DB2JSONResultSet methods are:

Method name	Function of the method
close	Closes a DB2JSONResultSet object
getAsciiStream	Returns the contents of the DB2JSONResultSet object as a java.io.InputStream object
getCharacterStream	Returns a java.io.Reader object on which applications can call methods to read the JSON document incrementally
getCurrentRow	Returns the current row of the DB2JSONResultSet as a JSON snippet
next	Moves the cursor to the next row in the DB2JSONResultSet object
toJSONString	Returns the rest of the DB2JSONResultSet object, starting with the current row, as a String

DB2 Cancun Release 10.5.0.4: Improved implementation of ResultSet.getXXX for DECIMAL columns

The IBM Data Server Driver for JDBC and SQLJ implementation of ResultSet.getXXX methods that return numeric values has been improved. As a result, you might experience better performance when you retrieve DECIMAL column data from a table.

DB2 Cancun Release 10.5.0.4: Enhancements that provide more DB2 Connect client license information

If you use a DB2 Connect client license for connections to DB2 for z/OS or DB2 for i, you can use IBM Data Server Driver for JDBC and SQLJ enhancements to obtain the following information about the client license:

License expiration date
Obtain this information in one of the following ways:
- Invoke the com.ibm.db2.DB2Jcc utility with the -expirationDate option.
- Invoke method DB2Version.getExpirationDateForDriver.
License type
Obtain this information in one of the following ways:
- Invoke the com.ibm.db2.DB2Jcc utility with the -expirationDateWithLicenseType option.
- Invoke method DB2Version.getExpirationDateForDriverWithLicenseType.
DB2 Connect version that is valid with the client license:
Obtain this information in one of the following ways:
- Invoke the com.ibm.db2.DB2Jcc utility with the -db2ConnectVersion option.
- Invoke method DB2Version.getDB2ConnectLicenseVersion.

DB2 Cancun Release 10.5.0.4: Serviceability enhancements for connections to DB2 for z/OS data sharing groups

IBM Data Server Driver for JDBC and SQLJ versions 3.68 and 4.18 provide the following enhancements for connections to DB2 for z/OS data sharing groups.

More data sharing environment information: New value TRACE_SYSPLEX (x'80000') for the traceLevel Connection and DataSource property, and the db2.jcc.traceLevel and db2.jcc.override.traceLevel configuration properties enables gathering of statistics about the data sharing environment.

FP5: Driver enhancements

In DB2 for Linux, UNIX, and Windows V10.5 (FP5), the following enhancements are available in version 3.69 or version 4.19 of the driver. Version 3.69 contains JDBC 3.0 or earlier functions. Version 4.19 contains JDBC 4.0 or later functions, and JDBC 3.0 or earlier functions.

FP5: New and changed properties

The following Connection and DataSource properties are new in V10.5 Fix Pack 5:

diagLevelExceptionCode: Specifies whether or not to collect detailed diagnostic information for SQL errors.
diagLevelExceptionCode applies only to connections to DB2 for z/OS data servers.
dumpDiagLevel: Specifies whether to collect diagnostic information for all reason codes or only critical reason codes.
dumpDiagLevel applies only to connections to DB2 for z/OS data servers.
sslCertLocation: Specifies that an application can configure the location of a trusted certificate file.
tracePolling: The default for db2.jcc.tracePolling property is false before version 3.69 of the driver, and true is the default for 3.69 and later.

FP5: Enhancements to COMMIT on connection close

The connectionCloseWithInFlightTransaction property is enhanced with a new DB2BaseDataSource.CONNECTION_CLOSE_WITH_COMMIT (3) option, which specifies that when a connection is closed in the middle of a transaction, the transaction is committed, and no SQLException is thrown. This option is used only when migrating applications to DB2.

FP7: Compatibility with Java 8 runtime environment

The IBM Data Server Driver for JDBC and SQLJ is now compatible with Java 8 Runtime Environments. Refer to the IBM Data Server Driver for JDBC and SQLJ fix pack 7 APARs for known Java 8 compatibility issues that have been fixed.

FP7: OSGi compliant bundling of driver jars

In DB2 for Linux, UNIX, and Windows V10.5 (FP7), OSGi compliant DB2 JCC drivers are available. This enables the following functionality:

OSGi management of the DB2 drivers.
More accurate dependency resolution of the drivers at install time.
Deployment of multiple versions of the driver into the same OSGi.

FP7: Serviceability enhancements

DB2 for Linux, UNIX, and Windows V10.5 (FP7) and z/OS provide the following serviceability enhancements:

Circular tracing for JCC Serviceability dumps. Event data is now dumped into two rotating files for each JVM. Files now reach a size of maximum 2MB before being recycled.
You can configure SQL codes with comma separated error codes.
You can suffix an 'n' or 'p' to SQL codes for negative and positive SQL codes respectively.
You must configure SQL Warnings with a "+" sign prefix or 'p' suffix.
Integer constants related to logging diagnostic information for error codes in JCC has been discontinued.

FP7: db2sqljcustomize enhancement for -hostLangJAVA option

You can distinguish which language a package has been created with using the genDBRM hostLangJAVA clause.

FP7: New and changed properties

useCachedCursor property

You will obtain optimal performance when the property is left unconfigured. Explicitly set the property to “true” or “false” if you desire older driver behaviour.