SQLSetConnectAttr - Set a connection attribute

SQLSetConnectAttr() sets connection attributes for a particular connection.

Unicode (UTF-16) equivalent: This function can also be used with the Unicode (UTF-16) character set. The corresponding Unicode function is SQLSetConnectAttrW(). Refer to Unicode in Db2 for i CLI for more information about Unicode support for Db2 for iCLI.

Syntax

SQLRETURN SQLSetConnectAttr  (SQLHDBC     hdbc,
                              SQLINTEGER  fAttr,
                              SQLPOINTER  vParam,
                              SQLINTEGER  sLen);

Function arguments

Table 1. SQLSetConnectAttr arguments
Data type	Argument	Use	Description
SQLHDBC	hdbc	Input	Connection handle.
SQLINTEGER	fAttr	Input	Connect attribute to set, refer to Table 2 for more information.
SQLPOINTER	vParam	Input	Value associated with fAttr. Depending on the option, this can be a pointer to a 32-bit integer value, or a character string.
SQLINTEGER	sLen	Input	Length of input value, if it is a character string; otherwise, unused.

Usage

All connection and statement options set through the SQLSetConnectAttr() persist until SQLFreeConnect() is called or the next SQLSetConnectAttr() call.

The format of information set through vParam depends on the specified fAttr. The option information can be either a 32-bit integer or a pointer to a null-terminated character string.

