Cursor behavior notes

Cursor behaviors can affect how data is fetched when working with the IBM® i Access ODBC driver.

Cursor types can be set via SQLSetStmtAttr with the SQL_ATTR_CURSOR_TYPE option.

Cursor types:

SQL_CURSOR_FORWARD_ONLY - All catalog result sets use this type of cursor. When a catalog result set has been generated the cursor type will be automatically changed to this.
SQL_CURSOR_KEYSET_DRIVEN - mapped to SQL_CURSOR_STATIC.
SQL_CURSOR_DYNAMIC - supported.
SQL_CURSOR_STATIC -supported if the statement allows it.

Note: Procedure result set cursors are opened in the procedure, therefore setting the cursor type with SQLSetStmtAttr will not affect the cursor type. See Stored procedure result sets for more information on procedure result sets.

The following factors can affect the concurrency of the cursor:

If the SQL statement contains the "FOR UPDATE" clause the value for SQL_ATTR_CONCURRENCY will be set to SQL_CONCUR_LOCK.
If the CONCURRENCY keyword / DSN setting is set to 1 (checked) then if the SQL statement does not have "FOR FETCH ONLY" clause in it the ODBC driver will lock records from the result set.

Rowset size:

The ODBC driver uses the value of SQL_ROWSET_SIZE when dealing with SQLExtendedFetch. The driver uses the value of SQL_ATTR_ROW_ARRAY_SIZE when dealing with SQLFetch and SQLFetchScroll.

When there are LOBs in a result set there is a chance that locators may be used by the driver. Locators are internal handles to LOB fields. Locators are used when the setting for the MAXFIELDLEN connection option has a smaller value than the size of a LOB column in the result set. Locators can improve performance in some cases as the driver only gets the data the application asks for. The downside of locators is that there is some extra communication needed with the server. When locators are not used the driver will download more LOB data even if it is not used. It is strongly encouraged that the COMPRESSION connection option be enabled if locators are not being used. See Connection String keywords descriptions for more details on the MAXFIELDLEN keyword

SQLGetData can only be used for accessing data from single row fetches. Calling SQLGetData on a multiple row fetch is not supported.

Result set row counts:

There are several options that your application can use to determine the row count before fetching data:

You can set the cursor type to SQL_CURSOR_STATIC.
If your application uses ADO, you can use client-side cursors.
Your application can use the COUNT() function by calling SELECT COUNT(*) FROM MYTABLE prior to running the actual query.