JDBC driver connection properties

This table contains valid JDBC driver connection properties, their values, and their descriptions.

Property Values Meaning
access all, read call, read only This value can be used to restrict the type of operations that can be done with a specific connection. The default value is all and basically means that the connection has full access to the JDBC API. The read call value allows the connection to do only queries and call stored procedures. An attempt to update the database through an SQL statement is stopped. The read only value can be used to restrict a connection to only queries. Stored procedure calls and update statements are stopped.
auto commit true, false This value is used to set the auto commit setting of the connection. The default value is true unless the transaction isolation property has been set to a value other than none. In that case, the default value is false.
batch style 2.0, 2.1 The JDBC 2.1 specification defines a second method for how exceptions in a batch update can be handled. The driver can comply with either of these. The default is to work as defined in the JDBC 2.0 specification.
block size 0, 8, 16, 32, 64, 128, 256, 512 This is the number of rows that are fetched at a time for a result set. For typical forward-only processing of a result set, a block of this size is obtained. Then the database is not accessed because each row is processed by your application. The database requests another block of data only when the end of the block is reached.

This value is only used if the blocking enabled property is set to true.

Setting the block size property to 0 has the same effect as setting the blocking enabled property to false.

The default is to use blocking with a block size of 32. This is a fairly arbitrary decision and the default could change in the future.

Blocking is not used on scrollable result sets.
blocking enabled true, false This value is used to determine if the connection uses blocking on result set row retrieval. Blocking can significantly improve the performance of processing result sets.

By default, this property is set to true.
commit hold false, true This value specifies if "commit hold" is used when the connection.commit() method is called. When "commit hold" is used, cursors and other database resources are not closed or freed when commit is called.

The default value is false.
concurrent access resolution 1, 2, 3 This property specifies whether "currently committed" access is used on the connection. The following values are possible:
1
"currently committed" will be used
2
"wait for outcome" will be used
3
"skip locks" will be used
The default value is 2.
cursor hold true, false This value specifies if result sets remain open when a transaction is committed. A value of true means that an application is able to access its open result sets after commit is called. A value of false means that commit closes any open cursors under the connection.

By default, this property is set to true.

This value property serves as a default value for all result sets made for the connection. With cursor holdability support added in JDBC 3.0, this default is simply replaced if an application specifies a different holdability later.

If you are migrating to JDBC 3.0 from an earlier version, be aware that cursor holdability support was not added until JDBC 3.0. In earlier versions, the default value of "true" was sent at connect time, but it was not yet recognized by the JVM. Therefore, the cursor hold property will not impact database functionality until JDBC 3.0.
cursor sensitivity asensitive, sensitive Specifies the cursor sensitivity used by ResultSet.TYPE_SCROLL_SENSITIVE cursors. By default, the native JDBC driver creates asensitive cursors for ResultSet.TYPE_SCROLL_SENSITIVE cursors.
data truncation true, false This value specifies if truncation of character data should cause warnings and exceptions to be generated (true) or if the data should just be silently truncated (false). If the default is true, data truncation of character fields are honored.
date format julian, mdy, dmy, ymd, usa, iso, eur, jis This property allows you to change how dates are formatted.
date separator /(slash), -(dash), .(period), ,(comma), b This property allows you to change what the date separator is. This is only valid in combination with some of the dateFormat values (according to system rules).
decfloat rounding mode round half even, round half up, round down, round ceiling, round floor, round half down, round up, round half even This property specifies the rounding mode to be used by decimal float operations. The default value is round half even.
decimal separator .(period), ,(comma) This property allows you to change what the decimal separator is.
direct map true, false This property specifies if database direct map optimizations will be used when retrieving result sets from the database. The default value is true.
do escape processing true, false This property sets a flag for if statements under the connection must do escape processing. Using escape processing is a way to code your SQL statements so that they are generic and similar for all platforms, but then the database reads the escape clauses and substitutes the proper system specific version for the user.

This is good, except that it forces extra work on the system. In the case where you know you are only using SQL statements that already contain valid IBM® i SQL syntax, it is recommended that this value be set to false to increase performance.

The default value for this property is true, as it must be compliant with the JDBC specification (that is, escape processing is active by default).

