Statement attributes (CLI) list
- Statement attribute definition
- Statement attribute values
- Statement attribute default values (when applicable)
- Data source specific limitations
- 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 toSQLGetData()
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 Informix® database server.
- SQL_ALLOW_INTERLEAVED_GETDATA_OFF - This default setting does
not allow applications to call
- SQL_ATTR_APP_PARAM_DESC
- The handle to the APD for subsequent calls to
SQLExecute()
andSQLExecDirect()
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 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_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 theSQLFetch()
function.Setting the AppUsesLOBLocator CLI/ODBC and IBM data server driver 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
SQLAllocHandle()
,SQLCancel()
,SQLSetStmtAttr()
,SQLGetDiagField()
,SQLGetDiagRec()
, orSQLGetFunctions()
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 functions can also be called asynchronously. Starting from Version 9.7, Fix Pack 4, the SQL_ATTR_ASYNC_ENABLE attribute can be used with SQL_ATTR_USE_LOAD_API. - SQL_ATTR_BLOCK_FOR_NROWS
- A 32-bit integer that specifies the required 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 an Informix database server.
- 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. AllSQLExecute()
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 allSQLExecute()
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 an Informix database server.
- 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 that are
connected to the Db2 for z/OS® servers to convert
array input chaining into column-wise array input for INSERT and MERGE
operations. The possible values for the SQL_ATTR_COLUMNWISE_MRI attribute are SQL_COLUMNWISE_MRI_OFF
and SQL_COLUMNWISE_MRI_ON:
- SQL_COLUMNWISE_MRI_OFF (default): The CLI driver does not convert chaining data to column-wise array input.
- SQL_COLUMNWISE_MRI_ON: The CLI driver converts array input chaining to column-wise array input. The Multi-Row Insert (MRI) feature inDb2 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.
The SQL_ATTR_COLUMNWISE_MRI attribute can be specified for use with Db2 for z/OS servers. For connection to servers other than Db2 for z/OS, the CLI driver automatically converts chaining data to row-wise array input.
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.
- If a parameter is bound with the SQL_DATA_AT_EXEC argument in a MERGE or INSERT operation, which require calls to 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()
orSQLPrepare()
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()
orSQLPrepare()
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 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
- An IBM extension with a value that is a
32-bit integer. The value specifies whether the cursor associated with this
StatementHandle is preserved in the same position as before the COMMIT
operation. The value also specifies 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 a 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 or ODBC configuration keyword.
Note: The SQL_ATTR_CURSOR_HOLD attribute does not impact a result set that is returned by a stored procedure. TheResultset
value that is returned by a stored procedure is preserved after a commit or rollback if the cursor in the stored procedure is declared as WITHHOLD. TheResultset
value that is returned by a stored procedure is not preserved after a commit or rollback if the cursor in the stored procedure is not declared as WITHHOLD. - 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()
andSQLExecDirect()
. 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_NONSCROLLABLE = Scrollable cursors are not required
on the statement handle. If the application calls
- 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()
andSQLExecDirect()
. 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 an Informix database server.
- SQL_ATTR_DB2ZLOAD_BEGIN
- This attribute enables CLI to move data from the file into LOAD utility. CLI will call
SQLPUTData() to retrieve data from the file and stream the data directly to the server. The data
serves as the input to the LOAD utility.
The CLI will only execute LOAD utility against the connected Db2 for z/OS server once the attribute is set to SQL_TRUE. Once this attribute has been set, the application cannot issue any prepare or execute statements until the operation ends the LOAD.
When the data streaming is complete, the application can call SQL_ATTR_DB2ZLOAD_END to end the LOAD operation.
Example of setting the SQL_ATTR_DB2ZLOAD_BEGIN attribute:RC = SQLSetStmtAttr( hstmt, SQL_ATTR_DB2ZLOAD_BEGIN, (SQLPOINTER)SQL_TRUE, SQL_IS_INTEGER);
- SQL_ATTR_DB2ZLOAD_END
- This attribute is used to indicate the end of a LOAD operation. The streaming of data to LOAD
utility and Db2 for z/OS will complete the execution of LOAD. Setting this attribute to SQL_TRUE enables the CLI to end
the LOAD utility started by SQL_ATTR_DB2ZLOAD_BEGIN attribute against the connected Db2 for z/OS.
The return value of this attribute will indicate whether the LOAD operation was successful or has failed. Once the LOAD is finished, you may check the status of LOAD using 'SQL_DIAGDB2ZLOAD_RETCODE' and 'SQL_DIAG_DB2ZLOAD_LOAD_MSGS' with SQLGetDiagField()
Example of setting the SQL_ATTR_DB2ZLOAD_BEGIN attribute:RC = SQLSetStmtAttr(hstmt, SQL_ATTR_DB2ZLOAD_END, (SQLPOINTER)SQL_TRUE, SQL_IS_INTEGER);
- SQL_ATTR_DB2ZLOAD_LOADSTMT
- Specifies the utility control statement for the LOAD operation. This statement attribute for LOAD should not be null and should not be more than 32 704 bytes. You may specify the SQL statement in char format. If the LOAD statement is not specified, CLI cannot start the LOAD operation without failure.
- SQL_ATTR_DB2ZLOAD_MSGFILE
-
Specifies the name of the file in which the application can receive the load messages returned by the server.
The user can call this attribute after ending the zload using SQL_ATTR_DB2ZLOAD_END to get the load messages returned by the server.
The file name specified should be an absolute path or relative path, where the application has write permissions.
CLI will create the file if the filename specified by the application doesn't exist. CLI will also truncate the file if it already exists. If any problem occurs in creating or writing to the file, CLI will ignore it silently and proceed.
- SQL_ATTR_DB2ZLOAD_UTILITYID
- specifies the utility-id for the LOAD operation.
A utility-id is a string of single-byte characters which can only be made up of any alphabetic character (A - Z, uppercase and lowercase), any numeric character (0 - 9), and any of the following characters: #, $, @, ¢, !, or period (.). The maximum length of the string value for the utility-id specified should not be greater than 16 single-byte characters. You may specify the utility-id in char format.
If a value for the utility-id is not specified, the driver will generate the default value of the format “ZLDdddmmhhsstttt” where: ddd is the day of the year (001 to 365 or 366), hh is the hour (00 to 23), mm is the minute (00 to 59), ss is the second (00 to 59), and tttt is the last 4 digits of the micro-seconds (0000 to 9999).
Example of setting the SQL_ATTR_DB2ZLOAD_UTILITYID attribute:RC = SQLSetStmtAttr(hstmt, SQL_ATTR_DB2ZLOAD_UTILITYID, (SQLPOINTER)”DSNZLOAD”,SQL_NTS);
- 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 or 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()
.
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_TRUE = Turns on automatic population of the IPD after a call
to
- 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.
- Support for extended indicators in Db2 and in Db2 for z/OS 10 data servers starts in Version 9.7 Fix Pack 2. Support for extended indicators in Db2 for IBM® i 7.1 data servers starts in 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_FREE_MEMORY_ON_STMTCLOSE
- A statement level attribute that specifies whether the SQL_CLOSE action
that is specified with the SQLFreeStmt() function
frees large memory allocation by the CLI driver.
The CLI driver
allocates large memory under the following scenarios:
- When client fetches the LOB columns from a server that does not support the Dynamic Data Format feature.
- When client invokes the
SQLGetData()
function with AllowInterleavedGetData keyword set.
- SQL_FREE_MEMORY_ON_STMTCLOSE_NO: The default value. The memory allocation by the CLI driver for specified scenario is not freed when SQL_CLOSE action takes place.
- SQL_FREE_MEMORY_ON_STMTCLOSE_YES: The memory allocation by the CLI driver for specified scenario is freed when SQL_CLOSE action takes place.
The SQL_ATTR_FREE_MEMORY_ON_STMTCLOSE attribute can also be specified as a connection attribute, affecting all statement handle allocated after the value is set.RC = SQLSetStmtAttr(hstmt, SQL_ATTR_FREE_MEMORY_ON_STMTCLOSE , (SQLPOINTER)SQL_FREE_MEMORY_ON_STMTCLOSE_YES, 0);
Note: This is an IBM defined extension. - 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 toSQLSetStmtAttr()
. - 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 toSQLSetStmtAttr()
. - 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 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 for z/OS Version 8 and later or IBM Informix Dynamic Servers (Informix database server).
- 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 Informix database server.
- SQL_ATTR_INTERLEAVED_PUTDATA
- This attribute allows inserting LOB data with SQLParamData and SQLPutData in
an interleaving fashion. For example:
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).// 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);
- 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 about 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 an Informix database server.
- 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 an Informix database server.
- 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 an Informix database server.
- 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 an Informix database server.
- 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 an Informix database server.
- 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 an Informix database server.
- 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 an Informix database server.
- 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 an Informix database server.
- 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.
For more information, see the LOBCacheSize CLI/ODBC configuration keyword.
- SQL_ATTR_MAXBLKEXT
- A 32-bit integer value that is used to set the maximum number of extra query blocks returned by
a Db2 for z/OS server for a result set.
SQL_ATTR_MAXBLKEXT is a connection and statement level attribute.
- 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 ofIf data is truncated because the value specified for SQL_ATTR_MAX_LENGTH is less than the amount of data available, a
SQLBindCol()
orSQLGetData()
should be used instead for truncating data.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 and IBM data server driver 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 an Informix database server.
- 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.
- 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 or 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()
orSQLExecute()
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()
orSQLExecute()
. 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.
SQLFetch()
,SQLFetchScroll()
, orSQLSetPos()
is called.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()
orSQLExecute()
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.Note: The maximum value allowed for the SQL_ATTR_PARAMSET_SIZE attribute is 32767.
In a non-fenced or trusted routine, array insertion is not supported. If SQL_ATTR_PARAMSET_SIZE is greater than 1, CLI0150E is returned.
- SQL_ATTR_PREFETCH
- This attribute has been deprecated.
- SQL_ATTR_PREFETCH_NROWS
- This attribute enables the CLI to prefetch the
specified number of rows for scrollable cursors, excluding dynamic cursors.
Supported values to use include any integer 1 - 1024. The default value is 0 (where CLI will not prefetch or block the cursor data).
Example of setting the SQL_ATTR_PREFETCH_NROWS attribute:SQINTEGER nrows = 50; RC = SQLSetStmtAttr( hstmt, SQL_ATTR_PREFETCH_NROWS, (SQLPOINTER)nrows, SQL_IS_INTEGER);
- 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()
, orSQLExecDirect()
.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 an Informix database server. Informix optimizer directives should be used instead. For more information, see Optimizer directives
- SQL_ATTR_QUERY_PREFETCH
- A 32-bit integer that enables a client to send a prefetch request to the server when the
SQLFetch () API is called. By default, this attribute is set to 0 (disabled).
This attribute has the following values:
- SQL_QUERY_PREFETCH_ON or 1: Enables the prefetch multiple query blocks feature when the SQLFetch() API is called for forward-only cursor.
- SQL_QUERY_PREFETCH_OFF (default) or 0: Disables the prefetch multiple query blocks feature.
- SQL_QUERY_PREFETCH_DEFAULT or 0: Disables the prefetch multiple query block feature.
RC = SQLSetStmtAttr(hstmt, SQL_ATTR_QUERY_PREFETCH , (SQLPOINTER)SQL_QUERY_PREFETCH_ON, 0);
- 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.
The query timeout does not affect the XA control operations. The XA control operations include operations such as xa_open, xa_prepare, and others.
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 IBM 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 IBM 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.
SQL_ATTR_REOPT and SQL_ATTR_CURRENT_PACKAGE_SET are mutually exclusive, therefore, if one is set, the other is not allowed.db2 bind db2clipk.bnd collection NULLIDR1 db2 bind db2clipk.bnd collection NULLIDRA
This attribute is not supported when accessing an Informix database server.
- 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.
- SQL_RD_ON =
- SQL_ATTR_RETRY_ON_MERGE
- An integer value that specifies the number of retry attempts that can be made for a MERGE
statement with the array input chaining feature enabled. The array input chaining feature can be
enabled for a connection to Db2, Db2 for IBM i, or Db2 for z/OS servers. The
ColumnwiseMRI keyword or the SQL_ATTR_COLUMNWISE_MRI attribute can be used to
enable the array input chaining feature when you are connecting to Db2 for z/OS servers.
The ArrayInputChain keyword with the SQL_ATTR_CHAINING_BEGIN and SQL_ATTR_CHAINING_END statement attributes are used to enable the array input chaining feature when you are connecting to Db2 or Db2 for IBM i servers. For connection to Db2 for IBM i server, the SQL_ATTR_RETRY_ON_MERGE parameter is supported on Db2 for IBM i V7R1 and later releases.
The CLI driver allocates an array buffer that consists of 100 elements for each parameter that you provide by using the SQLBindParameter() or SQLExtendedBind() function. When an error is encountered due to a resource contention issue on the server, the CLI driver constructs an array of failed rows and reruns the statement again for the number of iterations set with the SQL_ATTR_RETRY_ON_MERGE attribute.
- 0 (default): The default value. No retry attempt is made.
- 1-6: The number of retry attempts that can be made. The maximum number of retry attempts is six.
The SQL_ATTR_RETRY_ON_MERGE attribute setting has no effect under the following conditions:- If any of the following data types are bound as a parameter in a MERGE operation:
- SQL_CLOB
- SQL_BLOB
- SQL_LONGVARBINARY
- SQL_LONGVARGRAPHIC
- SQL_DBCLOB
- SQL_XML
- If a parameter is bound with the SQL_DATA_AT_EXEC argument in a MERGE operation, which requires calls to the SQLPutData() and SQLParamData() functions.
- If any of the following statement attributes are specified in a MERGE operation:
- SQL_ATTR_INTERLEAVED_PUTDATA
- SQL_ATTR_INTERLEAVED_STREAM_PUTDATA
- SQL_ATTR_USE_LOAD_API
- If array parameters are specified for a MERGE operation.
- If the SQL_ATTR_INSERT_BUFFERING statement attribute is enabled in a connection to a Db2 server.
- If the CLI driver is unable to allocate required internal buffers to store application data.
- 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 an Informix database server.
- 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()
orSQLFetchScroll()
. 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).
Note: The maximum value allowed for the SQL_ATTR_ROW_ARRAY_SIZE attribute is 32767.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()
orSQLFetchScroll()
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()
andSQLFetchScroll()
.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 toSQLSetStmtAttr()
. - 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()
, orSQLSetPos()
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()
orSQLFetchScroll()
. The array has as many elements as there are rows in the rowset.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()
, orSQLSetPos()
is called.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()
orSQLFetchScroll()
.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 behaviors are observed:- 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 the SQL_ATTR_ROWCOUNT_PREFETCH 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 thanSQLExtendedFetch()
. 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 singleSQLFetch()
call. This option can be specified even when the cursor is open and becomes effective on the nextSQLExtendedFetch()
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 an Informix database server.
- SQL_ATTR_STMT_CONCENTRATOR
- 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 and IBM data server driver 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 and IBM data server driver configuration keyword is an alternative method of specifying this behavior.
- SQL_ATTR_STREAM_OUTPUTLOB_ON_CALL
- The SQL_ATTR_STREAM_OUTPUTLOB_ON_CALL attribute specifies streaming
of OUT parameters of stored procedures that are LOB or XML data type.
You can also specify the SQL_ATTR_STREAM_OUTPUTLOB_ON_CALL attribute
to obtain the data length information for OUT parameters that are
LOB and XML data type. The SQL_ATTR_STREAM_OUTPUTLOB_ON_CALL attribute
can have one of the following values:
- SQL_STREAM_OUTPUTLOB_ON_CALL_FALSE (default): Disables the streaming of OUT parameter data that are LOB or XML data type for CALL statements.
- SQL_STREAM_OUTPUTLOB_ON_CALL_TRUE: Enables the streaming of OUT parameter data that are LOB or XML data type for CALL statements.
You must ensure that the following conditions are satisfied to get the actual data length information and stream the OUT parameter data that are LOB or XML data type:- Specify NULL value for the rgbValue argument of the SQLBindParameters() function for OUT parameters that are LOB and XML data type.
- If application requires the data length information for OUT parameters that are LOB and XML data type, you must pass the valid memory address to an SQLINTEGER variable for the pcbValue argument of the SQLBindParameter() function. Successful CALL statement through use of the SQLExecute() function returns the length information in the SQLINTEGER variable.
- All OUT parameter data from the stored procedure call are retrieved with the SQLGetData() function before you retrieve any result sets with the SQLFetch() function. Calling the SQLFetch() function causes the CLI driver to delete all the data buffers.
- The LOB locators are not used.
- The OUT parameters of stored procedures are not ARRAY form of LOB or XML data type.
- 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 or 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 will use bookmarks with a cursor, and CLI provides variable-length bookmarks if they are supported.
- 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 in instance-based client and instance-less client will use External table to insert new values.
- SQL_USE_LOAD_REPLACE
- Use the LOAD utility to replace existing data in the table in the instance-based client. The instance-less client does not support this value.
- 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 will resume.
- SQL_USE_LOAD_WITH_ET
- Use External table interface to append to existing data in table for both instance-based and instance-less client.
This attribute is not supported when accessing an Informix database server.
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.
SQLSetStmtAttr()
orSQLSetStmtOption()
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 an Informix database server.
- 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.
SQLPrepare()
orSQLExecDirect()
function call.This attribute is not supported when accessing an Informix database server.
- SQL_ATTR_LOAD_REPLACE_OPTION
-
A 32-bit integer that tells CLI LOAD utility to use the value passed after LOAD REPLACE. Based on the value passed, the appropriate action on the dictionary will be taken. The supported values are:
- SQL_LOAD_KEEPDICTIONARY
-
Use KEEPDICTIONARY option after REPLACE. It is the default option for row-organized tables.
- SQL_LOAD_RESETDICTIONARY
-
Use RESETDICTIONARY option after REPLACE. It is the default option for column-organized tables.
- SQL_LOAD_RESETDICTIONARYONLY
-
Use RESETDICTIONARYONLY option after REPLACE. This option is applicable to column-organized tables only.
- SQL_LOAD_REPLACE_DEFAULT
-
Use default option after REPLACE. KEEPDICTIONARY is the default value for row-organized tables, and RESETDICTIONARY is the default value for column-organized tables used internally by db2Load() API.
This attribute has no default value.
This attribute is supported against LUW database servers only. If used against other database servers, SQLSetStmtAttr will not throw any error and this attribute will be ignored. This attribute is supported only through SQLSetStmtAttr() and SQLGetStmtAttr() API's. It is not supported through the deprecated SQLSetStmtOption() and SQLGetStmtOption() API's .
The statement attribute SQL_ATTR_USE_LOAD_API must be set using SQL_USE_LOAD_REPLACE value.