DB2 Version 9.7 for Linux, UNIX, and Windows

Statement attributes (CLI) list

The currently defined attributes and the version of CLI or ODBC in which they were introduced are shown below; it is expected that more will be defined to take advantage of different data sources.

SQL_ATTR_ALLOW_INTERLEAVED_GETDATA

Specifies whether the application can call SQLGetData() for previously accessed LOB columns and maintain the data offset position from the previous call to SQLGetData() when querying data servers that support Dynamic Data Format. This attribute has one of the following values:

SQL_ALLOW_INTERLEAVED_GETDATA_OFF - This default setting does not allow applications to call SQLGetData() for previously accessed LOB columns.
SQL_ALLOW_INTERLEAVED_GETDATA_ON - This keyword only affects connections to database servers that support Dynamic Data Format, also known as progressive streaming. Specify this option to allow applications to call SQLGetData() for previously accessed LOB columns and start reading LOB data from where the application stopped reading during the previous read.

Setting the AllowInterleavedGetData CLI/ODBC configuration keyword is an alternative method of specifying this behavior at the connection level.

SQL_ATTR_ALLOW_INTERLEAVED_GETDATA connection attribute is not supported with an IDS data server.

SQL_ATTR_APP_PARAM_DESC

The handle to the APD for subsequent calls to SQLExecute() and SQLExecDirect() on the statement handle. The initial value of this attribute is the descriptor implicitly allocated when the statement was initially allocated. If this attribute is set to SQL_NULL_DESC, an explicitly allocated APD handle that was previously associated with the statement handle is dissociated from it, and the statement handle reverts to the implicitly allocated APD handle.

This attribute cannot be set to a descriptor handle that was implicitly allocated for another statement or to another descriptor handle that was implicitly set on the same statement; implicitly allocated descriptor handles cannot be associated with more than one statement or descriptor handle.

This attribute cannot be set at the connection level.

SQL_ATTR_APP_ROW_DESC

The handle to the ARD for subsequent fetches on the statement handle. The initial value of this attribute is the descriptor implicitly allocated when the statement was initially allocated. If this attribute is set to SQL_NULL_DESC, an explicitly allocated ARD handle that was previously associated with the statement handle is dissociated from it, and the statement handle reverts to the implicitly allocated ARD handle.

This attribute cannot be set at the connection level.

SQL_ATTR_APP_USES_LOB_LOCATOR

A 32-bit unsigned integer that indicates if applications are using LOB locators. This attribute has either of the following values:

1 (default): Indicates that applications are using LOB locators.
0: For applications that do not use LOB locators and are querying data on a server that supports Dynamic Data Format, also known as progressive streaming, specify 0 to indicate that LOB locators are not used and allow the return of LOB data to be optimized.

This keyword is ignored for stored procedure result sets.

If the keyword is set to 0 and an application binds a LOB locator to a result set that uses SQLBindCol(), an Invalid conversion error is returned by the SQLFetch() function.

Setting the AppUsesLOBLocator CLI/ODBC configuration keyword is an alternative method of specifying this behavior.

SQL_ATTR_ASYNC_ENABLE

A 32-bit integer value that specifies whether a function called with the specified statement is executed asynchronously:

SQL_ASYNC_ENABLE_OFF = Off (the default)
SQL_ASYNC_ENABLE_ON = On

After a function has been called asynchronously, only the original function, SQLAllocHandle(), SQLCancel(), SQLSetStmtAttr(), SQLGetDiagField(), SQLGetDiagRec(), or SQLGetFunctions() can be called on the statement handle, until the original function returns a code other than SQL_STILL_EXECUTING. Any other function called on any other statement handle under the same connection returns SQL_ERROR with an SQLSTATE of HY010 (Function sequence error).

Because CLI supports statement level asynchronous-execution, the statement attribute SQL_ATTR_ASYNC_ENABLE can be set. Its initial value is the same as the value of the connection level attribute with the same name at the time the statement handle was allocated.

The following functions can be executed asynchronously: SQLBulkOperations(), SQLColAttribute(), SQLColumnPrivileges(), SQLColumns(), SQLDescribeCol(), SQLDescribeParam(), SQLExecDirect(), SQLExecute(), SQLExtendedFetch(), SQLExtendedPrepare(), SQLFetch(), SQLFetchScroll(), SQLForeignKeys(), SQLGetData(), SQLGetLength(), SQLGetPosition(), SQLMoreResults(), SQLNumResultCols(), SQLParamData(), SQLPrepare(), SQLPrimaryKeys(), SQLProcedureColumns(), SQLProcedures(), SQLRowCount(), SQLSetPos(), SQLSpecialColumns(), SQLStatistics(), SQLTablePrivileges(), SQLTables().

Note: Any Unicode equivalent of a function stated above can be called asynchronously. Starting from Version 9.7, Fix Pack 4, this attribute can also be used with SQL_ATTR_USE_LOAD_API.

SQL_ATTR_BLOCK_FOR_NROWS

A 32-bit integer that specifies the desired block size, in rows, to be returned by the server when fetching a result set. For large read-only result sets consisting of one or more data blocks, a large block size can improve performance by reducing the number of synchronous server block requests made by the client. The default value is 0 which means the default block size is returned by the server.

SQL_ATTR_BLOCK_LOBS

A Boolean attribute that specifies if blocking of result sets returning LOB data types is enabled. By default, this attribute is set to 0 (false), however, when set to 1 (true) and when accessing a server that supports blocking of result sets returning LOB data types, all of the LOB data associated with rows that fit completely within a single query block are returned in a single fetch request.

This attribute is not supported when accessing IDS data servers.

SQL_ATTR_CALL_RETURN

A read-only attribute to be retrieved after executing a stored procedure. The value returned from this attribute is -1 if the stored procedure failed to execute (for example, if the library containing the stored procedure executable cannot be found). If the stored procedure executed successfully but has a negative return code (for example, if data truncation occurred when inserting data into a table), then SQL_ATTR_CALL_RETURN returns the value that was set in the sqlerrd (1) field of the SQLCA when the stored procedure was executed.

SQL_ATTR_CHAINING_BEGIN

A 32-bit integer which specifies that DB2® chains together SQLExecute() requests for a single prepared statement before sending the requests to the server; this feature is referred to as CLI array input chaining. All SQLExecute() requests associated with a prepared statement are not sent to the server until either the SQL_ATTR_CHAINING_END statement attribute is set, or the available buffer space is consumed by rows that have been chained. The size of this buffer is defined by the aslheapsz database manager configuration parameter for local client applications, or the rqrioblk database manager configuration parameter for client/server configurations.

This attribute can be used with the CLI/ODBC configuration keyword ArrayInputChain to effect array input without needing to specify the array size. See the documentation for ArrayInputChain for more information.

Note: The specific 32-bit integer value that is set with this attribute is not significant to CLI. Setting this attribute to any 32-bit integer value enables the CLI array input chaining feature.

SQL_ATTR_CHAINING_END

