IBM Toolbox for Java JDBC properties
Many properties can be specified when connecting to a server database using JDBC. All properties are optional and can be specified either as part of the URL or in a java.util.Properties object. If a property is set in both the URL and a Properties object, the value in the URL will be used.
The following tables list the different connection properties that are recognized by this driver. Some of these properties affect performance and others are server job attributes. The tables organize the properties into the following categories:
General properties are system attributes that specify the user, password, and whether a prompt is necessary to connect to the server.
|"password"||Specifies the password for connecting to the server. If none is specified, then the user will be prompted, unless the "prompt" property is set to "false", in which case an attempt to connect will fail.||no||system password||(user will be prompted)|
|"prompt"||Specifies whether the user is prompted if a user name or password is needed to connect to the server. If a connection cannot be made without prompting the user, and this property is set to "false", then an attempt to connect will fail.||no||
|"user"||Specifies the user name for connecting to the server. If none is specified, then the user will be prompted, unless the "prompt" property is set to "false", in which case an attempt to connect will fail.||no||server user||(user will be prompted)|
Server properties specify attributes that govern transactions, libraries, and databases.
|"auto commit"||Specifies whether auto-commit mode is the default
connection mode for new connections. Calling AS400JDBCConnection.setAutoCommit()
will override this property on a per-connection basis.
Note: In order to use transaction isolation levels other than *NONE when using auto-commit mode, the property "true autocommit" needs to be set to true.
|"concurrent access resolution"||Specifies whether "currently committed" access is used on the connection. A value of 1 indicates that "currently committed" will be used. A value of 2 indicates that "wait for outcome" will be used. A value of 3 indicates that "skip locks" will be used.||no||
|"cursor hold"||Specifies whether to hold the cursor across transactions. If this property is set to "true", cursors are not closed when a transaction is committed or rolled back. All resources acquired during the unit of work are held, but locks on specific rows and objects implicitly acquired during the unit of work are released.||no||
|"cursor sensitivity"||Specifies the cursor sensitivity to request from the database.
The behavior depends on the resultSetType:
|"database name"||Specifies the database to use for the connection
to an independent auxiliary storage pool (ASP).
This property applies only when connecting to an IBM® i server. When you specify
a database name, the name must exist in the relational database directory
on the server and correspond to either an independent ASP or the system
default database. The following criteria determine which database
||no||Database name "*SYSBAS"||The database name specified in the job description for the user profile is used. When the job description does not specify a database name, the system default database is used.|
|"decfloat rounding mode"||Specifies the rounding mode to use when working with the decfloat data type. This property is ignored when connecting to systems running IBM i 5.4 and earlier.||no||
Specifies one or more libraries that you want to add to or replace the library list of the server job, and optionally sets the default SQL schema (default library). Note that libraries cannot be longer than 10 characters in length. You must use the SET PATH SQL statement if you have libraries longer than 10 characters.
Library list The server uses specified libraries to resolve unqualified stored procedure names, and stored procedures use them to resolve unqualified names. To specify multiple libraries, use commas or spaces to separate individual entries. You can use *LIBL as a placeholder for the current library list of the server job
For more information about library list properties, see JDBC LibraryList property.
Default SQL schema The server uses the default SQL schema to resolve unqualified names in SQL statements. For example, in the statement "SELECT * FROM MYTABLE", the server looks only in the default SQL schema for MYTABLE. You can specify the default SQL schema on the connection URL. When you do not specify the default SQL schema on the connection URL, the following conditions apply, depending on the SQL naming convention that is used:
|no||List of server libraries, separated by commas or spaces||"*LIBL"|
|"maximum precision"||Specifies the maximum decimal precision the database might use.||no||
|"maximum scale"||Specifies the maximum scale the database might use.||no||"0"-"63"||"31"|
|"minimum divide scale"||Specifies the minimum scale value for the result of decimal division.||no||
|"package ccsid"||Specifies the character encoding to use for the SQL package and any statements sent to the server.||no||
|"transaction isolation"||Specifies the default transaction isolation.||no||
|"translate hex"||Specifies how hexadecimal literals are interpreted.||no||
|"true autocommit"||Specifies whether the connection should use true auto commit support. True autocommit means that autocommit is on and is running under a isolation level other than *NONE. By default, the driver handles autocommit by running under the server isolation level of *NONE.||no||
|"xa loosely coupled support"||Specifies whether lock sharing is allowed for
loosely coupled transaction branches.
Note: This setting is ignored when running to IBM i 5.3 or earlier.
Format properties specify date and time formats, date and decimal separators, and table naming conventions used within SQL statements.
|"date format"||Specifies the date format used in date literals within SQL statements.||no||
|"date separator"||Specifies the date separator used in date literals within SQL statements. This property has no effect unless the "date format" property is set to "julian", "mdy", "dmy" or "ymd".||no||
|"decimal separator"||Specifies the decimal separator used in numeric literals within SQL statements.||no||
|"naming"||Specifies the naming convention used when referring to tables.||no||"sql" (as in schema.table) "system" (as in schema/table)||"sql"|
|"time format"||Specifies the time format used in time literals within SQL statements.||no||
|"time separator"||Specifies the time separator used in time literals within SQL statements. This property has no effect unless the "time format" property is set to "hms".||no||
Performance properties are attributes that include caching, data conversion, data compression, and prefetching that affect performance.
|"big decimal"||Specifies whether an intermediate java.math.BigDecimal object is used for packed and zoned decimal conversions. If this property is set to "true", an intermediate java.math.BigDecimal object is used for packed and zoned decimal conversions as described by the JDBC specification. If this property is set to "false", no intermediate objects are used for packed and zoned decimal conversions. Instead, such values are converted directly to and from Java double values. Such conversions will be faster but may not follow all conversion and data truncation rules documented by the JDBC specification.||no||
|"block criteria"||Specifies the criteria for retrieving data from the server
in blocks of records. Specifying a non-zero value for this property
will reduce the frequency of communication to the server, and therefore
Ensure that record blocking is off if the cursor is going to be used for subsequent UPDATEs, or else the row that is updated will not necessarily be the current row.
|"block size"||Specifies the block size (in kilobytes) to retrieve from the server and cache on the client. This property has no effect unless the "block criteria" property is non-zero. Larger block sizes reduce the frequency of communication to the server, and therefore may improve performance.||no||
|"data compression"||Specifies whether result set data is compressed. If this property is set to "true", then result set data is compressed. If this property is set to "false", then result set data is not compressed. Data compression may improve performance when retrieving large result sets.||no||
|"extended dynamic"||Specifies whether to use extended dynamic support. Extended dynamic support provides a mechanism for caching dynamic SQL statements on the server. The first time a particular SQL statement is prepared, it is stored in a SQL package on the server. If the package does not exist, it is automatically created. On subsequent prepares of the same SQL statement, the server can skip a significant part of the processing by using information stored in the SQL package. If this is set to "true", then a package name must be set using the "package" property.||no||
|"lazy close"||Specifies whether to delay closing cursors until subsequent requests. This will improve overall performance by reducing the total number of requests.||no||
|"lob threshold"||Specifies the maximum LOB (large object) size (in bytes) that can be retrieved as part of a result set. LOBs that are larger than this threshold will be retrieved in pieces using extra communication to the server. Larger LOB thresholds will reduce the frequency of communication to the server, but will download more LOB data, even if it is not used. Smaller LOB thresholds may increase frequency of communication to the server, but will only download LOB data as it is needed.||no||"0" - "16777216"||"32768"|
|"package"||Specifies the base name of the SQL package. Note that only the first six characters are used to generate the name of the SQL package on the server. This property has no effect unless the "extended dynamic" property is set to "true". In addition, this property must be set if the "extended dynamic" property is set to "true".||no||SQL package||""|
|"package add"||Specifies whether to add newly prepared statements to the SQL package specified on the "package" property. This property has no effect unless the "extended dynamic" property is set to "true".||no||
|"package cache"||Specifies whether to cache a subset of the SQL package information in client memory. Caching SQL packages locally reduces the amount of communication to the server for prepares and describes. This property has no effect unless the "extended dynamic" property is set to "true".||no||
|"package criteria"||Specifies the type of SQL statements to be stored in the SQL package. This can be useful to improve the performance of complex join conditions. This property has no effect unless the "extended dynamic" property is set to "true".||no||
|"package error"||Specifies the action to take when SQL package errors occur. When a SQL package error occurs, the driver will optionally throw a SQLException or post a notice to the Connection, based on the value of this property. This property has no effect unless the "extended dynamic" property is set to "true".||no||
|"package library"||Specifies the library for the SQL package. This property has no effect unless the "extended dynamic" property is set to "true".||no||Library for SQL package||"QGPL"|
|"prefetch"||Specifies whether to prefetch data upon executing a SELECT statement. This will improve performance when accessing the initial rows in the ResultSet.||no||
|"qaqqinilib"||Specifies a QAQQINI library name. Used to specify 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.||no||"QAQQINI library name"||(server default)|
|"query optimize goal"||Specifies the goal the server should use with
optimization of queries. This setting corresponds to the server's
QAQQINI option called OPTIMIZATION_GOAL.
Note: This property is ignored when connecting to systems running to IBM i 5.3 and earlier.
|"query storage limit"||Limits 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 execute.||no||-1 (*NOMAX) to 2 147 352 578||-1, which indicates a special value of *NOMAX|
|"query timeout mechanism"||Specifies the mechanism to implement the queryTimeout feature.||no||
|"receive buffer size"||Specifies the buffer size used to receive data
through the socket connection between the front-end driver and the
Note: This does not specify the actual receive buffer size. It is only used as a hint by the underlying socket code.
|no||"1" - max size||(platform dependent)|
|"send buffer size"||Specifies the buffer size used to send data
through the socket connection between the front-end driver and the
Note: This does not specify the actual send buffer size. It is only used as a hint by the underlying socket code.
|no||"1" - max size||(platform dependent)|
|"use block update"||This property allows the toolbox JDBC driver to use a block update mode when updating blocks of data into the database. This is an optimized version of the batch update.||no||
|"variable field compression"||Specifies whether variable-length fields should be compressed.||no||
Sort properties specify how the server performs stores and performs sorts.
|"sort"||Specifies how the server sorts records before sending them to the client.||no||
|"sort language"||Specifies a 3-character language id to use for selection of a sort sequence. This property has no effect unless the "sort" property is set to "language".||no||Language id||ENU|
|"sort table"||Specifies the library and file name of a sort sequence table stored on the server. This property has no effect unless the "sort" property is set to "table".||no||Qualified sort table name||""|
|"sort weight"||Specifies how the server treats case while sorting records. This property has no effect unless the "sort" property is set to "language."||no||
Other properties are those properties not easily categorized. These properties determine which JDBC driver is used, and specify options related to level of database access, bidirectional string type, data truncation and so on.
|"access"||Specifies the level of database access for the connection.||no||
|"autocommit exception"||Specifies whether to throw an SQLException when Connection.commit() or Connection.rollback() is called if autocommit is enabled.||no||
|"bidi string type"||Specifies the output string type of bidirectional data. See BidiStringType for more information.||no||
|"bidi implicit reordering"||Specifies if bidi implicit LTR-RTL reordering should be used.||no||
|"bidi numeric ordering"||Specifies if the numeric ordering round trip feature should be used.||no||
|"data truncation"||Specifies whether truncation of character data generates
attention notices and exceptions. When this property is "true", the
When this property is "false", writing truncated data to the database or using such data in a query generates no exception or attention notice.
The default value is "true".
This property does not affect numeric data. Writing truncated numeric data to the database always throws an error and using truncated numeric data in a query always posts attention notices.
|"driver"||Specifies the JDBC driver implementation. The IBM Toolbox for Java JDBC driver can use different JDBC driver implementations based on the environment. If the environment is an IBM i JVM on the same server as the database to which the program is connecting, the native IBM Developer Kit for Java JDBC driver can be used. In any other environment, the IBM Toolbox for Java JDBC driver is used. This property has no effect if the "secondary URL" property is set.||no||
|"errors"||Specifies the amount of detail to be returned in the message for errors that occur on the server.||no||
|"extended metadata"||Specifies whether the driver requests extended metadata from
the server. Setting this property to true increases the accuracy of
the information returned from the following ResultSetMetaData methods:
Additionally, setting this property to true enables support for the ResultSetMetaData.getSchemaName(int) and ResultSetMetaData.isAutoIncrement(int) methods.
Note: Setting this property to true may degrade performance because it requires retrieving more information from the server. Leave the property as the default (false) unless you need more specific information from the listed methods. For example, when this property is off (false), ResultSetMetaData.isSearchable(int) always returns "true" because the driver does not have enough information from the server to make a judgment. Turning on this property (true) forces the driver to get the correct data from the server.
|"full open"||Specifies whether the server fully opens a file for each query. By default the server optimizes open requests. This optimization improves performance but may fail if a database monitor is active when a query is run more than once. Set the property to true only when identical queries are issued when monitors are active.||no||
|"hold input locators"||Specifies whether input locators should be allocated as type hold locators or not hold locators. If the locators are of type hold, they will not be released when a commit is done.||no||
|"hold statements"||Specifies if statements should remain open until a transaction boundary when autocommit is off and they are associated with a LOB locator. By default, all the resources associated with a statement are released when the statement is closed. Set this property to true only when access to a LOB locator is needed after a statement has been closed.||no||
|"ignore warnings"||Specifies a list of SQL states for which the driver should not create warning objects. By default, the IBM Toolbox for Java JDBC driver will internally create a java.sql.SQLWarning object for each warning returned by the database. 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.||no||A comma separated list of SQL states that should be ignored.||""|
|"keep alive"||Specifies whether socket connection is to be periodically checked for operational status.||no||
|"key ring name"||Specifies the key ring class name used for SSL connections with the server. This property has no effect unless "secure" is set to true and a key ring password is set using the "key ring password" property.||no||"key ring name"||""|
|"key ring password"||Specifies the password for the key ring class used for SSL communications with the server. This property has no effect unless "secure" is set to true and a key ring name is set using the "key ring name" property.||no||"key ring password"||""|
|"metadata source"||Specifies how to retrieve DatabaseMetaData.
If set to "0," database metadata will be retrieved through the Retrieve
Object Information (ROI) data flow.
If set to "1," database metadata will be retrieved by calling system stored procedures.
||"0" for IBM i 6.1 and older releases, "1" for all other releases|
|"proxy server"||Specifies the host name and port of the middle-tier machine
where the proxy server is running. The format for this is hostname[:port],
where the port is optional. If this is not set, then the hostname
and port are retrieved from the com.ibm.as400.access.AS400.proxyServer property.
The default port is 3470 (if the connection uses SSL, the
default port is 3471). The ProxyServer must be running on
the middle-tier machine.
The name of the middle-tier machine is ignored in a two-tier environment.
|no||Proxy server host name and port||(value of the proxyServer property, or none if not set)|
|"remarks"||Specifies the source of the text for REMARKS columns in ResultSets returned by DatabaseMetaData methods. This property is only used if the "metadata source" property is set to "0".||no||
|"secondary URL"||Specifies the URL to be used for a connection on the middle-tier's DriverManager in a multiple tier environment, if it is different than already specified. This property allows you to use this driver to connect to other databases. Use a backslash as an escape character before backslashes and semicolons in the URL.||no||JDBC URL||(current JDBC URL)|
|"secure"||Specifies whether a Secure Sockets Layer (SSL) connection is used to communicate with the server.||no||
|"server trace"||Specifies the level of tracing of the JDBC server job. When tracing is enabled, tracing starts when the client connects to the server and ends when the connection is disconnected. You must start tracing before connecting to the server, because the client enables server tracing only at connect time.||no||
Multiple types of trace can be started by adding these values together. For example, "6" starts the database monitor and starts debug.
|"thread used"||Specifies whether threads are used in communication with the host servers.||no||
|"toolbox trace"||Specifies what category of an IBM Toolbox for Java trace to log. Trace messages are useful for debugging programs that call JDBC. However, there is a performance penalty associated with logging trace messages, so this property is only set for debugging. Trace messages are logged to System.out.||no||
|"trace"||Specifies whether trace messages are logged. Trace messages are useful for debugging programs that call JDBC. However, there is a performance penalty associated with logging trace messages, so this property only set to "true" for debugging. Trace messages are logged to System.out.||no||
|"translate binary"||Specifies whether binary data is translated. If this property is set to "true", then BINARY and VARBINARY fields are treated as CHAR and VARCHAR fields.||no||
|"translate boolean"||Specifies how Boolean objects are interpreted when setting the value for a character field/parameter using the PreparedStatement.setObject(), CallableStatement.setObject() or ResultSet.updateObject() methods. Setting the property to "true" would store the Boolean object in the character field as either "true" or "false." Setting the property to "false" would store the Boolean object in the character field as either "1" or "0."||no||