The following statement attributes may need to be set by DB2 CLI applications.
Setting this attribute limits the number of rows returned to the application. This can be used to avoid an application from being overwhelmed with a very large result set generated inadvertently, especially for applications on clients with limited memory resources.
In DB2 UDB v6, setting SQL_ATTR_MAX_ROWS will add the "OPTIMIZE for n ROWS" and "Fetch n Rows ONLY" clauses onto the statement. For other servers, the full result set is still generated at the server, DB2 CLI will only fetch up to SQL_ATTR_MAX_ROWS rows.
This statement attribute determines if the cursor for this statement will be defined with the equivalent of the CURSOR WITH HOLD clause.
Resources associated with statement handles can be better utilized by DB2 CLI if the statements that do not require CURSOR WITH HOLD are set to SQL_CURSOR_HOLD_OFF.
| Note: | Many ODBC applications expect a default behavior where the cursor position is maintained after a commit. |
DB2 CLI allows the isolation level to be set at the statement level, (however, we recommend that the isolation level be set at the connection level). The isolation level determines the level of concurrency possible, and the level of locking required to execute the statement.
Resources associated with statement handles can be better utilized by DB2 CLI if statements are set to the required isolation level, rather than leaving all statements at the default isolation level. This should only be attempted with a thorough understanding of the locking and isolation levels of the connected DBMS. Refer to the SQL Reference for a complete discussion of isolation levels and their effect.
Applications should use the minimum isolation level possible to maximize concurrency.