This value is added due to a shortcoming of the JDBC specification. You can only set escape processing to off in the Statement class. That works well if you are dealing with simple statements. You create your statement, turn off escape processing, and start processing statements. However, in the case of prepared statements and callable statements, this scheme does not work. You supply the SQL statement at the time that the prepared or callable statement is constructed and it does not change after that. So the statement is prepared up front and changing the escape processing after that is meaningless. Having this connection property allows a way to get around the extra overhead.
errors basic, full This property allows the full system second-level error text to be returned in SQLException object messages. The default is basic which returns only the standard message text.
extended metadata true, false The property specifies if the driver should request extended metadata from the database. Setting this property to true increases the accuracy of the information returned from the following ResultSetMetaData methods:
  • getColumnLabel(int)
  • getSchemaName(int)
  • getTableName(int)
  • isReadOnly(int)
  • isSearchable(int)
  • isWriteable(int)
Setting this property to true may slow performance because it requires retrieving more information from the database.
ignore warnings A command separated list of SQL states that should be ignored. By default, the native JDBC driver will internally create a java.sql.SQLWarning object for each warning returned by the database. This property specifies a list of SQL states for which the native JDBC driver should not create warning objects. For example, a warning with the SQLSTATE 0100C is created every time a result set is returned from a stored procedure. This warning can be safely ignored to improve the performance of applications that call stored procedures.
libraries A space-separated list of libraries. (A list of libraries can also be separated by colons or commas.) This property allows a list of libraries to be placed into the server job's library list or a specific default library to be set.

The naming property affects how this property works. In the default case, where naming is set to sql, JDBC works like ODBC. The library list has no effect on how the connection processes. There is a default library for all unqualified tables. By default, that library has the same name as the user profile that is connected. If the libraries property is specified, the first library in the list becomes the default library. If a default library is specified on the connection URL (as in jdbc:db2:*local/mylibrary), that overrides any value in this property.

In the case where naming is set system, each of the libraries specified for this property is added to the user portion of the library list and the library list is searched to resolve unqualified table references.
Start of changelob block sizeEnd of change Start of change4096,65536,262144,1048576,4194304,16777216End of change Start of changeThis property specifies the number of bytes read from the InputStream when processing a block of data. The default value of this connection property is 1048576 bytes. Lower values will typically cause slower performance when processing lob data.End of change
lob threshold Any value under 500000 This property tells the driver to place the actual values into the result set storage instead of locators for lob columns if the lob column is smaller than the threshold size. This property acts against the column size, not the lob data size itself. For example, if the lob column is defined to hold up to 1 MB for each lob, but all the column values are under 500 KB, locators are still used.

Note that the size limit is set as it is to allow blocks of data to be fetched without danger of not always growing data blocks larger than the 16 MB maximum allocation size. With large result sets, it is still easy to exceed this limit, which causes fetches to fail. Care must be taken in how the block size property and this property interact with the size of a data block.

The default is 0. Locators are always used for lob data.
maximum precision 31, 63 This value specifies the maximum precision used for decimal and numeric types. The default value is 31.
maximum scale 0-63 This value specifies the maximum scale (number of decimal positions to the right of the decimal point) that is returned used by decimal and numeric types. The value can range from 0 to the maximum precision. The default value is 31.
minimum divide scale 0-9 This value specifies the minimum divide scale (number of decimal positions to the right of the decimal point) that is returned for both intermediary and result data types. The value can range from 0 to 9, not to exceed the maximum scale. If 0 is specified, minimum divide scale is not used. The default value is 0.
naming sql, system This property allows you to use either the traditional IBM i naming syntax or the standard SQL naming syntax. System naming means that you use a /(slash) character to separate collection and table values, and SQL naming means that you use a .(period) character to separate the values.

The setting of this value has ramifications for what the default library is also. See the libraries property above for further information about this.

The default is to use SQL naming.
password anything This property allows for a password to be specified for the connection. This property does not work correctly without also specifying the user property. These properties allow for connections to be made to the database as a user other than the one that is running the IBM i job.

Specifying the user and password properties have the same effect as using the connection method with the signature getConnection(String url, String userId, String password).
prefetch true, false This property specifies if the driver fetches the first data for a result set immediately after processing or wait until the data is requested. If the default is true, data is prefetched.

