|
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 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 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 (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.
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
|