Table 2. Connect options
fAttr	Contents
SQL_ATTR_2ND_LEVEL_TEXT	A 32-bit integer value: SQL_TRUE – Error text obtained by calling `SQLError()` contains the complete text description of the error. SQL_FALSE – Error text obtained by calling `SQLError()` contains the first-level description of the error only. This is the default.
SQL_ATTR_AUTOCOMMIT	A 32-bit value that sets the commit behavior for the connection. These are the possible values: SQL_TRUE – Each SQL statement is automatically committed as it is processed. SQL_FALSE – The SQL statements are not automatically committed. If running with commitment control, changes must be explicitly committed or rolled back using either `SQLEndTran()` or `SQLTransact()`. This is the default.
SQL_ATTR_CONCURRENT_ACCESS_RESOLUTION	A 32-bit integer value that specifies the concurrent access resolution to use at the statement level. This attribute only applies to the transaction isolation level of Cursor Stability or Read Stability, otherwise, it is ignored. These are the possible values : SQL_USE_CURRENTLY_COMMITTED -- Use currently committed semantics.Db2 for iCLI flows "currently committed" on every prepare, which means that the database manager can use the currently committed version of the data for applicable scans when the data is in the process of being updated or deleted. Rows in the process of being inserted that have not been committed are skipped. SQL_WAIT_FOR_OUTCOME -- Wait for outcome. Db2 for iCLI flows "wait for outcome" on every prepare, which causes the application to wait for conflicting row locks held by other users to be released when encountering rows in the process of being updated. Rows in the process of being inserted or deleted rows are not skipped. SQL_SKIP_LOCKED_DATA -- Skip locked data. Rather than waiting for conflicting row locks to be released, Db2 for i skips those rows which have conflicting locks held by another user. As a result, skipped rows are not returned in the result set returned to CLI. CLI flows "skip locked data" on every prepare.
SQL_ATTR_CONN_SORT_SEQUENCE	A 32-bit integer value that specifies the sort sequence to use with the connection. The possible values are: SQL_HEX_SORT_SEQUENCE – use *HEX sort sequence. SQL_JOB_SORT_SEQUENCE – Extract sort sequence from the job in which the CLI API requests are being made and use that sort sequence. SQL_JOBRUN_SORT_SEQUENCE – Extract sort sequence from the job in which the database access is done and use that sort sequence. The distinction between SQL_JOB_SORT_SEQUENCE and SQL_JOBRUN_SORT_SEQUENCE will only be seen when running in server-mode. In that case, the SQL_JOBRUN_SORT_SEQUENCE will cause the effective sort sequence of the server-mode job to be used, rather the front-end job where the CLI is being executed.
SQL_ATTR_COMMIT or SQL_TXN_ISOLATION	A 32-bit value that sets the transaction-isolation level for the current connection referenced by hdbc. The following values are accepted by Db2 for i CLI, but each data source might only support some of these isolation levels: SQL_TXN_NO_COMMIT – Commitment control is not used. SQL_TXN_READ_UNCOMMITTED – Dirty reads, nonrepeatable reads, and phantoms are possible. This is the default isolation level. SQL_TXN_READ_COMMITTED – Dirty reads are not possible. Non-repeatable reads and phantoms are possible. SQL_TXN_REPEATABLE_READ – Dirty reads and nonrepeatable reads are not possible. Phantoms are possible. SQL_TXN_SERIALIZABLE – Transactions are serializable. Dirty reads, non-repeatable reads, and phantoms are not possible. In IBM® terminology, SQL_TXN_READ_UNCOMMITTED is uncommitted read SQL_TXN_READ_COMMITTED is cursor stability SQL_TXN_REPEATABLE_READ is read stability SQL_TXN_SERIALIZABLE is repeatable read For a detailed explanation of isolation levels, refer to the Db2 for i SQL Reference.
SQL_ATTR_CURRENT_IMPLICIT_XMLPARSE_OPTION	A null-terminated character string that is the string constant used to set the CURRENT IMPLICIT XMLPARSE OPTION special register. Setting this attribute causes the SET CURRENT IMPLICIT XMLPARSE OPTION SQL statement to be issued. If this attribute is set before a connection has been established, the SET CURRENT IMPLICIT XMLPARSE OPTION SQL statement will be issued when the connection is made. The valid values include: STRIP WHITESPACE In the XML Standard, whitespace is space characters (U+0020), carriage returns (U+000D), line feeds (U+000A), or tabs (U+0009) that are in the document to improve readability. Boundary whitespace is whitespace characters that appear between elements. The STRIP WHITESPACE option removes whitespace. PRESERVE WHITESPACE Whitespace is not removed. The default value of the CURRENT IMPLICIT XMLPARSE OPTION special register is 'STRIP WHITESPACE'.
SQL_ATTR_DATE_FMT	A 32-bit integer value: SQL_FMT_ISO – The International Organization for Standardization (ISO) date format yyyy-mm-dd is used. This is the default. SQL_FMT_USA – The United States date format mm/dd/yyyy is used. SQL_FMT_EUR – The European date format dd.mm.yyyy is used. SQL_FMT_JIS – The Japanese Industrial Standard date format yyyy-mm-dd is used. SQL_FMT_MDY – The date format mm/dd/yy is used. SQL_FMT_DMY – The date format dd/mm/yy is used. SQL_FMT_YMD – The date format yy/mm/dd is used. SQL_FMT_JUL – The Julian date format yy/ddd is used. SQL_FMT_JOB – The job default is used.
SQL_ATTR_DATE_SEP	A 32-bit integer value: SQL_SEP_SLASH – A slash ( / ) is used as the date separator. This is the default. SQL_SEP_DASH – A dash ( - ) is used as the date separator. SQL_SEP_PERIOD – A period ( . ) is used as the date separator. SQL_SEP_COMMA – A comma ( , ) is used as the date separator. SQL_SEP_BLANK – A blank is used as the date separator. SQL_SEP_JOB – The job default is used. Separators only apply to the following SQL_ATTR_DATE_FMT attribute types: SQL_FMT_MDY SQL_FMT_DMY SQL_FMT_YMD SQL_FMT_JUL
SQL_ATTR_DBC_DEFAULT_LIB	A character value that indicates the default library that is used for resolving unqualified file references.
SQL_ATTR_DBC_SYS_NAMING	A 32-bit integer value: SQL_TRUE – Db2 for i CLI uses the IBM i system naming mode. Files are qualified using the slash (/) delimiter. Unqualified files are resolved using the library list for the job. SQL_FALSE – Db2 for i CLI uses the default naming mode, which is SQL naming. Files are qualified using the period (.) delimiter. Unqualified files are resolved using either the default library or the current user ID.
SQL_ATTR_DECFLOAT_ROUNDING_MODE	A 32-bit integer value: ROUND_CEILING ROUND_DOWN ROUND_FLOOR ROUND_HALF_DOWN ROUND_HALF_EVEN - This is the default. ROUND_HALF_UP ROUND_UP Specifying this attribute causes the decimal floating point rounding mode to be set in the following manner: For a local non-server mode connection, the local job will use the specified rounding mode. For a local server mode connection, the server job will use the specified rounding mode. For a remote connection, the application requestor's job will use the rounding mode specified on the connection attribute. Additionally, a SET CURRENT DECFLOAT ROUNDING MODE statement will be sent to the application server to set the initial rounding mode there. Applications should avoid setting the rounding mode using an SQL statement. Using the SET CURRENT DECFLOAT ROUNDING MODE statement will have no effect on the current connection if a local connection has been made. Executing the SQL statement for a remote connection will change the rounding mode for the application server, but will not affect the rounding mode in the application requestor job.
SQL_ATTR_DECIMAL_SEP	A 32-bit integer value: SQL_SEP_PERIOD – A period ( . ) is used as the decimal separator. This is the default. SQL_SEP_COMMA – A comma ( , ) is used as the decimal separator. SQL_SEP_JOB – The job default is used.
SQL_ATTR_EXTENDED_COL_INFO	A 32-bit integer value: SQL_TRUE – Statement handles allocated against this connection handle can be used on `SQLColAttribute()` to retrieve extended column information, such as base table, base schema, base column, and label. SQL_FALSE – Statement handles allocated against this connection handle cannot be used on the `SQLColAttribute()` function to retrieve extended column information. This is the default.
SQL_ATTR_EXTENDED_INDICATORS	A 32-bit integer value: SQL_TRUE – Extended indicator support will be enabled. The user will be able to specify values to signify UNASSIGNED and DEFAULT on the SQLBindParameter API. SQL_FALSE – Extended indicator support is not enabled. This is the default.
SQL_ATTR_FREE_LOCATORS	A pointer to an array of 32-bit integer values containing the locator handles to be freed. The sLen parameter indicates the number of locators to be freed. A special value of '-99' for the sLen parameter indicates that all locators and locator storage that has been allocated up to that point in the connection should be freed. A non-null pointer to the array of locator handles must still be passed, though it is not used.
SQL_ATTR_HEX_LITERALS	A 32-bit integer value: SQL_HEX_IS_CHAR – Hexadecimal constants are treated as character data. This is the default. SQL_HEX_IS_BINARY – Hexadecimal constants are treated as binary data.
SQL_ATTR_INFO_ACCTSTR	A character value used to identify the client accounting string that is sent to the host database server at connect time. Db2 for i servers support a length of up to 255 characters. When the value is being set, some servers might not handle the entire length provided and might truncate the value. To ensure that the data is converted correctly when transmitted to a host system, use only the characters A to Z, 0 to 9, and the underscore (_) or period (.).
SQL_ATTR_INFO_APPLNAME	A character value used to identify the client application name that is sent to the host database server at connect time.Db2 for i servers support a length of up to 255 characters. When the value is being set, some servers might not handle the entire length provided and might truncate the value. To ensure that the data is converted correctly when transmitted to a host system, use only the characters A to Z, 0 to 9, and the underscore (_) or period (.).
SQL_ATTR_INFO_PROGRAMID	A character value used to identify the client program name that is sent to the host database server at connect time.Db2 for i servers support a length of up to 255 characters. When the value is being set, some servers might not handle the entire length provided and might truncate the value. To ensure that the data is converted correctly when transmitted to a host system, use only the characters A to Z, 0 to 9, and the underscore (_) or period (.).
SQL_ATTR_INFO_USERID	A character value used to identify the client user-id that is sent to the host database server at connect time.Db2 for i servers support a length of up to 255 characters. When the value is being set, some servers might not handle the entire length provided and might truncate the value. This user-id is not to be confused with the authentication user-id. This user-id is for identification purposes only and is not used for any authorization. To ensure that the data is converted correctly when transmitted to a host system, use only the characters A to Z, 0 to 9, and the underscore (_) or period (.).
SQL_ATTR_INFO_WRKSTNNAME	A character value used to identify the client workstation name that is sent to the host database server at connect time. Db2 for i servers support a length of up to 255 characters. When the value is being set, some servers might not handle the entire length provided and might truncate the value. To ensure that the data is converted correctly when transmitted to a host system, use only the characters A to Z, 0 to 9, and the underscore (_) or period (.).
SQL_ATTR_MAX_PRECISION	An integer constant that is the maximum precision (length) that should be returned for the result data types. The value can be 31 or 63. The default value is 31.
SQL_ATTR_MAX_SCALE	An integer constant that is the maximum scale (number of decimal positions to the right of the decimal point) that should be returned for the result data types. The value can range from 0 to the maximum precision. The default value is 31.
SQL_ATTR_MIN_DIVIDE_SCALE	Specify the minimum divide scale (number of decimal positions to the right of the decimal point) that should be returned for the result data types resulting from a divide operation. The value can range from 0 to 9, not to exceed the maximum scale. If 0 is specified, minimum divide scale is not used. The default value is 0.
SQL_ATTR_OLD_MTADTA_BEHAVIOR	A 32-bit integer value: SQL_TRUE – Run with the internal implementation for meta-data APIs as defined before V6R1M0. Compatibility with other DB2® CLI meta-data APIs is not guaranteed if this option is set. This is not recommended. SQL_FALSE – Run with the new internal implementation for meta-data APIs. This is the default. Meta-data APIs are functions that query the DB2 catalogs such as `SQLTables`, `SQLColumns()`, and `SQLStatistics()`.
SQL_ATTR_NULLT_ARRAY_RESULTS	A 32-bit integer value: SQL_TRUE – DB2 CLI uses null termination to indicate the length of output character string columns in array result set data. SQL_FALSE – DB2 CLI does not null terminate output character string columns in array result set data. This is the default.
SQL_ATTR_NULLT_OUTPUT_PARMS	A 32-bit integer value: SQL_TRUE – DB2 CLI uses null termination to indicate the length of SQL CALL statement output character string parameters. SQL_FALSE – DB2 CLI does not null terminate string output parameters of SQL CALL statement . This is the default.
SQL_ATTR_QUERY_OPTIMIZE_GOAL	A 32-bit integer value that tells the optimizer to behave in a specified way when processing a query: SQL_FIRST_IO – All queries are optimized with the goal of returning the first page of output as fast as possible. This goal works well when the output is controlled by a user who is most likely to cancel the query after viewing the first page of output data. Queries coded with an OPTIMIZE FOR nnn ROWS clause honor the goal specified by the clause. SQL_ALL_IO – All queries are optimized with the goal of running the entire query to completion in the shortest amount of elapsed time. This is a good option when the output of a query is being written to a file or report, or the interface is queuing the output data. Queries coded with an OPTIMIZE FOR nnn ROWS clause honor the goal specified by the clause. This is the default.
SQL_ATTR_SAVEPOINT_NAME	A character value that indicates the savepoint name to be used by `SQLEndTran()` on the functions SQL_SAVEPOINT_NAME_ROLLBACK or SQL_SAVEPOINT_NAME_RELEASE.
SQL_ATTR_SERVERMODE_SUBSYSTEM	A null terminated character string that is used to specify the subsystem in which the associated QSQSRVR jobs will run. The default behavior is to have the jobs run in the QSYSWRK subsystem. If the value *SAME is used, then the QSQSRVR jobs will run in the same subsystem as the job using the CLI API.
SQL_ATTR_TIME_FMT	A 32-bit integer value: SQL_FMT_ISO – The International Organization for Standardization (ISO) time format hh.mm.ss is used. This is the default. SQL_FMT_USA – The United States time format hh:mmxx is used, where xx is AM or PM. SQL_FMT_EUR – The European time format hh.mm.ss is used. SQL_FMT_JIS – The Japanese Industrial Standard time format hh:mm:ss is used. SQL_FMT_HMS – The hh:mm:ss format is used.
SQL_ATTR_TIME_SEP	A 32-bit integer value: SQL_SEP_COLON – A colon ( : ) is used as the time separator. This is the default. SQL_SEP_PERIOD – A period ( . ) is used as the time separator. SQL_SEP_COMMA – A comma ( , ) is used as the time separator. SQL_SEP_BLANK – A blank is used as the time separator. SQL_SEP_JOB – The job default is used.
SQL_ATTR_TIMESTAMP_PREC	A 32-bit integer value: SQL_TRUE – Timestamps are treated as fixed length types with a length of 26 and precision of 6. The following functions are affected : SQLBindCol - cbValueMax is ignored and always treated as 26. SQLBindParam - cbParamDef is ignored and always treated as 26. ibScale is ignored and always treated as 6. SQLBindParameter -ColumnSize is ignored and always treated as 26. DecimalDigits is ignored and always treated as 6. SQLColAttribute - SQL_DESC_LENGTH is always 26, SQL_DESC_PRECISION is always 26, SQL_DESC_SCALE is always 6, and SQL_DESC_DISPLAY_SIZE is either 26 or 27, depending on whether connection attribute SQL_ATTR_INCLUDE_NULL_IN_LEN has been set. SQLColAttributes - SQL_DESC_LENGTH is always 26, SQL_DESC_PRECISION is always 26, SQL_DESC_SCALE is always 6, and SQL_DESC_DISPLAY_SIZE is either 26 or 27, depending on whether connection attribute SQL_ATTR_INCLUDE_NULL_IN_LEN has been set. SQLDescribeCol - pcbColDef is always 26 and pibScale is always 6. SQLDescribeParam - ParameterSizePtr is always 26 and DecimalDigitsPtr is always 6. SQLGetDescRec - prec is always 26 and scale is always 6. SQLPutData - cbValue is ignored and treated as 26. SQL_FALSE - Timestamps are treated as varying length types with a length between 19 and 32 and a corresponding precision between 0 and 12. SQL_FALSE is the default.
SQL_ATTR_TXN_EXTERNAL	A 32-bit integer value that must be SQL_TRUE to enable the use of XA transaction setting in the CLI connection. SQL_ATTR_TXN_EXTERNAL must be set to SQL_TRUE to use the XA transaction options by the SQL_ATTR_TXN_INFO connection attribute. The default is SQL_FALSE, which is not to enable XA transaction support. However, as soon as transaction support is enabled for the connection, it cannot be disabled. (Attempting to set SQL_ATTR_TXN_EXTERNAL to SQL_FALSE results in a CLI error.) Further information as well as an example of use of the SQL_ATTR_TXN_EXTERNAL connection attribute can be found in Example: Using the CLI XA transaction connection attributes.
SQL_ATTR_TXN_INFO	A 32-bit integer value: SQL_TXN_CREATE – Create and start a transaction. This parallels the xa_start(TMNOFLAGS) XA option. SQL_TXN_END – End the specified transaction. The user is responsible to commit or roll back the work. This parallels the xa_end(TMSUCCESS) XA option. SQL_TXN_END_FAIL – End the specified transaction and mark the transaction as rollback required. This parallels the xa_end(TMFAIL) XA option. SQL_TXN_CLEAR – Suspend the transaction to work on a different transaction. This parallels the xa_end(TMSUSPEND) XA option. SQL_TXN_FIND – Find, retrieve, and use the nonsuspended transaction specified in vParam for the current connection. This allows work to continue on the open cursors for the previously nonsuspended transaction. This parallels the xa_start(TMJOIN) XA option. SQL_TXN_RESUME – Find, retrieve, and use the suspended transaction specified in vParam for the current connection. This allows work to continue on the open cursors for the previously suspended transaction. This parallels the xa_start(TMRESUME) XA option. Use of this connection attribute requires the user to be running in server mode. Keep in mind, a user cannot toggle between a non-server mode and server mode environment. The input argument vParam must point to a TXN_STRUCT object. This structure can be found in the header file QSYSINC/h.SQLCLI. The xa_info argument for the xa_open XA API must include the THDCTL=C keyword and value when using `SQLSetConnectAttr()`API instead of xa_start and xa_end to start and end XA transaction branch associations. See XA transaction support for commitment control in the Commitment control topic for more information about XA transactions. See XA APIs for more information. See Example: Using the CLI XA transaction connection attributes for more information and an example that shows how you can use the SQL_ATTR_TXN_INFO connection attribute.
SQL_ATTR_TXN_INFO (continued)	When running XA calls through CLI, the return codes from CLI reflect the XA return code specifications. These values can be found in the XA specification documentation, as well as in the XA.h include file. Note that the return code values that are listed in the XA include file take precedence over the CLI return code values when calling XA through this connection attribute.
SQL_ATTR_UCS2	A 32-bit integer value: SQL_TRUE – When using statement handles allocated against this connection handle for `SQLPrepare()` and `SQLExecDirect()` functions, the statement text is passed in the UCS-2 (Unicode) coded character set identifier (CCSID). SQL_FALSE – When using statement handles allocated against this connection handle for `SQLPrepare()` and `SQLExecDirect()` functions, the statement text is passed in the job's CCSID. This is the default.
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. 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 (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 `SQLSetConnectAttr()` or `SQLSetConnectOption()`will result in a CLI0191E (SQLSTATE HY024) error, and the value will remain 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 setting affects any statement handles allocated after the value is changed. Existing statement handles retain their original values..

Return codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 3. SQLSetConnectAttr SQLSTATEs
SQLSTATE	Description	Explanation
HY001	Memory allocation failure	The driver is unable to allocate memory required to support the processing or completion of the function.
HY009	Argument value that is not valid	Given the fAttr value, a value that is not valid is specified for the argument vParam. An fAttr that is not valid value is specified.