For applications using the Native JDBC driver, this is rarely an issue. The property exists primarily for internal use with Java™ stored procedures and user-defined functions where it is important that the database engine does not fetch any data from result sets on your behalf before you request it.
qaqqinilib library name This property specifies the library that contains the qaqqini file to use. A qaqqini file contains all of the attributes that can potentially impact the performance of the Db2® for i database engine.
query optimize goal 1, 2 This property specifies the goal the server should use with optimization of queries. This setting corresponds to the server's QAQQINI option called OPTIMIZATION_GOAL. The following values are possible:
1
Optimize query for first block of data (*FIRSTIO)
2
Optimize query for entire result set (*ALLIO)
The default value is 2.
reuse objects true, false This property specifies if the driver attempts to reuse some types of objects after you close them. This is a performance enhancement. The default is true.
servermode subsystem *SAME, subsystem name This property specifies the subsystem in which the associated QSQSRVR jobs will run. The default behavior is to have the jobs run in the QSYSWRK subsystem. If the value *SAME is used, then the QSQSRVR jobs will run in the same subsystem as the job using the native JDBC driver.
In order for a QSQSRVR job to run in a different subsystem, a QSQSRVR prestart job entry for that subsystem must exist. The following commands can be used to create a QSQSRVR prestart job entry. 
ENDSBS sbs

ADDPJE SBSD(library/sbsd) 
  PGM(QSYS/QSQSRVR) STRJOBS(*YES) INLJOBS(x) 
  THRESHOLD(y) ADLJOBS(z) MAXUSE(*NOMAX)

STRSBS sbs

Where sbs is the subsystem, library is the library in which the subsystem description sbsd is located in, and x, y, and z are numeric values for the corresponding parameters on the Add Prestart Job Entry (ADDPJE) command.

If a prestart job entry does not exist for QSQSRVR within the subsystem, the QSQSRVR job will utilize a Batch Immediate job (BCI) instead of a Prestart job (PJ). This Batch Immediate job typically runs in the same subsystem as the job using the native JDBC driver.
time format hms, usa, iso, eur, jis This property allows you to change how time values are formatted.
time separator :(colon), .(period), ,(comma), b This property allows you to change what the time separator is. This is only valid in combination with some of the timeFormat values (according to system rules).
trace true, false This property allows for turning on tracing of the connection. It can be used as a simple debugging aide.

The default value is false, which does not use tracing.
transaction isolation none, read committed, read uncommitted, repeatable read, serializable This property allows you to set the transaction isolation level for the connection. There is no difference between setting this property to a specific level and specifying a level on the setTransactionIsolation method in the Connection interface.

The default value for this property is none, as JDBC defaults to auto-commit mode.
translate binary true, false This property can be used to force the JDBC driver to treat binary and varbinary data values as if they were char and varchar data values. When binary data is to be treated the same as character data, the CCSID of the job will be used as the CCSID of the data.

The default for this property is false, where binary data is not treated the same as character data.
translate hex binary, character This value is used to select the data type used by hex constants in SQL expression. The binary setting indicates that hex constants will use the BINARY data type. The character setting indicates that hex contants will use the CHARACTER FOR BIT DATA data type. The default setting is character.
use block insert true, false This property allows the native JDBC driver to go into a block insert mode for inserting blocks of data into the database. This is an optimized version of the batch update. This optimized mode can only be used in applications that ensure that they do not break certain system constraints or data insert failures and potentially corrupt data.

Applications that turn on this property only connect to the local system when attempting to perform batched updates. They do use DRDA to establish remote connections because blocked insert cannot be managed over DRDA.

Applications must also ensure that PreparedStatements with an SQL insert statement and a values clause make all the insert values parameters. No constants are permitted in the values list. This is a requirement of the blocked insert engine of the system.

The default is false.
user anything This property allows for a user ID to be specified for the connection. This property does not work correctly without also specifying the password property. These properties allow for connections to be made to the database as a user other than the one that is running the IBM i job.

Specifying the user and password properties has the same effect as using the connection method with the signature getConnection(String url, String userId, String password).