Intel 170 Servers, AS/400 RISC Server References for Jdbc 10.2 DB2 for i5/OS access with Odbc

yUse 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

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

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

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

yUse block inserts and batch updates.

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

References for JDBC

yThe System i Information Center Http://publib.boulder.ibm.com/iseries/

yThe home page for Java and DB2 for i5/OS http://www-03.ibm.com/systems/i/software/db2/javadb2.html

ySun’s JDBC web page http://java.sun.com/products/jdbc/

10.2DB2 for i5/OS access with ODBC

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	Chapter 10 - DB2 for i5/OS JDBC and ODBC	153