Intel AS/400 RISC Server, 170 Servers, 7xx Servers manual Odbc Performance Settings

yEmploy efficient SQL programming techniques to minimize the amount of data processed

yPrepared statement reuse to minimize parsing and optimization overhead for frequently run queries

yUse stored procedures when appropriate to bundle processing into fewer database requests

yConsider extended dynamic package support for SQL statement and package caching

yProcess data in blocks of multiple rows rather than single records when possible (e.g. Block inserts)

In addition for ODBC performance ensure that each statement has a unique statement handle. Sharing statement handles for multiple sequential SQL statements causes DB2 on i5/OS to do FULL OPEN operations since the database cursor can not be reused. By ensuring that an SQLAllocStmt is done before any SQLPrepare or SQLExecDirect commands, database processing can be optimized. This is especially important when a set of SQL statements are executed in a loop. Ensuring each SQL statement has its own handle reduces the DB2 overhead.

Tools such as ODBC Trace (available through the ODBC Driver Manager) are useful in understanding what ODBC calls are made and what activity occurs as a result. Client application profilers may also be useful in tuning client applications. These are often included in application development toolkits.

ODBC Performance Settings

You may be able to further improve the performance of your ODBC application by configuring the ODBC data source through the Data Sources (ODBC) administrator in the Control Panel. Listed below are some of the parameters that can be set to better tune the performance of the System i Access ODBC Driver. The ODBC performance parameters discussed in detail are:

yPrefetch

yExtendedDynamic

yRecordBlocking

yBlockSizeKB

yLazyClose

yLibraryView

Prefetch : The Prefetch option is a performance enhancement to allow some or all of the rows of a particular ODBC query to be fetched at PREPARE time. We recommend that this setting be turned ON. However, if the client application uses EXTENDED FETCH (SQLExtendedFetch) this option should be turned OFF.

ExtendedDynamic: Extended dynamic support provides a means to "cache" dynamic SQL statements on the System i server. With extended dynamic, information about the SQL statement is saved away in an SQL package object on the System i the first time the statement is run. On subsequent uses of the statement, System i Access ODBC recognizes that the statement has been run before and can skip a significant part of the processing by using the information saved in the SQL package. Statements which are cached include SELECT, positioned UPDATE and DELETE, INSERT with subselect, DECLARE PROCEDURE, and all other statements which contain parameter markers.

All extended dynamic support is application based. This means that each application can have its own configuration for extended dynamic support. Extended dynamic support as a whole is controlled through the use of the ExtendedDynamic option. If this option in not selected, no packages are used. If the option is selected (default) custom settings per application can be configured with the “Custom Settings Per Application” button. When this button is clicked a “Package information for application” window pops up and package library and name fields can be filled in and usage options can be selected.

IBM i 6.1 Performance Capabilities Reference - January/April/October 2008
© Copyright IBM Corp. 2008	Chapter 10 - DB2 for i5/OS JDBC and ODBC	154