For IBM i, one of the properties for a connection is the library list to be used. This can be especially useful when the
connection is used for calling a stored procedure or SQL trigger. To specify the library list, append the "libraries"
property to the connection's Properties field.
For example, the connection properties might be changed to:
;prompt=false;trace=false;libraries=*LIBL,lib1,lib2,lib3
Here is a screenshot of some sample properties:
With this example, the connection will append libraries lib1, lib2, and lib3 to the end of the default library list.
NOTES: If WOW is installed on the IBM i and you want to connect to different systems, you may need to setup host entries. Use the CFGTCP CL command with options 10 and 12 to do this. Refer to IBM i manuals for details.
NOTE: This example applies to the iSeries (AS/400). For other platforms, refer to the appropriate JDBC documentation.
IBM Toolbox for Java Details: http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Frzahh%2Fpage1.htm.
Many additional properties can be set for the IBM i AS/400 JDBC Connection such as date formats. Refer to IBM's website for the most up to date list for your OS version.
To make SQL on the IBM i case insensitive (ignore case) , add the sort=language; property. For example:
jdbc:as400://172.17.2.9/pjsys70;translate binary=true;sort=language;
Some users have reported WOW showing strange "hexadecimal" characters. This may be due to the systems CCSID character encoding. For example, if you see data such as the image below: Add the following property to your WOW connection properties section: "translate binary=true" and restart your WOW connection. NOTE: multiple properties are separated by a ;
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.
Server properties specify attributes that govern transactions, libraries, and databases.
Server property
"cursor hold"
"cursor sensitivity"
"database name"
"libraries"
"maximum precision"
"maximum scale"
"minimum divide scale"
"package ccsid"
"rollback cursor hold"
Description
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.
Specifies the cursor sensitivity to request from the database. The behavior depends on the resultSetType:
ResultSet.TYPE_FORWARD_ONLY or ResultSet.TYPE_SCROLL_SENSITIVE means that the value of this property controls what cursor sensitivity the Java™ program requests from the database.
ResultSet.TYPE_SCROLL_INSENSITIVE causes this property to be ignored.
This property is ignored when connecting to systems running V5R1 and earlier versions of i5/OS®.
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 i5/OS. 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:
When this property is used to specify a database, the specified database is used. When the specified database does not exist, the connection fails.
When this property is used to specify *SYSBAS as the database name, the system default database is used.
When this property is omitted, 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.
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 library (default schema).
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
When the first entry is *LIBL, the specified libraries are added to the current library list of the server job
When you do not use *LIBL, the specified libraries replace the current library list of the server job
For more information about library list properties, seeJDBC LibraryList property.
Default schema The server uses the default schema to resolve unqualified names in SQL statements. For example, in the statement "SELECT * FROM MYTABLE", the server looks only in the default schema for MYTABLE. You can specify the default schema on the connection URL. When you do not specify the default schema on the connection URL, the following conditions apply, depending on whether you use SQL Naming or System Naming.
SQL Naming When you do not specify the default schema on the connection URL:
Required
no
no
no
no
no
no
no
no
Choices
"true"
"false"
"" (Use the ResultSet Type to determine the cursor sensitivity)
"asensitive"
"sensitive"
"insensitive"
Database name "*SYSBAS"
List of server libraries, separated by commas or spaces
"31"
"63"
"0"-"63"
"0"
"1"
"2"
"3"
"4"
"5"
"6"
"7"
"8"
"9"
"1200" (UCS-2)
"13488" (UTF-16)
"true"
"false"
Default
"true"
""
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.
"*LIBL"
"31"
"31"
"0"
"13488"
The first entry (unless it is *LIBL) becomes the default schema
When the first entry is *LIBL, the second entry becomes the default schema
When you do not set this property or when it contains only *LIBL, the user profile becomes the default schema
System Naming When you do not specify the default schema on the connection URL:
No default schema is set, and the server uses the specified libraries to search for unqualified names
When you do not set this property or when it contains only *LIBL, the server uses the current library list of the server job to search for unqualified names
Specifies the maximum decimal precision the database might use.
Specifies the maximum scale the database might use.
Specifies the minimum scale value for the result of decimal division.
Specifies the character encoding to use for the SQL package and any statements sent to the server.
Specifies whether to hold the cursor after a rollback. If this property is set to "true", cursors are held after a transaction is rolled back.
"false"
no
no
no
no
"transaction isolation"
"translate hex"
"true autocommit"
"xa loosely coupled support"
Specifies the default transaction isolation.
Specifies how hexadecimal literals are interpreted.
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.
"none"
"read
uncommited"
"read commited"
"repeatable
read"
"serializable"
"character" (Interpret hexadecimal literals as character data)
"binary" (Interpret hexadecimal literals as binary data)
"true" (Use true autocommit.)
"false" (Do not use true autocommit.)
"0" = Locks cannot be shared
"1" = Locks can be shared
"read uncommitted"
"character"
"false"
Specifies whether lock sharing is allowed for loosely coupled transaction branches.
Note: This setting is ignored when running to i5/OS V5R3 or earlier.
no
"0"
Format properties specify date and time formats, date and decimal separators, and table naming conventions used within SQL statements.
Performance properties are attributes that include caching, data conversion, data compression, and prefetching that affect performance.
Performance property
"big decimal"
"block criteria"
"block size"
"data compression"
"extended dynamic"
"lazy close"
"lob threshold"
"package"
"package add"
"package cache"
"package criteria"
"package error"
"package library"
"prefetch"
"qaqqinilib"
Description
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.
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 improve 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.
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.
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.
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.
Specifies whether to delay closing cursors until subsequent requests. This will improve overall performance by reducing the total number of requests.
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.
Specifies the base name of the SQL package. Note that only the first seven 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".
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".
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".
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".
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".
Specifies the library for the SQL package. This property has no effect unless the "extended dynamic" property is set to "true".
Specifies whether to prefetch data upon executing a SELECT statement. This will improve performance when accessing the initial rows in the ResultSet.
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® UDB for iSeries™database engine.
Required
no
no
no
no
no
no
no
no
no
no
no
no
no
no
no
Choices
"true"
"false"
"0" (no record blocking)
"1" (block if FOR FETCH ONLY is specified)
"2" (block unless FOR UPDATE is specified)
"0"
"8"
"16"
"32"
"64"
"128"
"256"
"512"
"true"
"false"
"true"
"false"
"true"
"false"
"0" - "16777216"
SQL package
"true"
"false"
"true"
"false"
"default" (only store SQL statements with parameter markers in the package)
"select" (store all SQL SELECT statements in the package)
"exception"
"warning"
"none"
Library for SQL package
"true"
"false"
"QAQQINI library name"
Default
"true"
"2"
"32"
"true"
"false"
"false"
"32768"
""
"true"
"false"
"default"
"warning"
"QGPL"
"true"
(server default)
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 i5/OS V5R3 and earlier.
"0" = Optimize query for first block of data (*FIRSTIO) when extended dynamic packages are used; Optimize query for entire result set (*ALLIO) when packages are not used
"1" = Optimize query for first block of data (*FIRSTIO)
"2" = Optimize query for entire result set (*ALLIO)
"query optimize goal"
no
"0"
Sort properties specify how the server performs stores and performs sorts.