A 32-bit integer which specifies that the CLI array input chaining behavior enabled earlier, with the setting of the SQL_ATTR_CHAINING_BEGIN statement attribute, ends. Setting SQL_ATTR_CHAINING_END causes all chained SQLExecute() requests to be sent to the server. After this attribute is set, SQLRowCount() can be called to determine the total row count for all SQLExecute() statements that were chained between the SQL_ATTR_CHAINING_BEGIN and SQL_ATTR_CHAINING_END pair. Error diagnostic information for the chained statements becomes available after the SQL_ATTR_CHAINING_END attribute is set.

This attribute can be used with the CLI configuration keyword ArrayInputChain to affect array input without needing to specify the array size. See the documentation for ArrayInputChain for more information.

Note: The specific 32-bit integer value that is set with this attribute is not significant to CLI. Setting this attribute to any 32-bit integer value disables the CLI array input chaining feature that was enabled when SQL_ATTR_CHAINING_BEGIN was set.

SQL_ATTR_CLIENT_LOB_BUFFERING

Specifies whether LOB locators or the underlying LOB data is returned in a result set for LOB columns that are not bound. By default, locators are returned. If an application usually fetches unbound LOBs and then must retrieve the underlying LOB data, the application performance can be improved by retrieving the LOB data from the outset. This action reduces the number of synchronous waits and network flows. The possible values for this attribute are:

SQL_CLIENTLOB_USE_LOCATORS (default) - LOB locators are returned
SQL_CLIENTLOB_BUFFER_UNBOUND_LOBS - actual LOB data is returned

SQL_ATTR_CLOSE_BEHAVIOR

A 32-bit integer that specifies whether the DB2 server should attempt to release read locks acquired during a cursor's operation when the cursor is closed. It can be set to either:

SQL_CC_NO_RELEASE - read locks are not released. This is the default.
SQL_CC_RELEASE - read locks are released.

For cursors opened with isolation UR or CS, read locks are not held after a cursor moves off a row. For cursors opened with isolation RS or RR, SQL_ATTR_CLOSE_BEHAVIOR modifies some of those isolation levels, and an RR cursor might experience nonrepeatable reads or phantom reads.

If a cursor that is originally RR or RS is reopened after being closed with SQL_ATTR_CLOSE_BEHAVIOR then new read locks are acquired.

This attribute can also be set at the connection level, however when set at the connection level, it only affects cursor behavior for statement handles that are opened after this attribute is set.

See the SQLCloseCursor() function for more information.

This attribute is not supported when accessing IDS data servers.

SQL_ATTR_CLOSEOPEN

To reduce the time it takes to open and close cursors, DB2 automatically closes an open cursor if a second cursor is opened using the same handle. Network flow is therefore reduced when the close request is chained with the open request and the two statements are combined into one network request (instead of two requests).

0 = DB2 acts as a regular ODBC data source: Do not chain the close and open statements, return an error if there is an open cursor. This behavior is the default.
1 = Chain the close and open statements.

Previous CLI applications do not benefit from this default because they are designed to explicitly close the cursor. New applications, however, can take advantage of this behavior by not closing the cursors explicitly, but by allowing CLI to close the cursor on subsequent open requests.

SQL_ATTR_COLUMNWISE_MRI

A 32-bit unsigned integer that enables CLI applications connected to DB2 for z/OS® servers to convert array input chaining into column-wise array input for INSERT operations. This attribute is available starting in Version 9.7 Fix Pack 5. The possible values are as follows:

SQL_COLUMNWISE_MRI_OFF (default): CLI does not convert chaining data to column-wise array input.
SQL_COLUMNWISE_MRI_ON: CLI converts array input chaining to column-wise array input. The Multi-Row Insert (MRI) feature in DB2 for z/OS expects data to be in column-wise array form. If your application uses array input chaining, this conversion helps you optimize your application performance because data is sent in a compact array form each time you call SQLExecute (). For more information about array input chaining, see SQL_ATTR_CHAINING_BEGIN.

For non-DB2 for z/OS servers, CLI automatically converts chaining data to row-wise array input and setting this attribute has no effect.

The conversion is not performed in the following cases:

Bind parameters with a LOB data type such as SQL_CLOB, SQL_BLOB, SQL_LONGVARBINARY, SQL_LONGVARGRAPHIC, SQL_DBCLOB, or SQL_XML.
Bind input data-at-execute parameters by setting their value to SQL_DATA_AT_EXEC to pass data to INSERT operations by calling the SQLPutData() and SQLParamData() functions.
Space to store all the application data in the internal buffers is not available.

SQL_ATTR_CONCURRENCY

A 32-bit integer value that specifies the cursor concurrency:

SQL_CONCUR_READ_ONLY = Cursor is read-only. No updates are allowed. Supported by forward-only, static and keyset cursors.
SQL_CONCUR_LOCK = Cursor uses the lowest level of locking sufficient to ensure that the row can be updated. Supported by forward-only and keyset cursors.
SQL_CONCUR_VALUES = Cursor uses optimistic concurrency control, comparing values.

The default value for SQL_ATTR_CONCURRENCY is SQL_CONCUR_READ_ONLY for static and forward-only cursors. The default for a keyset cursor is SQL_CONCUR_VALUES.

This attribute cannot be specified for an open cursor.

If the SQL_ATTR_CURSOR_TYPE Attribute is changed to a type that does not support the current value of SQL_ATTR_CONCURRENCY, the value of SQL_ATTR_CONCURRENCY is changed at execution time, and a warning issued when SQLExecDirect() or SQLPrepare() is called.

If a SELECT FOR UPDATE statement is executed while the value of SQL_ATTR_CONCURRENCY is set to SQL_CONCUR_READ_ONLY, an error is returned. If the value of SQL_ATTR_CONCURRENCY is changed to a value that is supported for some value of SQL_ATTR_CURSOR_TYPE, but not for the current value of SQL_ATTR_CURSOR_TYPE, the value of SQL_ATTR_CURSOR_TYPE is changed at execution time, and SQLSTATE 01S02 (Option value changed) is issued when SQLExecDirect() or SQLPrepare() is called.

If the value of SQL_ATTR_CONCURRENCY is set to SQL_CONCUR_LOCK, this value is promoted to SQL_CONCUR_VALUES when all the following conditions are met:

SQL_ROWSET_SIZE OR SQL_ATTR_ROW_ARRAY_SIZE is greater than 1.
The data source is a database on a DB2 for Linux, UNIX, and Windows server.
The PATCH2 configuration keyword is set to 73.

If the specified concurrency is not supported by the data source, then CLI substitutes a different concurrency and returns SQLSTATE 01S02 (Option value changed). The order of substitution depends on the cursor type:

Forward-Only: SQL_CONCUR_LOCK is substituted for SQL_CONCUR_ROWVER and SQL_CONCUR_VALUES
Static: only SQL_CONCUR_READ_ONLY is valid
Keyset: SQL_CONCUR_VALUES is substituted for SQL_CONCUR_ROWVER

Note: The following value has also been defined by ODBC, but is not supported by CLI

SQL_CONCUR_ROWVER = Cursor uses optimistic concurrency control.

SQL_ATTR_CURSOR_HOLD

A 32-bit integer which specifies whether the cursor associated with this StatementHandle is preserved in the same position as before the COMMIT operation, and whether the application can fetch without executing the statement again.

SQL_CURSOR_HOLD_ON (this is the default)
SQL_CURSOR_HOLD_OFF

