SQLSetConnectAttr - Set a connection attribute

Purpose

SQLSetConnectAttr() sets connection attributes for a particular connection.

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.
Start of change
Table 2. Connect options
fAttr Contents
SQL_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. The following are 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_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® UDB CLI, but each server 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.
  • 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 IBM DB2 SQL Reference.

The SQL_ATTR_COMMIT attribute should be set before the SQLConnect(). If the value is changed after the connection has been established, and the connection is to a remote data source, the change does not take effect until the next successful SQLConnect() for the connection handle.
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.
SQL_ATTR_DBC_DEFAULT_LIB A character value that indicates the default library that is used for resolving unqualified file references. This is not valid if the connection is using system naming mode.
SQL_ATTR_DBC_SYS_NAMING A 32-bit integer value:
  • SQL_TRUE – DB2 UDB CLI uses the iSeries™ system naming mode. Files are qualified using the slash (/) delimiter. Unqualified files are resolved using the library list for the job.
  • SQL_FALSE – DB2 UDB 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_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 SQLColAttributes() 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 SQLColAttributes() function to retrieve extended column information. This is the default.
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_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.
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.
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.

Start of changeSQL_ATTR_QUERY_OPTIMIZE_GOALEnd of change

 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_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.

Start of changeSQL_ATTR_TXN_EXTERNALEnd of change

Start of changeA 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.End of change

Start of changeThe 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.)End of change

Start of changeFurther information as well as an example of use of the SQL_ATTR_TXN_EXTERNAL connection attribute can be found in Example: Use the CLI XA transaction connection attributes. End of change

Start of changeSQL_ATTR_TXN_INFOEnd of change

 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 CLI with XA transactions.

See XA transaction support for commitment control in the Commitment control topic for more information about XA transactions.

See XA API for more information.

See Example: Use 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.

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(), 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(), the statement text is passed in the job's CCSID. This is the default.
SQL_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.
End of change

Return codes

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.
Parent topic: DB2 UDB CLI functions
Related reference
SQLSetStmtOption - Set statement option
SQLSetConnectOption - Set connection option