|
What JDBC drivers does IBM provide for DB2 on i5/OS?
IBM provides two different JDBC drivers for the i5/OS database:
- The "Native" JDBC driver. This is shipped as part of the IBM Developer Kit for Java (57xxJV1). It is implemented by making native method calls to the SQL CLI (Call Level Interface). Consequently, it only runs on the i5/OS JVM. The class name to register is com.ibm.db2.jdbc.app.DB2Driver. The URL subprotocol is db2.
- The "Toolbox" JDBC driver. This is shipped as part of the IBM Toolbox for Java (57xxJC1). It is implemented by making direct socket connections to the database host server. This happens to be the same route that the IBM System i™ Access for Windows ODBC driver takes. However, IBM System i™ Access for Windows is NOT required. The Toolbox runs on any JVM. The class name to register is com.ibm.as400.access.AS400JDBCDriver. The URL subprotocol is as400.
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 i5/OS 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 i5/OS). The current general advice is this: If your program is only intended to run on the i5/OS 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 i5/OS system and the data is on a different i5/OS 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 i5/OS database with Java but without JDBC?
Yes, here are a few alternatives (and there are certainly more):
- Use the record-level database access classes provided in the IBM Toolbox for Java. These bypass JDBC and SQL altogether. Using record-level database access, your program can read and write database files using record formats specific to i5/OS. These classes communicate using DRDA and are often faster than JDBC, but they are also somewhat proprietary (whereas JDBC is somewhat standardized across the industry).
- Write your own client/server. You can then code your server program in whatever language you want (Java included). The server program can access the database using native methods. Some examples:
- Write a servlet that gets called by the web server when a client requests a given URL.
- Write a server program that reads requests from and sends replies to data queues. The client program can then use the IBM Toolbox for Java data queue classes to communicate with an i5/OS system.
- The client can call i5/OS programs directly using the IBM Toolbox for Java program call classes. Parameter passing and messages are supported.
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 i5/OS database Host Server. This is the same path that the System i Access for Windows ODBC driver uses to access the database. Note: System i Access for Windows is NOT required in order for you to use the IBM Toolbox for Java.
Does the Toolbox JDBC driver support JDBC 2.0?
Yes, as of Modification 2 of the Toolbox. Existing programs will certainly not need to change. However, in order to take advantage of the new JDBC 2.0 features, your program must be compiled and run using JDK 1.2 (Java 2). You can still use the Toolbox JDBC driver with JDK 1.1.x, but in that case your program will only be able to use the JDBC 1.x functionality.
Does the Toolbox JDBC driver support JDBC 3.0?
Yes, as of Modification 5 of the Toolbox (Version 5 Release 2 or JTOpen version 3.x). Existing programs will not need to change. However, in order to take advantage of the new JDBC 3.0 features, your program must be compiled and run using JDK 1.4 (Java 2). You can still use the Toolbox JDBC driver with JDK 1.2.x, but in that case your program will only be able to use the JDBC 1.1.x and 1.2.x functionality. In addition, some parts of the Toolbox JDBC support require functionality from a system running OS/400 Verision 5 Release 2 or later. Check the Toolbox javadoc to see which JDBC 3.0 methods require i5/OS support.
What character conversion issues must my program deal with?
The i5/OS 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 thraedsafe?
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 i5/OS 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 i5/OS database supports several date formats. The Toolbox JDBC driver uses the date format that is set up as the default on the i5/OS 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 i5/OS 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 i5/OS 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 i5/OS 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 use JDBC to access physical file members other than the default?
Although the i5/OS 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 i5/OS 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 i5/OS 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 i5/OS 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 i5/OS 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:
- The system name is incorrect. One way to verify this is to use ping with the specified system name. If ping fails, then there is a TCP/IP configuration problem between your client and the system.
- The user ID or password is incorrect.
- The i5/OS database host server is not started. You can start this by running STRHOSTSVR *DATABASE on the i5/OS command line. You can verify if the i5/OS database host server is running using the utilities.JPing application.
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:
- Configure your Java environment to load classes only when needed. Consult your Java environment's documentation for details.
- Download jtStubs.jar from the JTOpen website (link resides outside of ibm.com) (http://jt400.sourceforge.net/) and add it to your CLASSPATH. This file contains stubs for all the classes needed in some JDK 1.1.x. The inclusion of this file in the CLASSPATH usually satisfies these environments and eliminates the class loading errors.
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:
- Adjust any connection pools' idle timeout value to a value lower then the idle timeout of the network.
- Adjust the firewall/router's idle timeout to a value greater then the connection pools' idle timeout.
- Lower the TCP keepalive on the i5/OS 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.
- Verify that an i5/OS 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 (300KB).
Why do I get "Could not find the property connectionAttribute on class 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 i5/OS 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 i5/OS 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 i5/OS 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 i5/OS libraries separated by commas or spaces
Default: *LIBL
|