The default value when an StatementHandle is first allocated is SQL_CURSOR_HOLD_ON.

This option cannot be specified while there is an open cursor on this StatementHandle.

The default cursor hold mode can also be set using the CURSORHOLD CLI/ODBC configuration keyword.

Note: This option is an IBM extension.

SQL_ATTR_CURSOR_SCROLLABLE

A 32-bit integer that specifies the level of support that the application requires. Setting this attribute affects subsequent calls to SQLExecute() and SQLExecDirect(). The supported values are:

SQL_NONSCROLLABLE = Scrollable cursors are not required on the statement handle. If the application calls SQLFetchScroll() on this handle, the only valid value of FetchOrientation() is SQL_FETCH_NEXT. This value is the default.
SQL_SCROLLABLE = Scrollable cursors are required on the statement handle. When calling SQLFetchScroll(), the application can specify any valid value of FetchOrientation, achieving cursor positioning in modes other than the sequential mode.

SQL_ATTR_CURSOR_SENSITIVITY

A 32-bit integer that specifies whether cursors on the statement handle make visible the changes made to a result set by another cursor. Setting this attribute affects subsequent calls to SQLExecute() and SQLExecDirect(). The supported values are:

SQL_UNSPECIFIED = It is unspecified what the cursor type is and whether cursors on the statement handle make visible the changes made to a result set by another cursor. Cursors on the statement handle might make visible none, some or all such changes. This value is the default.
SQL_INSENSITIVE = All cursors on the statement handle show the result set without reflecting any changes made to it by any other cursor. Insensitive cursors are read-only. This corresponds to a static cursor which has a concurrency that is read-only.
SQL_SENSITIVE = All cursors on the statement handle make visible all changes made to a result by another cursor.

SQL_ATTR_CURSOR_TYPE

A 32-bit integer value that specifies the cursor type. The supported values are:

SQL_CURSOR_FORWARD_ONLY = The cursor only scrolls forward. This is the default.
SQL_CURSOR_STATIC = The data in the result set is static.
SQL_CURSOR_KEYSET_DRIVEN = CLI supports a pure keyset cursor. The SQL_KEYSET_SIZE statement attribute is ignored. To limit the size of the keyset the application must limit the size of the result set by setting the SQL_ATTR_MAX_ROWS attribute to a value other than 0.
SQL_CURSOR_DYNAMIC = A dynamic scrollable cursor detects all changes (inserts, deletes and updates) to the result set, and make insertions, deletions and updates to the result set. Dynamic cursors are only supported when accessing servers which are DB2 for z/OS Version 8.1 and later.

This option cannot be specified for an open cursor.

If the specified cursor type is not supported by the data source, CLI substitutes a different cursor type and returns SQLSTATE 01S02 (Option value changed). For a mixed or dynamic cursor, CLI substitutes, in order, a keyset-driven or static cursor.

SQL_ATTR_DB2_NOBINDOUT

A Boolean attribute that specifies when and where the client performs data conversion and related tasks during a fetch operation. The default value of this attribute is 0 (false) and should only be set to 1 (true) when connected to a federated database.

This attribute is not supported when accessing IDS data servers.

SQL_ATTR_DEFERRED_PREPARE

Specifies whether the PREPARE request is deferred until the corresponding execute request is issued.

SQL_DEFERRED_PREPARE_OFF = Disable deferred prepare. The PREPARE request is executed the moment it is issued.
SQL_DEFERRED_PREPARE_ON (default) = Enable deferred prepare. Defer the execution of the PREPARE request until the corresponding execute request is issued. The two requests are then combined into one command/reply flow (instead of two) to minimize network flow and to improve performance.
If the target DB2 database or the DDCS gateway does not support deferred prepare, the client disables deferred prepare for that connection.

Note: When deferred prepare is enabled, the row and cost estimates normally returned in the SQLERRD(3) and SQLERRD(4) of the SQLCA of a PREPARE statement might become zeros. This might be of concern to users who want to use these values to decide whether to continue the SQL statement.

The default deferred prepare mode can also be set using the DEFERREDPREPARE CLI/ODBC configuration keyword.

Note: This is an IBM defined extension.

SQL_ATTR_EARLYCLOSE

Specifies whether the temporary cursor on the server can be automatically closed, without closing the cursor on the client, when the last record is sent to the client.

SQL_EARLYCLOSE_OFF = Do not close the temporary cursor on the server early.
SQL_EARLYCLOSE_ON = Close the temporary cursor on the server early (default).
This saves a network request by not issuing the statement to explicitly close the cursor because it knows that it has already been closed.

Having this option on speeds up applications that use many small result sets.

The EARLYCLOSE feature is not used if the cursor type is anything other than SQL_CURSOR_FORWARD_ONLY.

Note: This is an IBM defined extension.

SQL_ATTR_ENABLE_AUTO_IPD

A 32-bit integer value that specifies whether automatic population of the IPD is performed:

SQL_TRUE = Turns on automatic population of the IPD after a call to SQLPrepare().
SQL_FALSE = Turns off automatic population of the IPD after a call to SQLPrepare().

The default value of the statement attribute SQL_ATTR_ENABLE_AUTO_IPD is equal to the value of the connection attribute SQL_ATTR_AUTO_IPD.

If the connection attribute SQL_ATTR_ AUTO_IPD is SQL_FALSE, the statement attribute SQL_ATTR_ENABLE_AUTO_IPD cannot be set to SQL_TRUE.

SQL_ATTR_EXTENDED_INDICATORS

A 32-bit integer that eliminates the need to indicate the position in the SQL statement where the contents of the application variables are substituted when the statement is executed. This attribute has the following values:

SQL_EXTENDED_INDICATOR_ENABLE: Enables users to specify values to signify SQL_UNASSIGNED and SQL_DEFAULT_PARAM on the SQLBindParameter / SQLExtendedBind methods.
SQL_EXTENDED_INDICATOR_NOT_SET (default): The user gets an InvalidArgument value error if the SQL_UNASSIGNED and SQL_DEFAULT_PARAM are not enabled before an application tries to use them.
Extended indicators support DB2 for Linux, UNIX, and Windows and for DB2 10 for z/OS data servers starts in DB2 Version 9.7 Fix Pack 2. Extended indicators support DB2 for IBM® i 7.1 data servers starts in DB2 Version 9.7 Fix Pack 5.

SQL_ATTR_FETCH_BOOKMARK_PTR

A pointer that points to a binary bookmark value. When SQLFetchScroll() is called with FetchOrientation equal to SQL_FETCH_BOOKMARK, CLI picks up the bookmark value from this field. This field defaults to a null pointer.

SQL_ATTR_IMP_PARAM_DESC

The handle to the IPD. The value of this attribute is the descriptor allocated when the statement was initially allocated. The application cannot set this attribute.

This attribute can be retrieved by a call to SQLGetStmtAttr(), but not set by a call to SQLSetStmtAttr().

SQL_ATTR_IMP_ROW_DESC

The handle to the IRD. The value of this attribute is the descriptor allocated when the statement was initially allocated. The application cannot set this attribute.

This attribute can be retrieved by a call to SQLGetStmtAttr(), but not set by a call to SQLSetStmtAttr().

