Many properties can be specified when connecting to an AS/400 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 table lists the different connection properties that are recognized by this driver.
Some of these properties affect performance and others are server job attributes.
Property |
Description |
Required |
Choices |
Default |
General properties |
"user" |
Specifies the user name for connecting to the AS/400 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 |
AS/400 user |
(user will be prompted) |
"password" |
Specifies the password for connecting to the AS/400 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 |
AS/400 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 AS/400 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" |
Property |
Description |
Required |
Choices |
Default |
Server properties |
"libraries" |
Specifies the AS/400 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 |
AS/400 libraries |
"*LIBL" |
"transaction isolation" |
Specifies the default transaction isolation. |
no |
"none"
"read committed"
"read uncommitted"
"repeatable read"
"serializable"
|
"none" |
Property |
Description |
Required |
Choices |
Default |
Format properties |
"date format" |
Specifies the date format used in date literals
within SQL statements. |
no |
"mdy"
"dmy"
"ymd"
"usa"
"iso"
"eur"
"jis"
|
(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" (e.g. schema.table )
"system" (e.g. 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 "date format" property is set to "hms". |
no |
":" (colon)
"." (period)
"," (comma)
"b" (space)
|
(server job) |
Property |
Description |
Required |
Choices |
Default |
Performance properties |
"block criteria" |
Specifies the criteria for retrieving data from the
AS/400 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 AS/400 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 increase
performance. |
no |
"8", "16", "32", "64", "128", "256", "512" |
"32" |
"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" |
"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 run, it is stored in a SQL package on the server.
On subsequent runs 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 |
"true", "false" |
"false" |
"package" |
Specifies the base name of the SQL package. Extended dynamic
support works best when this is derived from the application
name. Note that only the first seven characters are
significant. 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 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" |
"package cache" |
Specifies whether to cache SQL packages in memory. Caching
SQL packages locally reduces the amount of communication
to the server in some cases. This property has no effect
unless the "extended dynamic" property is set to "true". |
no |
"true", "false" |
"false" |
"package clear" |
Specifies whether to clear SQL packages when they become
full. Clearing a SQL package results in removing all
SQL statements that have been stored in the SQL package.
This property has no effect unless the "extended dynamic"
property is set to "true". |
no |
"true", "false" |
"false" |
"package add" |
Specifies whether to add statements to an existing SQL
package. This property has no effect unless the
"extended dynamic" property is set to "true". |
no |
"true", "false" |
"true" |
"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" |
Property |
Description |
Required |
Choices |
Default |
Sort properties |
"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 |
(locale) |
"sort table" |
Specifies the library and file name of a sort sequence
table stored on the AS/400 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" (upper- and lower-case characters are
sorted as the same character)
"unique" (upper- and lower-case characters are
sorted as different characters)
|
"shared" |
Property |
Description |
Required |
Choices |
Default |
Other properties |
"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" |
"errors" |
Specifies the amount of detail to be returned in the
message for errors that occur on the AS/400 server. |
no |
"basic"
"full"
|
"basic" |
"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" |
"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" |
"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" |