This content has been marked as final. Show 3 replies
The AS400 ODBC driver is having some issues with the bulk fetching, that's the reason why you have to set HS_FDS_FETCH_ROWS=1. Indeed, this setting is causing performance issues as each row is fetched by a separate SQlFetch statement instead of a bulk fetch of 100 (=default) rows like you have in MS Access.
When you want to get this fixed, you have to contact the ODBC driver vendor.
About the other settings:
=> I would set this parameter to false as commonly the DB2 stats are not always up to date.
HS_FDS_TRACE_LEVEL = 99
=> set it to off if you don't need a trace file
HS_FDS_TRACE_FILE_NAME = /u01/as400.log
=> not needed
HS_FDS_FETCH_ROWS = 1
=> mandatory for AS400 ODBC driver
HS_RPC_FETCH_SIZE = 6000
=> you can play around with several different settings, but keep in mind the network capacity at a certain time impacts the tests and you have to figure out the best value for your env.
HS_RPC_FETCH_REBLOCKING = ON
=> with RPC_FETCH_SIZE Reblocking=OFF sometimes increases the performance
HS_ROWID_CACHE_SIZE = 3
=> not needed
In my opinion disabling the statistics might increase the performance and when the driver supports bulk fetching it would also enhance the performance. If - like in your case- the driver does not support bulk fetching, switching the ODBC driver vendor, for example to a commercial ODBC driver vendor like DataDirect or also using the IBM CLI based ODBC driver (libdb2.so) might be also an alternative. As many commercial ODBC drivers require a license which you have to purchase, the Oracle Database Gateway for DRDA might be an alternative for you as well. Just compare the pricing of the DRDA gateway with the ODBC driver license for your commercial ODBC driver.
Thank you, this was very helpful and did increase our performance but still not up to bar with other odbc clients getting data from the same db2 server.
the ibm cli driver, is this free?
DRDA and DataDirect are both outside of my budget range
as far as I know this driver requires a license but it is available on the AS400 machine when cli connections are registered. But it is always good to get in touch with the vendor of the ODBC driver and ask the vendor about licensing.