Optimization of ODBC throughput for DB2 UDB for iSeries
I am using the 64bit ODBC driver with the DB2 UDB for iSeries database and would like to significantly improve the throughput. For example, a 4 million record load of customer master data from the ERP takes 35 minutes because the records are arriving in blocks of less than 2000.
The Performance tab on the iSeries Access for Windows ODBC Setup options offer many options, but they did not appear to have any positive or negative impact. The "Record blocking size (KB)" would seem like the the place to start, but it has no impact on the throughput in our case.
Here are the options on the Performance tab of the iSeries Access for Windows Setup accessed from the ODBC Administration:
Enable lazy close support Enable pre-fetch of data for queries Enable data compression
Large objects (LOB) threshold (KB) Use blocking with a fetch of 1 row Record blocking size (KB)
Open all cursors as updateable Cursor sensitivity Retrieve extended column information Allow query timeout
Query optimization goal
I would like to know if the ODBC throughput can be increased, or if there are other approaches that are more robust.
"Record blocking size" is certainly the right parameter. If it doesn't help, check your settings on the host - what subsystem is it running under? what resources are allowed for that subsystem, what are the limitations for the User ID (most likely QUSER), ODBC limitations etc...Your AS/400 administrator should be able to help with those settings...
Back to the ODBC settings - make sure you are not opening databases in read/write mode, that seems to slow things down.