SQL_ATTR_INFO_PROGRAMID

A user-defined character string, with a maximum length of 80 bytes, that associates an application with a statement. Once this attribute is set, DB2 UDB for z/OS Version 8 and later associates this identifier with any statements inserted into the dynamic SQL statement cache.

This attribute is only supported for CLI applications accessing DB2 UDB for z/OS Version 8 and later or IBM Informix® Dynamic Servers (IDS).

SQL_ATTR_INSERT_BUFFERING

This attribute enables buffering insert optimization of partitioned database environments. The possible values are: SQL_ATTR_INSERT_BUFFERING_OFF (default), SQL_ATTR_INSERT_BUFFERING_ON, and SQL_ATTR_INSERT_BUFFERING_IGD (duplicates are ignored).

This attribute is not supported when accessing IDS data servers.

SQL_ATTR_INTERLEAVED_PUTDATA

This attribute allows inserting LOB data with SQLParamData and SQLPutData in an interleaving fashion. For example:

// Set the attribute
SQLSetStmtAttr(hstmt,
              SQL_ATTR_INTERLEAVED_PUTDATA,
              TRUE,
              0);

//Bind the parameters with DATA_AT_EXEC indicator
blobInd = SQL_DATA_AT_EXEC;

cliRC = SQLBindParameter (hstmt,          /* statement handle      */
                          1,              /* parameter marker index */
                          SQL_PARAM_INPUT, /* it's input parameter  */
                          SQL_C_CHAR,      /* CLI variable is CHARACTER*/
                          SQL_CLOB,        /* table column is CLOB*/
                          10,              /* length of CLI variable */
                          0,              /* scale of decimal digits*/
                          &data1,          /* pointer to CLI variable*/
                          10,              /* buffer length          */
                          &blobInd);


cliRC = SQLBindParameter (hstmt,          /* statement handle      */
                          2,              /* parameter marker index */
                          SQL_PARAM_INPUT, /* it's input parameter  */
                          SQL_C_CHAR,      /* CLI variable is CHARACTER*/
                          SQL_CLOB,        /* table column is CLOB*/
                          10,              /* length of CLI variable */
                          0,              /* scale of decimal digits*/
                          &data2,          /* pointer to CLI variable*/
                          10,              /* buffer length          */
                          &blobInd);


SQLExecute (hstmt);
valuePtr = (SQLPOINTER) 2;
SQLParamData (hstmt, (SQLPOINTER *)&valuePtr);
//update buffer data2
SQLPutData (hstmt, data2, strlen(data2));
valuePtr = (SQLPOINTER) 1;
SQLParamData (hstmt, (SQLPOINTER *)&valuePtr);
//update buffer data1
SQLPutData (hstmt, data1, strlen(data2));
valuePtr = (SQLPOINTER) 2;
SQLParamData (hstmt, (SQLPOINTER *)&valuePtr);
//update buffer data2
SQLPutData (hstmt, data2, strlen(data2));
valuePtr = (SQLPOINTER) 1;
SQLParamData (hstmt, (SQLPOINTER *)&valuePtr);
//update buffer data1
SQLPutData (hstmt, data1, strlen(data2));

valuePtr = (SQLPOINTER) 0;
SQLParamData (hstmt, (SQLPOINTER *)&valuePtr);

This attribute disables any SQLPutData function streaming that is in effect and causes each of the parameter values to be buffered on the client until the data at the SQL_DATA_AT_EXEC is closed with SQLParamData(0).

SQL_ATTR_INTERLEAVED_STREAM_PUTDATA

This attribute allows inserting LOB data with SQLParamData and SQLPutData in an interleaving fashion with function streaming. Streaming writes the LOB data directly to the connection-level buffer, bypassing the internal statement-level buffer, for improved performance.

Applications get a "Function Sequence Error (CLI0125E)" error if a new attribute is set in middle of SQLExecute, SQLParamData or SQLPutData. This error is also returned whenever there is an incorrect sequence of SQLParamData and SQLPutData.

If this attribute is set for a statement handle of a connection, any other statement handle of the same connection gets a "Function Sequence Error (CLI0125E)" if an operation is performed that uses the connection buffer until all the data has been sent to the server for that statement handle with the SQL_ATTR_INTERLEAVED_STREAM_PUTDATA attribute enabled. All SQLParamData and SQLPutData calls must be complete for the statement handle with the SQL_ATTR_INTERLEAVED_STREAM_PUTDATA attribute enabled before any other statement handle may perform an operation that uses the connection buffer. For more information on limitations when using streaming, see the StreamPutData CLI/ODBC configuration keyword.

To indicate the end of data for all parameters in a set of interleaved parameters, call SQLParamData with a parameter number of (0). Applications should explicitly indicate the end of data for all parameters by calling SQLParamData with a parameter number of 0.

The end of data for a single parameter is indicated by calling SQLParamData with the equivalent negative parameter number. For example, to indicate the end of the data stream for parameter number 4, the application should specify SQLParamData(-4). Applications should always indicate end of data for a parameter using the negative of the parameter number. If applications indicate the end of data for a parameter which is being streamed , CLI is able to stream data for the next parameter. This may result in better performance.

The following example shows interleaving LOB data, how to mark the end of data for a single parameter using negative parameter numbers, and how to indicate the end of data for all parameters using a parameter number of (0):

// Set the SQL_ATTR_INTERLEAVED_STREAM_PUTDATA attribute
SQLSetStmtAttr(hstmt, SQL_ATTR_INTERLEAVED_STREAM_PUTDATA, TRUE, 0);

//Bind the parameters with DATA_AT_EXEC indicator
blobInd = SQL_DATA_AT_EXEC;

//declare the statement handle with parameter marker 
index value of 1,
//input parameter SQL_PARAM_INPUT, CLI variable type SQL_C_CHAR, 
table column type CLOB,
//length of CLI variable 10, scale of decimal digits 10, 
and DATA_AT_EXEC indicator
cliRC = SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, 
SQL_C_CHAR, SQL_CLOB, 
10, 0, &data1, 10, &blobInd);

//declare the next statement handle with 
parameter marker index value of 2
cliRC = SQLBindParameter (hstmt, 2, SQL_PARAM_INPUT, 
SQL_C_CHAR, SQL_CLOB, 
10, 0, &data2, 10, &blobInd);

//declare the next statement handle with 
parameter marker index value of 3
cliRC = SQLBindParameter (hstmt, 3, SQL_PARAM_INPUT, 
SQL_C_CHAR, SQL_CLOB, 
10, 0, &data3, 10, &blobInd);

SQLExecute (hstmt);

//make parameter 2 active
valuePtr = (SQLPOINTER) 2;
SQLParamData (hstmt, (SQLPOINTER *)&valuePtr);  

//buffer data for parameter 2
SQLPutData (hstmt, data2, strlen(data2));

//make parameter 1 active
valuePtr = (SQLPOINTER) 1;
SQLParamData (hstmt, (SQLPOINTER *)&valuePtr);

// stream data for parameter 1
SQLPutData (hstmt, data1, strlen(data2));

//make parameter 2 active 
valuePtr = (SQLPOINTER) 2;
SQLParamData (hstmt, (SQLPOINTER *)&valuePtr);

