Intel AS/400 RISC Server ユーザーズマニュアル

y

Use the lowest isolation level required by  the application.  Higher isolation levels can reduce  
performance levels as more locking and synchronization are required. Transaction levels in order of
increasing level are: TRANSACTION_NONE, TRANSACTION_READ_UNCOMMITTED,
TRANSACTION_READ_COMMITTED, TRANSACTION_REPEATABLE_READ,
TRANSACTION_SERIALIZABLE

y

Reuse connections. Minimize the opening and closing of connections where possible. These
operations are very expensive. If possible, keep connections open and reuse them. A connection pool
can help considerably. 

y

Consider use of Extended Dynamic support. In generally provides better performance by caching the
SQL statements in SQL packages on the System i.   

y

Use  appropriate cursor settings. Use a fetch forward only cursor type if the data does not need to be
scrollable.  Use read only cursors for retrieving data which will not be updated.  

y

Use block inserts and batch updates. 

y

Tune connection properties to maximize application performance.  The connection properties are
explained in the driver documentation. Among the properties are ‘block size’ and ‘data compression’
which should be tuned as follows:

1. Choose the right ‘block size” for the application. ‘block size’ specifies the amount of data to

retrieve from the server and cache on the client. For the Toolbox driver ‘block size’ specifies the
transfer size in kilobytes, with 32 as the default.  For the native driver ‘block size’ specifies the
number of rows that will be fetched at a time for a result set, with 32 as the default.  When larger
amounts of data are retrieved a larger block size may help minimize communication time.

2. The Toolbox driver has a ‘data compression’ property to enable compressing the data blocks

before sending them to the client. This is set to true by default.  In general this gives better
response time, but may use more CPU.  

y

The System i Information Center

y

The home page for Java and DB2 for i5/OS 

y

Sun’s JDBC web page

ODBC  (Open Database Connectivity) is a set of API's which provide clients with an open interface to
any ODBC supported database.  The ODBC APIs are part of  System i Access. 

In general, the JDBC Performance tuning tips also apply to the performance of ODBC applications:

IBM i 6.1 Performance Capabilities Reference - January/April/October 2008

©

 Copyright IBM Corp. 2008