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.
Note: The following list does not include DataSource properties.
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.
General property | Description | Required | Choices | Default |
---|---|---|---|---|
"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 | server password | (user will be prompted) |
"prompt" | Specifies whether the user should be prompted if a user name or password is needed to connect to the server. If a connection can not be made without prompting the user, and this property is set to "false", then an attempt to connect will fail. | no | "true" "false" |
"true" |
"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.
Server property | Description | Required | Choices | Default |
---|---|---|---|---|
"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 | "true" "false" |
"true" |
![]() |
Specifies the database to use for the connection, including one stored in an independent auxiliary storage pool. This property applies only when connecting to a V5R2 or later version of OS/400. When you specify a database name, the name must exist in the relational database directory on the server. The following criteria determine which database is accessed:
|
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.![]() |
"libraries" | Specifies the server libraries to add to the server job's
library list. The libraries are delimited by commas or spaces, and "*LIBL"
may be used as a place holder for the server job's current library list.
The library list is used for resolving unqualified stored procedure calls
and finding schemas in DatabaseMetaData catalog methods. If "*LIBL"
is not specified, then the specified libraries will replace the server job's
current library list.
In addition, if no default schema is specified in the URL, then the first library listed in this property will also be the default schema, which is used to resolve unqualified names in SQL statements. |
no | server libraries | "*LIBL" |
"transaction isolation" | Specifies the default transaction isolation. | no | "none" "read uncommitted" "read committed" "repeatable read" "serializable" |
"read uncommitted" |
Format properties specify date and time formats, date and decimal separators, and table naming conventions used within SQL statements.
Format property | Description | Required | Choices | Default |
---|---|---|---|---|
"date format" | Specifies the date format used in date literals within SQL statements. | no | "mdy" "dmy" "ymd" "usa" "iso" "eur" "jis" "julian" |
(server job) |
"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 | "/" (slash) "-" (dash) "." (period) "," (comma) "b" (space) |
(server job) |
"decimal separator" | Specifies the decimal separator used in numeric literals within SQL statements. | no | "." (period) "," (comma) |
(server job) |
"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 | "hms" "usa" "iso" "eur" "jis" |
(server job) |
"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 | ":" (colon) "." (period) "," (comma) "b" (space) |
(server job) |
Performance properties are attributes that include caching, data conversion, data compression, and prefetching that affect performance.
Performance property | Description | Required | Choices | Default |
---|---|---|---|---|
"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 | "true" "false" |
"true" |
"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 increase
performance.
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. |
no | "0" (no record blocking) "1" (block if FOR FETCH ONLY is specified) "2" (block unless FOR UPDATE is specified) |
"2" |
"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 increase performance. | no | "0" "8" "16" "32" "64" "128" "256" "512" |
"32" |
"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 | "true" "false" |
"true" |
"extended dynamic" | Specifies whether to use extended dynamic support. Extended
dynamic support provides a mechanism for caching dynamic SQL statements
on the server. ![]() ![]() |
no | "true" "false" |
"false" |
"lazy close" | Specifies whether to delay closing cursors until subsequent requests. This will increase overall performance by reducing the total number of requests. | no | "true" "false" |
"false" |
"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" | "0" |
"package" | Specifies the base name of the SQL package. ![]() ![]() |
no | SQL package | "" |
"package add" | ![]() ![]() |
no | "true" "false" |
"true" |
"package cache" | ![]() ![]() |
no | "true" "false" |
"false" |
"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 | "default" (only store SQL statements with parameter
markers in the package)![]() ![]() |
"default" |
"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 warning 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 | "exception" "warning" "none" |
"warning" |
"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 increase performance when accessing the initial rows in the ResultSet. | no | "true" "false" |
"true" |
Sort properties specify how the server performs stores and performs sorts.
Sort property | Description | Required | Choices | Default |
---|---|---|---|---|
"sort" | Specifies how the server sorts records before sending them to the client. | no | "hex" (base the sort on hexadecimal values) "job" (base the sort on the setting for the server job) "language" (base the sort on the language set in the "sort language" property) "table" (base the sort on the sort sequence table set in the "sort table" property) |
"job" |
"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 | "shared" (uppercase and lowercase characters sort as
the same character) "unique" (uppercase and lowercase characters sort as different characters) |
"shared" |
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.
Other property | Description | Required | Choices | Default |
---|---|---|---|---|
"access" | Specifies the level of database access for the connection. | no | "all" (all SQL statements allowed) "read call" (SELECT and CALL statements allowed) "read only" (SELECT statements only) |
"all" |
"bidirectional string type" | Specifies the output string type of bidirectional data. See BidiStringType for more information. | no |
"" (use the CCSID to determine bidirectional string type) |
"" |
"data truncation" |
When this property is "false", writing truncated data to the database or using such data in a query generates no exception or warning. 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 a warning. |
no | "true" "false" |
"true" |
"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 iSeries 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 | "toolbox" (use only the IBM Toolbox for Java JDBC
driver). "native" (use the IBM Developer Kit for Java JDBC driver if running on the server, otherwise use the Toolbox for Java JDBC driver). |
"toolbox" |
"errors" | Specifies the amount of detail to be returned in the message for errors that occur on the server. | no | "basic" "full" |
"basic" |
![]() |
Specifies whether the driver should request 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) method. Setting this property to true may slow 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 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. You can use extended metadata only when connecting to a server running OS/400 V5R2 or later. |
no | "true" "false" |
"false"![]() |
"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 | "true" "false" |
"false" |
"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" | "" |
"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. | no | "sql" (SQL object comment) "system" (OS/400 object description) |
"system" |
"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 databases other than the iSeries or AS/400e server. 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. SSL connections are only available when connecting to servers at V4R4 or later. | no | "true" (encrypt all client/server communication) "false" (encrypt only the password) |
"false" |
![]() |
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 |
"0" (trace is not active) Multiple types of trace can be started by adding these values together. For example, "6" starts the database monitor and starts debug. |
"0"![]() |
"thread used" | Specifies whether threads should be used in communication with the host servers. | no | "true" "false" |
"true" |
"trace" | Specifies whether trace messages should be 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 should only be set to "true" for debugging. Trace messages are logged to System.out. | no | "true" "false" |
"false" |
"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 | "true" "false" |
"false" |