//buffer data for parameter 2 
SQLPutData (hstmt, data2, strlen(data2));

//make parameter 3 active 
valuePtr = (SQLPOINTER) 3;
SQLParamData (hstmt, (SQLPOINTER *)&valuePtr);

//buffer data for parameter 3 
SQLPutData (hstmt, data1, strlen(data2));

//end of data for parameter 1
valuePtr = (SQLPOINTER) -1;
SQLParamData (hstmt, (SQLPOINTER *)&valuePtr);

//make parameter 2 active
valuePtr = (SQLPOINTER) 2;
SQLParamData (hstmt, (SQLPOINTER *)&valuePtr);

//stream the buffered data for parameter 2
SQLPutData (hstmt, data2, strlen(data2));

//make parameter 3 active valuePtr = (SQLPOINTER) 3;
SQLParamData (hstmt, (SQLPOINTER *)&valuePtr);

//buffer data for parameter 3 
SQLPutData (hstmt, data1, strlen(data2));

//end of data for parameter 3
valuePtr = (SQLPOINTER) -3;
SQLParamData (hstmt, (SQLPOINTER *)&valuePtr);

// indicate end of data for all parameters.
// CLI streams the buffered data for all parameters
valuePtr = (SQLPOINTER) 0;
SQLParamData (hstmt, (SQLPOINTER *)&valuePtr);

SQL_ATTR_KEYSET_SIZE

CLI supports a pure keyset cursor, therefore the SQL_KEYSET_SIZE statement attribute is ignored. To limit the size of the keyset the application must limit the size of the result set by setting the SQL_ATTR_MAX_ROWS attribute to a value other than 0.

This attribute is not supported when accessing IDS data servers.

SQL_ATTR_LOAD_INFO

A pointer to a structure of type db2LoadStruct. The db2LoadStruct structure is used to specify all applicable LOAD options that should be used during CLI LOAD. Note that this pointer and all of its embedded pointers should be valid during every CLI function call from the time the SQL_ATTR_USE_LOAD_API statement attribute is set to the time it is turned off. For this reason, it is recommended that this pointer and its embedded pointers point to dynamically allocated memory rather than locally declared structures.

This attribute is not supported when accessing IDS data servers.

SQL_ATTR_LOAD_MODIFIED_BY

A pointer to a char string that specifies the file type modifier option to be used during CLI LOAD.

SQL_ATTR_LOAD_ROWS_COMMITTED_PTR

A pointer to an integer that represents the total number of rows processed. This value equals the number of rows successfully loaded and committed to the database, plus the number of skipped and rejected rows. The integer is 32-bit on 32-bit platforms and 64-bit on 64-bit platforms.

This attribute is not supported when accessing IDS data servers.

SQL_ATTR_LOAD_ROWS_DELETED_PTR

A pointer to an integer that represents the number of duplicate rows deleted. The integer is 32-bit on 32-bit platforms and 64-bit on 64-bit platforms.

This attribute is not supported when accessing IDS data servers.

SQL_ATTR_LOAD_ROWS_LOADED_PTR

A pointer to an integer that represents the number of rows loaded into the target table. The integer is 32-bit on 32-bit platforms and 64-bit on 64-bit platforms.

This attribute is not supported when accessing IDS data servers.

SQL_ATTR_LOAD_ROWS_READ_PTR

A pointer to an integer that represents the number of rows read. The integer is 32-bit on 32-bit platforms and 64-bit on 64-bit platforms.

This attribute is not supported when accessing IDS data servers.

SQL_ATTR_LOAD_ROWS_REJECTED_PTR

A pointer to an integer that represents the number of rows that could not be loaded. The integer is 32-bit on 32-bit platforms and 64-bit on 64-bit platforms.

This attribute is not supported when accessing IDS data servers.

SQL_ATTR_LOAD_ROWS_SKIPPED_PTR

A pointer to an integer that represents the number of rows skipped before the CLI LOAD operation began. The integer is 32-bit on 32-bit platforms and 64-bit on 64-bit platforms.

This attribute is not supported when accessing IDS data servers.

SQL_ATTR_LOB_CACHE_SIZE

A 32-bit unsigned integer that specifies maximum cache size (in bytes) for LOBs. By default, LOBs are not cached.

See the LOBCacheSize CLI/ODBC configuration keyword for further usage information.

SQL_ATTR_MAX_LENGTH

A 32-bit integer value corresponding to the maximum amount of data that can be retrieved from a single character or binary column.

Note: SQL_ATTR_MAX_LENGTH should not be used to truncate data. The BufferLength argument of SQLBindCol() or SQLGetData() should be used instead for truncating data.

If data is truncated because the value specified for SQL_ATTR_MAX_LENGTH is less than the amount of data available, a SQLGetData() call or fetch returns SQL_SUCCESS instead of returning SQL_SUCCESS_WITH_INFO and SQLSTATE 01004 (Data Truncated). The default value for SQL_ATTR_MAX_LENGTH is 0; 0 means that CLI attempts to return all available data for character or binary type data.

SQL_ATTR_MAX_LOB_BLOCK_SIZE

A 32-bit unsigned integer that indicates the maximum size of LOB or XML data block. Specify a positive integer, up to 2 147 483 647. The default setting of 0 indicates that there is no limit to the data block size for LOB or XML data.

During data retrieval, the server includes all of the information for the current row in its reply to the client even if the maximum block size has been reached.

If both MaxLOBBlockSize and the db2set registry variable DB2_MAX_LOB_BLOCK_SIZE are specified, the value for MaxLOBBlockSize is used.

Setting the MaxLOBBlockSize CLI/ODBC configuration keyword is an alternative method of specifying this behavior.

SQL_ATTR_MAX_ROWS

A 32-bit integer value corresponding to the maximum number of rows to return to the application from a query. The default value for SQL_ATTR_MAX_ROWS is 0; 0 means all rows are returned.

SQL_ATTR_METADATA_ID

This statement attribute is defined by ODBC, but is not supported by CLI. Any attempt to set or get this attribute results in an SQLSTATE of HYC00 (Driver not capable).

This attribute is not supported when accessing IDS data servers.

SQL_ATTR_NOSCAN

A 32-bit integer value that specifies whether CLI scans SQL strings for escape clauses. The two permitted values are:

SQL_NOSCAN_OFF - SQL strings are scanned for escape clause sequences. This is the default.
SQL_NOSCAN_ON - SQL strings are not scanned for escape clauses. Everything is sent directly to the server for processing.

This application can choose to turn off the scanning if it never uses vendor escape sequences in the SQL strings that it sends. Turning off the scanning eliminates some of the overhead processing associated with scanning.

SQL_ATTR_OPTIMIZE_FOR_NROWS

A 32-bit integer value. If it is set to an integer larger than 0, "OPTIMIZE FOR n ROWS" clause is appended to every select statement If set to 0 (the default) this clause is not appended.

The default value can also be set using the OPTIMIZEFORNROWS CLI/ODBC configuration keyword.

SQL_ATTR_OPTIMIZE_SQLCOLUMNS

This attribute has been deprecated.

SQL_ATTR_PARAM_BIND_OFFSET_PTR

