ODBC limited block fetch

The Db2 ODBC driver can use limited block fetch to improve performance of FETCH operations on a local Db2 for z/OS® server.

With limited block fetch, the local Db2 for z/OS server groups the rows that are retrieved by an SQL query into a block of rows in a query buffer. The Db2 ODBC driver retrieves those blocks of rows from the query buffer. Applications that perform single-row fetches or multi-row fetches from large result sets with the SQLFetch(), SQLExtendedFetch() or SQLFetchScroll() function can benefit from limited block fetch. Retrieval of a large number of rows at one time can offer better performance than multiple retrievals of fewer rows.

You can enable limited block fetch without any making any changes to your applications. To enable limited block fetch:
  • Set the LIMITEDBLOCKFETCH initialization keyword to 1.

    1 is the default value.

  • If the default value of 32767 bytes does not provide adequate performance, adjust the QUERYDATASIZE initialization parameter to set the number of bytes that are transferred at one time during FETCH processing. In general, a larger value of QUERYDATASIZE results in fewer trips to the data source, which can result in better performance.

Limited block fetch is effective only for non-scrollable cursors that do not update or delete data.

For applications that use locators when retrieving LOB data from a result set, set LIMITEDBLOCKFETCH to 0. Otherwise, if you attempt to use the SQLGetData() function to retrieve a LOB locator into an application variable after the data has been fetched, the function call fails.

Also, for applications that use the SQL_C_BINARYXML data type, set LIMITEDBLOCKFETCH to 0. Otherwise, if you attempt to use the SQLGetData() function to retrieve XML data and have LIMITEDBLOCKFETCH set to 1, the function call fails.

When you enable limited block fetch, the data that is returned to your application might not reflect the data that has been committed to the source table. For example, suppose that limited block fetch is enabled, and that your application issues SQLFetch() to fetch a row from a result set. Db2 ODBC retrieves and stores a block of rows. Suppose that another application concurrently deletes all subsequent rows from the table. The next SQLFetch() calls by your application retrieve subsequent rows from the stored block of rows. However, those rows no longer exist in the table. If your application fetches data from tables that are updated by other users, or if your application uses savepoints and issues ROLLBACK TO SAVEPOINT to manage transactions, you should disable limited block fetch.