Connection string keywords - Performance properties

Use these IBM® i Access ODBC driver connection string keywords to change Performance properties of the ODBC connection.

The following table lists connection string keywords for Performance properties that are recognized by the IBM i Access ODBC driver:

Table 1. IBM i Access ODBC connection string keywords for Performance properties
Keyword	Description	Choices	Default
BLOCKFETCH	Specifies whether or not internal blocking will be done on fetches of 1 row. When set, the driver will try to optimize the fetching of records when one record is requested by the application. Multiple records will be retrieved and stored by the driver for later retrieval by the application. When an application requests another row, the driver will not need to send another flow to the host database to get it. If not set, blocking will be used according to the application's ODBC settings for that particular statement. Note: For more information on setting this option see the Fine-tuning record blocking topic.	0 = Use ODBC settings for blocking 1 = Use blocking with a fetch of 1 row	1
BLOCKSIZE or BlockSizeKB	Specifies the block size (in kilobytes) that is retrieved on FETCH requests and then cached on the client. This property has no effect unless the BLOCKFETCH property is 1. Larger block sizes reduce the frequency of communication to the server, and therefore may increase performance.	1 – 8192	256
COMPRESSION or AllowDataCompression	Specifies whether to compress data sent to and from the server. In most cases, data compression improves performance due to less data being transmitted between the driver and the server.	0 = Disable compression 1 = Enable compression	1
CONCURRENCY	Specifies whether to override the ODBC concurrency setting by opening all cursors as updateable. Note: In the following two cases, setting this option has no effect: When building a SELECT SQL statement the FOR FETCH ONLY or FOR UPDATE clause can be added. If either of these clauses are present in a SQL statement the ODBC driver will honor the concurrency that is associated with the clause. Catalog result sets are always read-only.	0 = Use ODBC concurrency settings 1 = Open all cursors as updateable	0
CURSORSENSITIVITY	Specifies the cursor sensitivity to use when opening cursors. This option applies to all forward-only and dynamic cursors that are opened on the same connection. Static cursors are always insensitive.	0 - Unspecified/Asensitive 1 = Insensitive 2 = Sensitive
EXTCOLINFO or ExtendedColInfo	The extended column information affects what the SQLGetDescField and SQLColAttribute APIs return as Implementation Row Descriptor (IRD) information. The extended column information is available after the SQLPrepare API has been called. The information that is returned is: SQL_DESC_AUTO_UNIQUE_VALUE SQL_DESC_BASE_COLUMN_NAME SQL_DESC_BASE_TABLE_NAME and SQL_DESC_TABLE_NAME SQL_DESC_LABEL SQL_DESC_SCHEMA_NAME SQL_DESC_SEARCHABLE SQL_DESC_UNNAMED SQL_DESC_UPDATABLE Note: the driver sets the SQL_DESC_AUTO_UNIQUE_VALUE flag only if a column is an identity column with the ALWAYS option over a numeric data type (such as integer). Refer to the DB2® for i SQL Reference for details on identity columns.	0 = Do not retrieve extended column information 1 = Retrieve extended column information	0
LAZYCLOSE	Specifies whether to delay closing cursors until subsequent requests. This will increase overall performance by reducing the total number of requests. Note: This option can cause problems due to the cursors still holding locks on the result set rows after the close request.	0 = Close all cursors immediately 1 = Delay closing of cursors until the next request	0
MAXFIELDLEN or MaxFieldLength	Specifies the maximum LOB (large object) size (in kilobytes) that can be retrieved as part of a result set. LOBs that are larger than this threshold will be retrieved in pieces using extra communication to the server. Larger LOB thresholds will reduce the frequency of communication to the server, but will download more LOB data, even if it is not used. Smaller LOB thresholds may increase frequency of communication to the server, but they will only download LOB data as it is needed. Notes: Setting this property to 0 forces the driver to always retrieve the LOB values with additional communication flows. Setting this property larger than 15360 KB has no effect. Anything larger than 15360 KB is retrieved in pieces from the server. Retrieving the data in pieces reduces the amount of memory needed, at any given time, on the client.	0 — 2097152	32
PREFETCH	Specifies whether to prefetch data upon executing a SELECT statement. This increases performance when accessing the initial rows in the ResultSet.	0 = Do not prefetch data 1 = Prefetch data	1
QRYSTGLMT	Specifies storage limit for a query. If the estimated storage usage exceeds the specified storage limit in the parameter, the query is not executed.	*NOMAX = No Query Limit 0 - 2147352578	*NOMAX
QUERYOPTIMIZEGOAL	Specifies the optimization goal for queries. This parameter corresponds to the QAQQINI option called OPTIMIZATION_GOAL. For more information, refer to the QAQQINI option in the DB2 for i SQL Reference.	0 = Use the goal of ALLIO if extended dynamic support is enabled, otherwise use the FIRSTIO goal. 1 = FIRSTIO - Return the first block of data as fast as possible. 2 = ALLIO - Optimize as if the complete result set will be read by the application.	0
QUERYTIMEOUT	Specifies whether the driver will disable support for the query timeout attribute, SQL_ATTR_QUERY_TIMEOUT. If disabled, SQL queries will run until they finish.	0 = Disable support for the query timeout attribute 1 = Allow the query timeout attribute to be set	1