A 32-bit integer * value that points to an offset added to pointers to change binding of dynamic parameters. If this field is non-null, CLI dereferences the pointer, adds the dereferenced value to each of the deferred fields in the descriptor record (SQL_DESC_DATA_PTR, SQL_DESC_INDICATOR_PTR, and SQL_DESC_OCTET_LENGTH_PTR), and uses the resulting pointer values at execute time. It is set to null by default.

The bind offset is always added directly to the SQL_DESC_DATA_PTR, SQL_DESC_INDICATOR_PTR, and SQL_DESC_OCTET_LENGTH_PTR fields. If the offset is changed to a different value, the new value is added directly to the value in the descriptor field. The new offset is not added to the field value plus any earlier offsets.

Setting this statement attribute sets the SQL_DESC_BIND_OFFSET_PTR field in the APD header.

SQL_ATTR_PARAM_BIND_TYPE

A 32-bit integer value that indicates the binding orientation to be used for dynamic parameters.

This field is set to SQL_PARAM_BIND_BY_COLUMN (the default) to select column-wise binding.

To select row-wise binding, this field is set to the length of the structure or an instance of a buffer that is bound to a set of dynamic parameters. This length must include space for all of the bound parameters and any padding of the structure or buffer to ensure that when the address of a bound parameter is incremented with the specified length, the result points to the beginning of the same parameter in the next set of parameters. When using the sizeof operator in ANSI C, this behavior is guaranteed.

Setting this statement attribute sets the SQL_DESC_ BIND_TYPE field in the APD header.

SQL_ATTR_PARAM_OPERATION_PTR

A 16-bit unsigned integer * value that points to an array of 16-bit unsigned integer values used to specify whether or not a parameter should be ignored during execution of an SQL statement. Each value is set to either SQL_PARAM_PROCEED (for the parameter to be executed) or SQL_PARAM_IGNORE (for the parameter to be ignored).

A set of parameters can be ignored during processing by setting the status value in the array pointed to by SQL_DESC_ARRAY_STATUS_PTR in the APD to SQL_PARAM_IGNORE. A set of parameters is processed if its status value is set to SQL_PARAM_PROCEED, or if no elements in the array are set.

This statement attribute can be set to a null pointer, in which case CLI does not return parameter status values. This attribute can be set at any time, but the new value is not used until the next time SQLExecDirect() or SQLExecute() is called.

Setting this statement attribute sets the SQL_DESC_ARRAY_STATUS_PTR field in the APD.

SQL_ATTR_PARAM_STATUS_PTR

A 16-bit unsigned integer * value that points to an array of UWORD values containing status information for each row of parameter values after a call to SQLExecDirect() or SQLExecute(). This field is used only if SQL_ATTR_PARAMSET_SIZE is greater than 1.

The status values can contain the following values:

SQL_PARAM_SUCCESS: The SQL statement was successfully executed for this set of parameters.
SQL_PARAM_SUCCESS_WITH_INFO: The SQL statement was successfully executed for this set of parameters; however, warning information is available in the diagnostics data structure.
SQL_PARAM_ERROR: There was an error in processing this set of parameters. Additional error information is available in the diagnostics data structure.
SQL_PARAM_UNUSED: This parameter set was unused, possibly due to the fact that some previous parameter set caused an error that aborted further processing.
SQL_PARAM_DIAG_UNAVAILABLE: CLI treats arrays of parameters as a monolithic unit and so does not generate this level of error information.

Setting this statement attribute sets the SQL_DESC_ARRAY_STATUS_PTR field in the IPD header.

SQL_ATTR_PARAMOPT_ATOMIC

This is a 32-bit integer value which determines, when SQLParamOptions() has been used to specify multiple values for parameter markers, whether the underlying processing should be done via ATOMIC or NOT-ATOMIC Compound SQL. The possible values are:

SQL_ATOMIC_YES - The underlying processing makes use of ATOMIC Compound SQL. This is the default if the target database supports ATOMIC compound SQL.
SQL_ATOMIC_NO - The underlying processing makes use of NON-ATOMIC Compound SQL.

Specifying SQL_ATOMIC_YES when connected to a server that does not support ATOMIC compound SQL results in an error (SQLSTATE is S1C00).

Specifying SQL_ATOMIC_YES when SQL_PARC_BATCH is set to SQL_PARC_BATCH_ENABLE returns the CLI0150E error message. If you want to set SQL_PARC_BATCH to SQL_PARC_BATCH_ENABLE, you must specify SQL_ATOMIC_NO.

SQL_ATTR_PARAMS_PROCESSED_PTR

A 32-bit unsigned integer * record field that points to a buffer in which to return the current row number. As each row of parameters is processed, this is set to the number of that row. No row number is returned if this is a null pointer.

Setting this statement attribute sets the SQL_DESC_ROWS_PROCESSED_PTR field in the IPD header.

If the call to SQLExecDirect() or SQLExecute() that fills in the buffer pointed to by this attribute does not return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, the contents of the buffer are undefined.

SQL_ATTR_PARAMSET_SIZE

A 32-bit unsigned integer value that specifies the number of values for each parameter. If SQL_ATTR_PARAMSET_SIZE is greater than 1, SQL_DESC_DATA_PTR, SQL_DESC_INDICATOR_PTR, and SQL_DESC_OCTET_LENGTH_PTR of the APD point to arrays. The cardinality of each array is equal to the value of this field.

Setting this statement attribute sets the SQL_DESC_ARRAY_SIZE field in the APD header.

Starting in DB2 Version 9.7 Fix Pack 6, array input using SQL_ATTR_PARAMSET_SIZE, inside a trusted procedure body, is supported.

SQL_ATTR_PREFETCH

This attribute has been deprecated.

SQL_ATTR_QUERY_OPTIMIZATION_LEVEL

A 32-bit integer value that sets the query optimization level to be used on the next call to SQLPrepare(), SQLExtendedPrepare(), or SQLExecDirect().

Supported values to use are: -1 (default), 0, 1, 2, 3, 5, 7, and 9.

The SQL_ATTR_QUERY_OPTIMIZATION_LEVEL statement attribute does not set the optimization level for IDS data servers. Informix optimizer directives should be used instead. For more information, see Optimizer directives

SQL_ATTR_QUERY_TIMEOUT

A 32-bit integer value that is the number of seconds to wait for an SQL statement or XQuery expression to execute before aborting the execution and returning to the application. This option can be set and used to terminate long running queries. The default value of 0 means CLI waits indefinitely for the server to complete execution of the SQL statement. CLI supports non-zero values for all platforms that support multithreading.

When using this attribute against a server which does not have native interrupt support (such as DB2 for z/OS and OS/390®, Version 7 and earlier, and DB2 for i), the INTERRUPT_ENABLED option must be set when cataloging the DCS database entry for the server.

When the INTERRUPT_ENABLED option is set and this attribute is set to a non-zero value, the DB2 for i server drops the connection and rolls back the unit of work. The application receives an SQL30081N error indicating that the connection to the server has been terminated. In order for the application to process additional database requests, the application must establish a new connection with the database server.

The SQL_ATTR_QUERY_TIMEOUT can also interrupt a LOAD, which returns SQL3005N instead of SQL0952N.

