Intel AS/400 RISC Server - Manual (Page 152)

Chapter 10. DB2 for i5/OS JDBC and ODBC Performance
DB2 for i5/OS can be accessed through many different interfaces. Among these interfaces are: Windows
.NET, OLE DB, Windows database APIs, ODBC and JDBC. This chapter will focus on access through
JDBC and ODBC by providing programming and tuning hints as well as links to detailed information.
10.1 DB2 for i5/OS access with JDBC
Access to the System i data from portable Java applications can be achieved with the universal database
access APIs available in JDBC (Java Database Connectivity). There are two JDBC drivers for the System
i. The Native JDBC driver is a type 2 driver. It uses the SQL Call Level Interface for database access
and is bundled in the System i Developer Kit for Java. The JDBC Toolbox driver is a type 4 driver
which is bundled in the System i Toolbox for Java. In general, the Native driver is chosen when running
on the System i server directly, while the Toolbox driver is typically chosen when accessing data on the
System i server from another machine. The Toolbox driver is typically used when accessing System i data
from a Windows machine, but it could be used when accessing the System i server from any Java capable
system. More detailed information on which driver to choose may be found in the JDBC references.
JDBC Performance Tuning Tips
JDBC performance depends on many factors ranging from generic best programming practices for
databases to specific tuning which optimizes JDBC API performance. Tips for both SQL programming
and JDBC tuning techniques to improve performance are included here.
y
In general when accessing a database it takes less time to retrieve smaller amounts of data. This is
even more significant for remote database access where the data is sent over a network to a client.
For good performance, SQL queries should be written to retrieve only the data that is needed. Select
only needed fields so that additional data is not unnecessarily retrieved and sent. Use appropriate
predicates to minimize row selection on the server side to reduce the amount of data sent for client
processing.
y Follow the ‘Prepare once, execute many times’ rule of thumb. For statements that are executed many
times, use the PreparedStatement object to prepare the statement once. Then use this object to do
subsequent executes of this statement. This significantly reduces the overhead of parsing and
compiling the statement every time it is executed.
y
Do not use a PreparedStatement object if an SQL statement is run only one time. Compiling and
running a statement at the same time has less overhead than compiling the statement and running it in
two separate operations.
y
Consider using JDBC stored procedures. Stored procedures can help reduce network communication
time and traffic which improves response time. Java supports stored procedures via CallableStatement
objects.
y
Turn off autocommit, if possible. Explicitly manage commits in the application, but do not leave
transactions uncommitted for long periods of time.
IBM i 6.1 Performance Capabilities Reference - January/April/October 2008
©
Copyright IBM Corp. 2008 Chapter 10 - DB2 for i5/OS JDBC and ODBC 152