y
Employ efficient SQL programming techniques to minimize the amount of data processed
y
Prepared statement reuse to minimize parsing and optimization overhead for frequently run queries
y
Use stored procedures when appropriate to bundle processing into fewer database requests
y
Consider extended dynamic package support for SQL statement and package caching
y
Process 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:
y
Prefetch
y
ExtendedDynamic
y
RecordBlocking
y
BlockSizeKB
y
LazyClose
y
LibraryView
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