SQL_ATTR_REOPT

A 32-bit integer value that enables query optimization for SQL statements that contain special registers or parameter markers. Optimization occurs by using the values available at query execution time for special registers or parameter markers, instead of the default estimates that are chosen by the compiler. The valid values of the attribute are:

2 = SQL_REOPT_NONE. This is the default. No query optimization occurs at query execution time. The default estimates chosen by the compiler are used for the special registers or parameter markers. The default NULLID package set is used to execute dynamic SQL statements.
3 = SQL_REOPT_ONCE. Query optimization occurs once at query execution time, when the query is executed for the first time. The NULLIDR1 package set, which is bound with the REOPT ONCE bind option, is used.
4 = SQL_REOPT_ALWAYS. Query optimization or reoptimization occurs at query execution time every time the query is executed. The NULLIDRA package set, which is bound with the REOPT ALWAYS bind option, is used.

The NULLIDR1 and NULLIDRA are reserved package set names, and when used, REOPT ONCE and REOPT ALWAYS are implied respectively. These package sets have to be explicitly created with these commands:

db2 bind db2clipk.bnd collection NULLIDR1
db2 bind db2clipk.bnd collection NULLIDRA

SQL_ATTR_REOPT and SQL_ATTR_CURRENT_PACKAGE_SET are mutually exclusive, therefore, if one is set, the other is not allowed.

This attribute is not supported when accessing IDS data servers.

SQL_ATTR_RETRIEVE_DATA

A 32-bit integer value:

SQL_RD_ON = SQLFetchScroll() and in DB2 CLI/v5 and later, SQLFetch(), retrieve data after it positions the cursor to the specified location. This is the default.
SQL_RD_OFF = SQLFetchScroll() and in DB2 CLI/v5 and later, SQLFetch(), do not retrieve data after it positions the cursor.

By setting SQL_RETRIEVE_DATA to SQL_RD_OFF, an application can verify if a row exists or retrieve a bookmark for the row without incurring the overhead of retrieving rows.

SQL_ATTR_RETURN_USER_DEFINED_TYPES

A Boolean attribute that specifies whether user-defined type columns are reported as the user-defined type or the underlying base type when queried by functions such as SQLDescribeCol(). The default value is 0 (false), where columns are reported as the underlying base type.

This attribute is not supported when accessing IDS data servers.

SQL_ATTR_ROW_ARRAY_SIZE

A 32-bit integer value that specifies the number of rows in the rowset. This is the number of rows returned by each call to SQLFetch() or SQLFetchScroll(). The default value is 1.

If the specified rowset size exceeds the maximum rowset size supported by the data source, CLI substitutes that value and returns SQLSTATE 01S02 (Option value changed).

This option can be specified for an open cursor.

Setting this statement attribute sets the SQL_DESC_ARRAY_SIZE field in the ARD header.

SQL_ATTR_ROW_BIND_OFFSET_PTR

A 32-bit integer * value that points to an offset added to pointers to change binding of column data. If this field is non-null, CLI dereferences the pointer, adds the dereferenced value to each of the deferred fields in the descriptor record (SQL_DESC_DATA_PTR, SQL_DESC_INDICATOR_PTR, and SQL_DESC_OCTET_LENGTH_PTR), and uses the new pointer values when binding. It is set to null by default.

Setting this statement attribute sets the SQL_DESC_BIND_OFFSET_PTR field in the ARD header.

SQL_ATTR_ROW_BIND_TYPE

A 32-bit integer value that sets the binding orientation to be used when SQLFetch() or SQLFetchScroll() is called on the associated statement. Column-wise binding is selected by supplying the defined constant SQL_BIND_BY_COLUMN in *ValuePtr. Row-wise binding is selected by supplying a value in *ValuePtr specifying the length of a structure or an instance of a buffer into which result columns are bound.

The length specified in *ValuePtr must include space for all of the bound columns and any padding of the structure or buffer to ensure that when the address of a bound column is incremented with the specified length, the result points to the beginning of the same column in the next row. When using the sizeof operator with structures or unions in ANSI C, this behavior is guaranteed.

Column-wise binding is the default binding orientation for SQLFetch() and SQLFetchScroll().

Setting this statement attribute sets the SQL_DESC_BIND_TYPE field in the ARD header.

SQL_ATTR_ROW_NUMBER

A 32-bit integer value that is the number of the current row in the entire result set. If the number of the current row cannot be determined or there is no current row, CLI returns 0.

This attribute can be retrieved by a call to SQLGetStmtAttr(), but not set by a call to SQLSetStmtAttr().

SQL_ATTR_ROW_OPERATION_PTR

A 16-bit unsigned integer * value that points to an array of UDWORD values used to ignore a row during a bulk operation using SQLSetPos(). Each value is set to either SQL_ROW_PROCEED (for the row to be included in the bulk operation) or SQL_ROW_IGNORE (for the row to be excluded from the bulk operation).

This statement attribute can be set to a null pointer, in which case CLI does not return row status values. This attribute can be set at any time, but the new value is not used until the next time SQLFetch(), SQLFetchScroll(), or SQLSetPos() is called.

Setting this statement attribute sets the SQL_DESC_ARRAY_STATUS_PTR field in the ARD.

SQL_ATTR_ROW_STATUS_PTR

A 16-bit unsigned integer * value that points to an array of UWORD values containing row status values after a call to SQLFetch() or SQLFetchScroll(). The array has as many elements as there are rows in the rowset.

Setting this statement attribute sets the SQL_DESC_ARRAY_STATUS_PTR field in the IRD header.

SQL_ATTR_ROWS_FETCHED_PTR

A 32-bit unsigned integer * value that points to a buffer in which to return the number of rows fetched after a call to SQLFetch() or SQLFetchScroll().

Setting this statement attribute sets the SQL_DESC_ROWS_PROCESSED_PTR field in the IRD header.

This attribute is mapped by CLI to the RowCountPtr array in a call to SQLExtendedFetch().

SQL_ATTR_ROWCOUNT_PREFETCH

This attribute enables CLI to determine the number of rows so that the entire result set can be prefetched. This attribute has one of the following values:

0 (default): Off
1: On

If you set SQL_ATTR_ROWCOUNT_PREFETCH to 0 and call SQLRowCount() using a non-scrollable SELECT-only cursor, the function sets the contents of RowCountPtr to -1 because the number of rows is not available until all of the data has been fetched.

If you set SQL_ATTR_ROWCOUNT_PREFETCH to 1 and call SQLRowCount() using a non-scrollable SELECT-only cursor, the following occurs:

If you use SELECT * FROM INSERT | UPDATE | DELETE statements with forward-only cursors, the row count comes from the SELECT statements. This is different than the rows-affected count that is provided with these cursors without this attribute set.
All cursor data is prefetched. This might take several round trips to the server and a considerable amount of memory on the client.
The prefetched data is not discarded; instead, it is used to satisfy the fetch requests by the application.

This attribute is not applicable to scrollable cursors because they can provide a row count.

Specify this attribute before preparing a statement.

Restriction: SQL_ATTR_ROWCOUNT_PREFETCH is not supported when the cursor contains LOBs or XML.

SQL_ROWSET_SIZE

