Enhancements to IBM Toolbox for Java JDBC support for IBM i 6.1

Many additions were made to JDBC support in IBM® i 6.1.

The enhancements to IBM Toolbox for Java™ JDBC support are detailed in the following sections:

Support for JDBC 4.0

Support for the JDBC 4.0 API with Java SE Version 6 is available using one of the following JAR files:
  • /QIBM/ProdData/HTTP/Public/jt400/lib/java6/jt400.jar
  • /QIBM/ProdData/OS400/jt400/lib/java6/jt400Native.jar

Query storage limit

You can use the query storage limit property to limit the storage used by a query. This property compares the storage limit you specify to the estimated storage usage of the query. If the estimated storage usage exceeds the specified storage limit, the query is not allowed to run.

Beginning in V6R1, the IBM Toolbox for Java JDBC driver will add the following methods to the AS400JDBCDataSource class:

        setQueryStorageLimit()
        public void setQueryStorageLimit(int limit);
Specifies the query storage limit value to be used when statements in a connection are executed. Valid values are -1 to 2 147 352 578. The default is value -1, indicating a special value of *NOMAX.

        getQueryStorageLimit()
        public int getQueryStorageLimit()
Returns the query storage limit value used when statements in a connection are executed. The default value is -1, indicating a special value of *NOMAX.

Systems that run a release of IBM i prior to V6R1 will ignore the getQueryStorageLimit property.

Decimal float (DECFLOAT) data type

The decimal float (DECFLOAT) inherits favorable properties from both float and decimal data types. Data values of DECFLOAT are stored such that trailing zeros are significant: for example, 2.0 and 2.00 are different binary representations. In SQL comparisons, however, the values are treated as equal.

The IBM Toolbox for Java JDBC driver has added the following methods to the AS400JDBCDataSource class:

        setDecfloatRoundingMode()
        public void setDecfloatRoundingMode(int String mode)
Specifies the rounding mode to be used for DECFLOAT numbers.

        getDecfloatRoundingMode()
        public intString getDecfloatRoundingMode()
Returns the rounding mode to be used for DECFLOAT numbers.

Valid values for these methods include:
half even (default value)
Round to nearest digit. If equidistant between two digits, round to the nearest even digit. The numeric value of this constant is 0.
half up
Round to nearest digit. If equidistant between two digits, round up. The numeric value of this constant is 1.
down
Round to nearest lower digit. The numeric value of this constant is 2.
ceiling
Round towards positive infinity. The numeric value of this constant is 3.
floor
Round towards negative infinity. The numeric value of this constant is 4.
half down
Round to the nearest digit. If equidistant between two digits, round down. The numeric value of this constant is 5.
up
Round to nearest higher digit. The numeric value of this constant is 6.

Passing the client type and application name to the server

Web applications need a way to pass end-user-client information to the database server so that more detailed information may be logged. The IBM Toolbox for Java JDBC driver allows an application to override this information by calling the following java.sql.Connection.setClientInfo() methods:

void AS400JDBCConnection.setClientInfo(java.lang.String name,java.lang.String value)
This method sets the value of the client info property specified by name to the value specified by value. See the DatabaseMetadata.getClientInfoProperties method description for the client info properties supported by the IBM Toolbox for Java JDBC driver.

void AS400JDBCConnection.setClientInfo(java.util.Properties properties)
This method sets the value of the connection's client info properties. The Properties object contains the names and values of the client info properties to be set. The set of client info properties contained in the properties list replaces the current set of client info properties on the connection. If a property that is currently set on the connection is not present in the properties list, that property is cleared. Specifying an empty properties list will clear all of the properties on the connection.

String AS400JDBCConnection.getClientInfo(java.lang.String name)
This method returns the value of the client info property specified by name. This method may return null if the specified client info property has not been set and does not have a default value.

Properties AS400JDBCConnection.getClientInfo()
This method returns a list containing the name and current value of each client info property supported by the driver. The value of a client info property may be null if the property has not been set and does not have a default value.

ResultSet AS400JDBCDatabaseMetaData.getClientInfoProperties()
This method retrieves a list of the client info properties that the driver supports. The IBM Toolbox for Java JDBC driver returns a result set with the following information:
Table 1. Result set for getClientInfoProperties
Name Maximum length Default value Description
ApplicationName 255 "" The name of the application currently utilizing the connection.
ClientAccounting 255 "" Accounting information.
ClientHostname 255 "" The hostname of the computer the application using the connection is running on.
ClientProgramID 255 "" The client program identification.
ClientUser 255 "" The name of the user that the application using the connection is performing work for. This may not be the same as the user name that was used in establishing the connection.

Maximum length of cursor names extended

Beginning in V6R1, the maximum length of cursors will be 128 characters. The previous maximum length was 8 characters. An application can set the name of a cursor by calling the java.sql.Statement.setCursorName() method.

Generated key support

In past releases, only one row of information could be returned from a multiple row insert operation. Beginning in V6R1, you will be able to access more information about a multiple row insert operation. This will allow you to retrieve generated column information such as ROWID, identity column, sequence, or generated expressions. Generated key support will be implemented in the following IBM Toolbox for Java JDBC methods:
  • AS400JDBCStatement.getGeneratedKeys()
  • AS400JDBCStatement.execute(String sql, int autoGeneratedKeys)
  • AS400JDBCStatement.execute (String sql, int[] columnIndexes)
  • AS400JDBCStatement.execute (String sql, String[] columnNames)
  • AS400JDBCStatement.executeUpdate(String sql, int[] autoGeneratedKeys)
  • AS400JDBCStatement.executeUpdate (String sql, int[] columnIndexes)
  • AS400JDBCStatement.executeUpdate (String sql, String[] columnNames)
  • AS400JDBCConnection.prepareStatement(String sql, int autoGeneratedKeys)
  • AS400JDBCConnection.prepareStatement(String sql, int[] columnIndexes)
  • AS400JDBCConnection.prepareStatement(String sql, String[] columnNames)

Improved default value support

Beginning in V6R1, The IBM Toolbox for Java JDBC driver will return a column's default value as a string through the DatabaseMetaData.getColumns() method. If the default value is null, a string with the value 'NULL' will be returned.

Increased maximum in GROUP BY clause

The new value for DatabaseMetaData getMaxColumnsInGroupBy( ) is 8000.

Batch update support

The improved batch update support in V6R1 will provide better information to you when you are running a multiple-row batched insert statement. There will be a field in the diagnostics area and SQLCA that will contain the count of successful statements so that you can better determine the location of any errors.

The IBM Toolbox for Java JDBC driver will use this information when creating a java.sql.BatchUpdateException. AS400JDBCStatement and AS400JDBCPreparedStatement will also use this information to return the correct information back from the executeBatch() method.