This discussion is archived
3 Replies Latest reply: Nov 14, 2012 10:09 PM by kgronau RSS

DG4ODBC and the AS400 ODBC (HS_FDS_FETCH_ROWS parameter)

dscheingold Newbie
Currently Being Moderated
I am trying to figure out what is the optimal setting for ODBC connection to an DB2/AS400 server

In order to get the AS400 to return correct records i have to set HS_FDS_FETCH_ROWS to 1

If i set it to anything other than 1 , the results returned are incorrect (some rows are skipped or some fields are blank/null)

This is causing some lag when compared to ODBC connectivity from MS access. Any pointers in the right direction will be greatly appreciated. Thank you


These are my current parameters

HS_FDS_CONNECT_INFO = AS400
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so.2.0.0
HS_FDS_SUPPORT_STATISTICS=TRUE
HS_FDS_TRACE_LEVEL = 99
HS_FDS_TRACE_FILE_NAME = /u01/as400.log
HS_FDS_FETCH_ROWS = 1
HS_RPC_FETCH_SIZE = 6000
HS_RPC_FETCH_REBLOCKING = ON
HS_ROWID_CACHE_SIZE = 3
set ODBCINSTINI=/etc/odbcinst.ini
set ODBCINI=/etc/odbc.ini
  • 1. Re: DG4ODBC and the AS400 ODBC (HS_FDS_FETCH_ROWS parameter)
    kgronau Guru
    Currently Being Moderated
    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:
    HS_FDS_SUPPORT_STATISTICS=TRUE
    => 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.
  • 2. Re: DG4ODBC and the AS400 ODBC (HS_FDS_FETCH_ROWS parameter)
    dscheingold Newbie
    Currently Being Moderated
    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
  • 3. Re: DG4ODBC and the AS400 ODBC (HS_FDS_FETCH_ROWS parameter)
    kgronau Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points