CLI applications should now use SQLFetchScroll() rather than SQLExtendedFetch(). Applications should also use the statement attribute SQL_ATTR_ROW_ARRAY_SIZE to set the number of rows in the rowset.

A 32-bit integer value that specifies the number of rows in the rowset. A rowset is the array of rows returned by each call to SQLExtendedFetch(). The default value is 1, which is equivalent to making a single SQLFetch() call. This option can be specified even when the cursor is open and becomes effective on the next SQLExtendedFetch() call.

SQL_ATTR_SIMULATE_CURSOR (ODBC 2.0)

This statement attribute is not supported by CLI but is defined by ODBC.

This attribute is not supported when accessing IDS data servers.

SQL_ATTR_STMT_CONCENTRATOR

Starting in Version 9.7 Fix Pack 3a, DB2 supports this statement attribute, which specifies whether dynamic statements that contain literal values use the statement cache.

SQL_STMT_CONCENTRATOR_OFF - The statement concentrator behavior is disabled.
SQL_STMT_CONCENTRATOR_WITH_LITERALS - The statement concentrator with literal behavior is enabled for situations that are supported by the server. For example, the statement concentrator is not enabled if the statement has parameter markers, named parameter markers, or a mix of literals, parameter markers, and named parameter markers.

Setting the StmtConcentrator CLI/ODBC configuration keyword is an alternative method of specifying this behavior.

Note: When this attribute is used against DB2® for z/OS® servers older than Version 10, the request is ignored.

SQL_ATTR_STMTTXN_ISOLATION

See SQL_ATTR_TXN_ISOLATION.

SQL_ATTR_STREAM_GETDATA

A 32-bit unsigned integer that indicates if the data output stream for the SQLGetData() function is optimized. The values are:

0 (default): CLI buffers all the data on the client.
1: For applications that do not need to buffer data and are querying data on a server that supports Dynamic Data Format, specify 1 to indicate that data buffering is not required. The CLI client optimizes the data output stream.

This keyword is ignored if Dynamic Data Format is not supported by the server.

If StreamGetData is set to 1 and CLI cannot determine the number of bytes still available to return in the output buffer, SQLGetData() returns SQL_NO_TOTAL (-4) as the length when truncation occurs. Otherwise, SQLGetData() returns the number of bytes still available.

Setting the StreamGetData CLI/ODBC configuration keyword is an alternative method of specifying this behavior.

SQL_ATTR_TXN_ISOLATION

A 32-bit integer value that sets the transaction isolation level for the current StatementHandle.

This option cannot be set if there is an open cursor on this statement handle (SQLSTATE 24000).

The value SQL_ATTR_STMTTXN_ISOLATION is synonymous with SQL_ATTR_TXN_ISOLATION. However, since the ODBC Driver Manager rejects the setting of SQL_ATTR_TXN_ISOLATION as a statement option, ODBC applications that need to set translation isolation level on a per statement basis must use the manifest constant SQL_ATTR_STMTTXN_ISOLATION instead on the SQLSetStmtAttr() call.

The default transaction isolation level can also be set using the TXNISOLATION CLI/ODBC configuration keyword.

This attribute (or corresponding keyword) is only applicable if the default isolation level is used for the statement handle. If the application has specifically set the isolation level for the statement handle, then this attribute does not have effect.

Note: It is an IBM extension to allow setting this option at the statement level.

SQL_ATTR_USE_BOOKMARKS

A 32-bit integer value that specifies whether an application CLI0150E is returned use bookmarks with a cursor:

SQL_UB_OFF = Off (the default)
SQL_UB_VARIABLE = An application uses bookmarks with a cursor, and CLI provides variable-length bookmarks if they are supported.

To use bookmarks with a cursor, the application must specify this option with the SQL_UB_VARIABLE value before opening the cursor.

SQL_ATTR_USE_LOAD_API

A 32-bit integer that indicates if the LOAD utility replaces the regular CLI array insert for inserting data. The possible values are:

SQL_USE_LOAD_OFF: (Default) Use regular CLI array insert to insert data.
SQL_USE_LOAD_INSERT: Use the LOAD utility to append to existing data in the table.
SQL_USE_LOAD_REPLACE: Use the LOAD utility to replace existing data in the table.
SQL_USE_LOAD_RESTART: Resume a previously failed CLI LOAD operation. If the previous CLI LOAD operation failed while rows were being inserted (that is, before the SQL_ATTR_USE_LOAD_API statement attribute was set to SQL_USE_LOAD_OFF), the CLI LOAD feature remains active, and subsequent rows are inserted by the CLI LOAD utility. Otherwise, if the operation failed while CLI LOAD was being turned off, regular CLI array inserts resume after the restarted load completes.
SQL_USE_LOAD_TERMINATE: Clean up and undo a previously failed CLI LOAD operation. After setting the statement attribute to this value, regular CLI array inserts resume.

This attribute is not supported when accessing IDS data servers.

Note: Starting from Version 9.7, Fix Pack 4, this attribute can be used with SQL_ATTR_ASYNC_ENABLE.

SQL_ATTR_XML_DECLARATION

A 32-bit unsigned integer that specifies which elements of an XML declaration are added to XML data when it is implicitly serialized. This attribute does not affect the result of the XMLSERIALIZE function.

This attribute can only be specified on a statement handle that has no open cursors associated with it. Attempting to update the value of this attribute while there are open cursors on the statement handle results in a CLI0126E (SQLSTATE HY011) error, and the value remains unchanged.

Set this attribute to the sum of each component required:

0: No declarations or byte order marks (BOMs) are added to the output buffer.
1: A byte order mark (BOM) in the appropriate endianness is prepended to the output buffer if the target encoding is UTF-16 or UTF-32. (Although a UTF-8 BOM exists, DB2 does not generate it, even if the target encoding is UTF-8.)
2: A minimal XML declaration is generated, containing only the XML version.
4: An encoding attribute that identifies the target encoding is added to any generated XML declaration. Therefore, this setting only has effect when the setting of 2 is also included when computing the value of this attribute.

Attempts to set any other value using SQLSetStmtAttr() or SQLSetStmtOption() results in a CLI0191E (SQLSTATE HY024) error, and the value remains unchanged.

The default setting is 7, which indicates that a BOM and an XML declaration containing the XML version and encoding attribute are generated during implicit serialization.

This attribute can also be specified on a connection handle and affects any statement handles allocated after the value is changed. Existing statement handles retain their original values.

This attribute is not supported when accessing IDS data servers.

SQL_ATTR_XQUERY_STATEMENT

A 32-bit integer value that specifies whether the statement associated with the current statement handle is an XQuery expression or an SQL statement or query. This can be used by CLI applications that do not want to prefix an XQuery expression with the "XQUERY" keyword. The supported values are:

SQL_TRUE: The next statement executed on the current statement handle is processed as an XQuery expression. If the server does not support XQuery, setting this attribute to SQL_TRUE results in a warning, CLI0005W (SQLSTATE 01S02), and the attribute's value is unchanged.
SQL_FALSE (default): The next statement executed on the current statement handle is processed as an SQL statement.

This attribute takes effect on the next SQLPrepare() or SQLExecDirect() function call.

This attribute is not supported when accessing IDS data servers.