Toolbox for Java and JTOpen

JDBC frequently asked questions

What JDBC drivers does IBM provide for DB2 on IBM i?

IBM provides two different JDBC drivers for the IBM i database:

In general, we strive to make both JDBC drivers behave the same. There are slight differences which are documented.

Which JDBC driver should I use?

The Native driver runs only on the IBM i JVM, but performs better than the Toolbox driver when the data is on the same machine. The Toolbox driver runs on any JVM (including the JVM shipped with IBM i). The current general advice is this: If your program is only intended to run on the IBM i JVM and the data is on the same machine, use the Native driver. If your program is intended to run on other JVMs or the Java program is on one IBM i system and the data is on a different IBM i system, use the Toolbox driver.

The best advice is to avoid tying (hardcoding) your program to use a specific JDBC driver. Instead, make the JDBC driver configurable at runtime. Users can then specify whichever JDBC driver makes sense in their environment.

Can I access my IBM i database with Java but without JDBC?

Yes, here are a few alternatives (and there are certainly more):

What type of JDBC driver does the Toolbox have?

Sun categorizes JDBC drivers into four types. The Toolbox driver is a Type 4 driver. This means (in Sun's words) that it "converts JDBC calls directly into the network protocol used by the DBMS. This allows a direct call from the client machine to the DBMS server and is an excellent solution for intranet access."

More specifically, the Toolbox driver communicates directly with the IBM i database Host Server. This is the same path that the IBM i Access for Windows ODBC driver uses to access the database. Note: IBM i Access for Windows is NOT required in order for you to use the IBM Toolbox for Java.

What character conversion issues must my program deal with?

The IBM i database uses EBCDIC to store text. Java uses Unicode. The JDBC driver handles all conversion between character sets, so your program should not have to worry about it.

If you need to store your data as Unicode in the database, make sure to declare the column as GRAPHIC CCSID 13488. If you receive the "CCSID conversion not valid" error, try setting your system CCSID to 37.

Is the Toolbox JDBC driver threadsafe?

In Version 5 Release 1, JTOpen 2.0, or later: Yes. All public Toolbox JDBC objects are threadsafe and can be shared between multiple threads.

In Toolbox Modification 3 (Version 4 Release 5), JTOpen 1.0, and previous versions: Yes and no. A Connection object is threadsafe and can be shared between threads without the need for additional synchronization. A Statement object is NOT threadsafe, and unexpected results can occur if multiple threads access the same Statement object. We suggest that multiple threads can share the same Connection, but should each create their own Statement.

Regardless of which version you are using, be aware that IBM i database cursors are scoped to an entire Connection object. If your Java program calls a stored procedure which creates a cursor and returns a ResultSet, it will not be threadsafe, even if you use separate CallableStatement objects in each thread.

How does the Toolbox JDBC driver deal with dates before 1940 (or after 2039)?

The IBM i database supports several date formats. The Toolbox JDBC driver uses the date format that is set up as the default on the IBM i system. This default is usually set to "mdy" which only supports dates between 1940 and 2039. You can override the date format by specifying the "date format" property when opening the JDBC connection. The best choice is "iso", which supports a full four-digit date. The easiest way to do this is to add ";date format=iso" to the end of the URL used when connecting to the database.

What does "Limit on number of statements exceeded" mean?

A Toolbox Connection object can manage only a finite number of open Statements at any given time. If this maximum is exceeded, an SQLException will be thrown with the message "Limit on number of statements exceeded." This usually happens when a JDBC program neglects to close Statement objects that are no longer needed. The solution is to close Statement objects when they are no longer needed. This is generally a good idea anyway, since it helps to conserve resources on both the client and the IBM i system.

"The limit is 9999 open Statements per Connection."

Why is the Toolbox JDBC returning EBCDIC characters to my Java program?

Normally, the Toolbox JDBC driver will translate EBCDIC characters to Unicode Strings automatically. If it appears to be returning untranslated EBCDIC characters instead, then it is likely that the field in the IBM i database is tagged with CCSID 65535. The Toolbox JDBC driver recognizes this CCSID as a field that should not be translated. To avoid this behavior, tag fields on the IBM i that you want to be translated, with a valid CCSID. Alternately, you can set the "translate binary" connection property to "true", which instructs the JDBC driver to translate all fields, including those tagged with CCSID 65535. The easiest way to do this is to add

";translate binary=true"

to the end of the URL used when connecting to the database.

What connection properties does the Toolbox JDBC driver support?

A connection property is a JDBC concept which allows a program to configure settings specific to a particular JDBC driver. Connection properties are typically set by passing a java.util.Properties object to the java.sql.DriverManager.registerDriver() method. The Toolbox JDBC driver also recognizes connection properties specified on the end of the connection URL in the format:

jdbc:as400://mySystem;name1=value1;name2=value2;...

This convenient syntax allows properties to be specified without necessarily recompiling code.

The full list of the connection properties supported by the Toolbox JDBC driver can be found in the IBM Toolbox for Java Programmers Guide (for example, the V5R4 guide). Find the javadoc documentation for the com.ibm.as400.access.AS400JDBCDriver class and take the "JDBC Properties" link from there.

How can I improve my program's performance when using the Toolbox JDBC driver?

How can I use JDBC to access physical file members other than the default?

Although the IBM i database supports physical files with multiple members, SQL (and consequently JDBC) is designed to access only the first member (which is usually the member with the same name as the file/table). If you are using Toolbox JDBC to access files and members created outside of SQL, you may need to trick SQL into using members other than the first.

You can do this by creating an SQL Alias. Suppose that you have a physical file MYLIB/SALES with twelve members (one member for each month of the year). To access JANUARY, you must first create an alias with the following SQL command:

CREATE ALIAS MYLIB/SALES_JANUARY FOR MYLIB/SALES(JANUARY)

Now, you can access the individual member JANUARY by using a select command such as SELECT * FROM SALES_JANUARY. However, if you rename a member of MYLIB/SALES, the alias will no longer point to it. You can either create a new alias for the member you renamed or create a new member with the old member name.

// Here is the exact CREATE ALIAS command string.
String aliasCommand = "CREATE ALIAS MYLIB/SALES_JANUARY " +
"FOR MYLIB/SALES(JANUARY) ";

// Create a Statement and issue the CREATE ALIAS SQL command.
Statement statement = connection.createStatement();
statement.executeUpdate(aliasCommand);

How do I get more information about an SQL message?

If the IBM i database detects an error while it is carrying out your JDBC requests, the Toolbox JDBC driver will throw an SQLException with message text that is in the form "[SQLxxxx] Something went wrong". The first part is an IBM i message id, and the sentence that follows is first-level message text. While the first-level message text describes the error, you may want to get more information such as the cause of the error and possible recovery actions. This information is called second-level message text and is available to your program. One way to retrieve the second-level message text is to set the "errors" connection property to "full". This will cause the SQLException to include the second-level message text (if any) in the exceptions text (available by calling getMessage() on the SQLException object).

To see the complete message information (including the second-level message text) for message SQLxxxx, you can sign-on to an IBM i emulation session and issue the CL command:

DSPMSGD RANGE(SQLxxxx) MSGF(QSQLMSG)

Why does ResultSet.getRow() sometimes return 0?

getRow() returns the current row number within the ResultSet as an int. Previous to Modification 5 (Version 5 Release 2 or JTOpen version 3.x), the Toolbox JDBC driver attempted to keep track of this row number internally by starting at 0 and incrementing with each cursor position change. Depending on the cursor positioning methods used, the Toolbox JDBC driver might lose track of the row number and report it as 0. For example, if your program called last(), afterLast(), or absolute() with a negative value, the Toolbox JDBC driver could no longer compute the row number (since the total number of rows in a result set was not always known).

In Modification 5 (Version 5 Release 2 or JTOpen version 3.x), getRow() accurately returns the correct row number in all cases.

What does "The application requester cannot establish the connection" mean?

The Toolbox JDBC driver uses the system name, user ID, and password provided in the call to DriverManager.getConnection() to establish a connection to the IBM i database. If any of these are not provided, the Toolbox JDBC driver will display a sign-on prompt. If the Toolbox JDBC driver can not sign on to the system for any reason, it will throw an SQLException with the message "The application requester cannot establish the connection". Here is a list of potential causes for this exception:

You can get more information about the exception by turning on JDBC tracing.

Why does my program fail with java.sql.BatchUpdateException cannot be found?

The Toolbox JDBC driver supports the JDBC 2.0 specification and therefore makes reference to the java.sql.BatchUpdateException class. This class did not exist in JDK 1.1.x. Some JDK 1.1.x environments try to preload this class despite the fact that it will never be used. There are two workarounds for this problem:

Why do I get a StaleConnectionException while using WebSphere 4.0 and Toolbox's AS400JDBCConnectionPoolDataSource?

There are several different reasons for this error. First of all, make sure you have v5r1 JACL PTF #SI02886 (SS03 PTF #SI03548) for Toolbox or at least JTOpen version 3.0. This PTF level updates the Toolbox JDBC driver to fully support the JDBC 2.x features used by WAS 4.0. Older versions of Toolbox may cause StaleConnectionExceptions or incorrectly report network errors causing function sequence errors.

If you are still seeing StaleConnectionExceptions, investigate if you have regular network timeouts set that would be resetting the connection between the client and server. WebSphere throws StaleConnectionExceptions when Toolbox reports that it has lost the connection to the system. This is generally because of a network timeout. To resolve the problem:

  1. Adjust any connection pools' idle timeout value to a value lower then the idle timeout of the network.
  2. Adjust the firewall/router's idle timeout to a value greater then the connection pools' idle timeout.
  3. Lower the TCP keepalive on the IBM i system. For example, if the keepalive is set to 30 minutes a keepalive frame will be sent once every 30 to 59 minutes. The keepalive frame may reset the network's inactivity timer. Note that keepalive frames do not reset the idle time counters on all network devices.
  4. Verify that an IBM i system operator is not ending the subsystems to perform backup operations.

Applications must correctly handle StaleConnectionExceptions to recover. This is especially true when transactions are used. See the WebSphere Connection Pooling whitepaper for additional information. It can be found at http://www-306.ibm.com/software/webservers/appserv/whitepapers/connection_pool.pdf (PDF, 300KB).

Why do I get "Could not find the property connectionAttribute on class com.ibm.as400.access.AS400JDBCConnectionPoolDataSource" in my trace log from WebSphere 4.0 when using the Toolbox's AS400JDBCConnectionPoolDataSource?

This is simply a warning that the Toolbox JDBC driver does not have this property on its data sources. It will not affect the execution of your WebSphere program.

Why don't I get the behavior I expect from the "libraries" property on the Toolbox driver?

The behavior of library lists on an IBM i system is quite complicated. Here is a summary of how the Toolbox JDBC driver's "libaries" property works as of v5r1 JACL PTF #SI02886, JTOpen 3.1, or Version 5 Release 2.

Property: libraries

Description

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 IBM i system 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

Default schema

The IBM i system uses the default schema to resolve unqualified names in SQL statements. For example, in the statement "SELECT * FROM MYTABLE", the system 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: * 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 system uses the specified libraries to search for unqualified names. * When you do not set this property or when it contains only *LIBL, the system uses the current library list of the server job to search for unqualified names

Required: no

Choices: List of IBM i libraries separated by commas or spaces

Default: *LIBL

Contact IBM

Browse Power Systems

Next generation applications for big data and analytics and cognitive computing are providing unprecedented insights into opportunities, threats and efficiencies. IBM Power Systems is at the forefront of delivering solutions to gain faster insights from analyzing both structured information and unstructured big data. With the secure, flexible and open platform of IBM Power Systems plus solutions and software, organizations can outpace their competitors by delivering faster services, providing differentiated offerings and turning operational cost into investment opportunity.

To draw insights and make better decisions, businesses rely on the secure, flexible and open platform of IBM Power Systems. Built with the first processor designed for big data workloads, the design of Power Systems combines the computing power, memory bandwidth and I/O in ways that are easier to consume and manage, building on strong resiliency, availability and security.

IBM Power Systems deliver flexibility and choice of operating systems to enable your business to support the next generation applications for big data and analytics and cognitive computing that are transforming how organizations work today. Whether running 1, 2, or all 3 - coupled with PowerVM, they maximize the benefit of Power Systems in your business.

Transform your business with Systems Software that enables virtualization, high availability, flexibility, security and compliance on Power Systems™. IBM’s integrated approach to developing Systems and Systems Software together delivers optimized results with Power Systems.

As an open innovation platform, Power Systems is optimized for big data and analytics performance and to deliver scale-out economics and security for the cloud. IBM and IBM Business Partner solutions exploit key capabilities in IBM Power Systems.

Over the last five years thousands of clients have migrated to IBM Power Systems. Learn how Power Systems has helped them support next generation applications for big data and analytics and cognitive computing on an open platform for choice while improving business performance, reducing risk, and establishing a platform for growth.