Connection attributes (CLI) list

• SQL_MODE_READ_ONLY: Indicates that an application intends to perform read operations on data. Therefore, a less restrictive isolation level and locking such as uncommitted read (SQL_TXN_READ_UNCOMMITTED) can be used on transactions. The CLI driver does not ensure that requests to the database are read-only. If an update request is issued, the CLI driver process the update request with the transaction isolation level that is selected as a result of the SQL_MODE_READ_ONLY setting.
• SQL_MODE_READ_WRITE (default): Indicates that an application intends to modify data. The CLI driver uses the default transaction isolation level for the connection where the SQL_MODE_READ_WRITE attribute is specified.

There must not be any outstanding transactions on a connection where the SQL_ATTR_ACCESS_MODE attribute was specified.

Specifies whether an application can call the SQLGetData() function for the previously accessed LOB columns and maintain the data offset position from the previous call to the SQLGetData() function. when querying data servers that support Dynamic Data Format.

• SQL_ALLOW_INTERLEAVED_GETDATA_OFF (default): Specify that an application does not intend to call the SQLGetData() function for the previously accessed LOB columns.
• SQL_ALLOW_INTERLEAVED_GETDATA_ON: Specify that an application intend to call SQLGetData() for previously accessed LOB columns and reads LOB data from where an application stopped reading during the previous read.

Setting the AllowInterleavedGetData CLI/ODBC configuration keyword is an alternative method to specifying the SQL_ATTR_ALLOW_INTERLEAVED_GETDATA attribute.

• SQL_AA_TRUE (default): Specify that an application is an ANSI application. All character data that is passed to and from an application is in the client code page with the ANSI version of the CLI/ODBC functions.
• SQL_AA_FALSE: Specify that an application is a Unicode application. All character data that is passed to and from an application is in Unicode when the Unicode (W) versions of the CLI/ODBC functions are called.

• 1 (default): Indicates use of the LOB locators.
• 0: Indicates that the LOB locators are not used. If the application is querying a server that supports the Dynamic Data Format feature, also known as progressive streaming, the 0 value can be specified to stream the data and avoid use of the LOB locators.

The SQL_ATTR_APP_USES_LOB_LOCATOR attribute is ignored for stored procedure result sets.

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

Setting the AppUsesLOBLocator CLI/ODBC configuration keyword is an alternative method for indicating the use of the LOB locators.

The SQL_ATTR_APPEND_FOR_FETCH_ONLY attribute allows an application to specify when the CLI driver appends the "FOR FETCH ONLY" clause. For example, if the CLI packages are bound with BLOCKING UNAMBIG option, application can specify 0 value for the SQL_ATTR_APPEND_FOR_FETCH_ONLY attribute to prevent cursor blocking or specify 1 to allow cursor blocking.

The SQL_ATTR_APPEND_FOR_FETCH_ONLY can be set either before a connection is allocated or immediately after a connection is established. Application can query the SQL_ATTR_APPEND_FOR_FETCH_ONLY attribute with the SQLGetConnectAttr() function after connection is established or after the SQL_ATTR_APPEND_FOR_FETCH_ONLY attribute is set.

Setting the AppendForFetchOnly CLI/ODBC configuration keyword is an alternative method to specify when the CLI driver appends the "FOR FETCH ONLY" clause.

• SQL_ASYNC_ENABLE_OFF (default): Disables the asynchronous statement execution.
• SQL_ASYNC_ENABLE_ON: Enables the asynchronous statement execution.

This attribute can be set when the SQLGetInfo() function that is called with the SQL_ASYNC_MODE (for the InfoType argument) value returns SQL_AM_CONNECTION or SQL_AM_STATEMENT.

• SQL_TRUE = Automatic population of the IPD after a call to SQLPrepare() is supported by the server.
• SQL_FALSE = Automatic population of the IPD after a call to SQLPrepare() is not supported by the server. Servers that do not support prepared statements will not be able to populate the IPD automatically.

If SQL_TRUE is returned for the SQL_ATTR_AUTO_IPD connection attribute, the statement attribute SQL_ATTR_ENABLE_AUTO_IPD can be set to turn automatic population of the IPD on or off. If SQL_ATTR_AUTO_IPD is SQL_FALSE, SQL_ATTR_ENABLE_AUTO_IPD cannot be set to SQL_TRUE.

The default value of SQL_ATTR_ENABLE_AUTO_IPD is equal to the value of SQL_ATTR_AUTO_IPD.

This connection attribute can be returned by SQLGetConnectAttr(), but cannot be set by SQLSetConnectAttr().

• SQL_AUTOCOMMIT_OFF: Autocommit mode is disabled. An application must explicitly commit or roll back transactions by using SQLEndTran() function calls.
• SQL_AUTOCOMMIT_ON (default): Autocommit mode is enabled. The CLI driver operates in autocommit mode by default. Each SQL statement is implicitly committed. Each statement that is not a query is committed immediately after successful statement execution or rolled back if a failure occurs. Each query is committed immediately after the associated cursor is closed.

  For a coordinated distributed unit of work (XA) connection, the default is SQL_AUTOCOMMIT_OFF.

• SQL_AUTOCOMMIT_DEFERRED: Autocommit mode is enabled, but the COMMIT statement is not sent immediately. Autocommit mode is enabled for subsequent SQL statements. If you set the SQL_AUTOCOMMIT_DEFERRED value, the first subsequent SQL statement is treated as a part of the current transaction.

When the SQL_AUTOCOMMIT_ON value is set, the CLI driver issues a COMMIT statement immediately and autocommit mode is enabled for subsequent SQL statements. Sending the implicit COMMIT statement separately to the database server after each SQL statement execution with the SQL_AUTOCOMMIT_ON setting can negatively impact your application performance.

• SQL_ATTR_CACHE_USRLIBL_YES (default): Specifies that the *USRLIBL value is cached. When the *USRLIBL value is retrieved from the server, the same *USRLIBL value is used for all catalog function calls in the same connection handle.
• SQL_ATTR_CACHE_USRLIBL_NO: Specifies that the *USRLIBL value is not cached. When the *USRLIBL value is specified in the schema list, the current *USRLIBL value is retrieved whenever a catalog function call is made. Every catalog function call requires an additional call to the metadata procedure on the database server.
• SQL_ATTR_CACHE_USRLIBL_REFRESH: Specifies that the current cached *USRLIBL value is removed, causing next call to a catalog function to retrieve the current *USRLIBL value, which is then cached.

The SQL_ATTR_CACHE_USRLIBL attribute is valid only for use with the DB2 for i server.

Example 1: Setting the code page to be used by this database connection

Unicode = 1208;
cliRC = SQLSetConnectAttr(hdbc,
                          SQL_ATTR_CLIENT_CODEPAGE, 
                          (SQLPOINTER)&unicode,  SQL_NTS);  
char *connStr = "DSN=EBCDICDB;"; 
cliRC = SQLDriverConnect (hdbc, (SQLHWND)NULL, connStr, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT);

Example 2: Getting the current value of SQL_ATTR_CLIENT_CODEPAGE

cliRC = SQLConnect(hdbc,
                       (SQLCHAR *)"SAMPLE",
                        SQL_NTS,
                       (SQLCHAR *)"USER1",
                       SQL_NTS,
                       (SQLCHAR *)"PASSWD1",
                       SQL_NTS);

cliRC = SQLGetConnectAttr(hdbc, 
                      SQL_ATTR_CLIENT_CODEPAGE,
                      &codePage, 0, NULL);

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

• SQL_COMMITONEOF_ON: The implicit COMMIT statement is issued following the retrieval of the entire result set and the EOF marker. The SQL_COMMITONEOF_ON value is the default only when you are connecting to DB2 for z/OS Version 9 and later servers.
• SQL_COMMITONEOF_OFF: The COMMIT statement is not implicitly issued following the retrieval of the entire result set and the EOF marker. You must explicitly call the SQLFreeStmt() function to close the cursor and release resources. The SQL_COMMITONEOF_OFF value is the default when you are connecting to database servers other than DB2 for z/OS 9, or version newer than Version 9.

• 0: No setting. The client does not supply a prepare option.
• 1: Use the currently committed semantics. On every prepared statement, the CLI driver specifies the currently committed semantics. The value indicates that the database manager uses the currently committed version of the data for applicable scans when the data is updated or deleted. The uncommitted inserted rows are skipped. The setting value of 1 applies when the isolation level is cursor stability or read stability (for read stability it skips uncommitted inserts only) and is ignored otherwise. Applicable scans include read-only scans that can be part of a read-only statement or a non read-only statement. The settings for the registry variables DB2_EVALUNCOMMITTED, DB2_SKIPDELETED, and DB2_SKIPINSERTED do not apply to scans with the currently committed semantics. However, the settings for these registry variables still apply to scans that do not use the currently committed semantics.
• 2: Wait for outcome. On every prepared statement, the CLI driver specifies the wait for outcome clause. The cursor stability and higher isolation level scans wait for the commit or rollback when the data is updated or deleted. Rows that are being inserted are not skipped. The settings for the registry variables DB2_EVALUNCOMMITTED, DB2_SKIPDELETED, and DB2_SKIPINSERTED no longer apply.
• 3: Skip locked data. On every prepared statement, the CLI driver specifies the skip locked data clause. The currently committed semantics are used and rows that are being inserted are skipped. The option 3 is not supported on DB2 for Linux, UNIX, and Windows servers. If specified, the option 3 setting is ignored.

For DB2 for Linux, UNIX, and Windows servers, use the SQL_ATTR_CONCURRENT_ACCESS_RESOLUTION attribute to override the default behavior for currently committed that is defined by the cur_commit configuration parameter. For DB2 for z/OS servers, use the SQL_ATTR_CONCURRENT_ACCESS_RESOLUTION attribute to enable currently committed behavior. There is no equivalent database configuration parameter available on DB2 for z/OS server for specifying this behavior.

DB2 for i V6R1 server supports only the options 0 and 2. DB2 for i V7R1 and later servers supports all available options for the SQL_ATTR_CONCURRENT_ACCESS_RESOLUTION attribute.

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

• a valid context (allocated by the sqleBeginCtx() DB2 API) to set the context
• a NULL pointer to reset the context

This attribute can only be used when the application is using the DB2 context APIs to manage multi-threaded applications. By default, CLI manages contexts by allocating one context per connection handle, and ensuring that any executing thread is attached to the correct context.

For more information about contexts, refer to the sqleBeginCtx() API.

This attribute is not supported when accessing the Informix® databases.

• An integer between 0 and 999
• SQL_CONN_CATALOG_NODE

If this variable is not set, the target logical node defaults to the logical node which is defined with port 0 on the machine.

This attribute is not supported when accessing Informix database servers.

There is also a corresponding keyword, the ConnectNode CLI/ODBC configuration keyword.

• SQL_CD_FALSE - The connection is still active.
• SQL_CD_TRUE - An error has already happened and caused the connection to the server to be terminated. The application should still perform a disconnect to clean up any CLI resources.

This attribute is used mainly by the Microsoft ODBC Driver Manager 3.5x before pooling the connection.

• SQL_CONCURRENT_TRANS (default): The application can have concurrent multiple connections to any one database or to multiple databases. Each connection has its own commit scope. No effort is made to enforce coordination of transactions. If an application issues a commit using the environment handle on SQLEndTran() and not all of the connections commit successfully, the application is responsible for recovery.
• SQL_COORDINATED_TRANS: The application wishes to have commit and rollbacks coordinated among multiple database connections. This option setting corresponds to the specification of the Type 2 CONNECT in embedded SQL. In contrast to the SQL_CONCURRENT_TRANS setting, the application is permitted only one open connection per database.
  Note: This connection type results in the default for SQL_ATTR_AUTOCOMMIT connection option to be SQL_AUTOCOMMIT_OFF.

If changing this attribute from the default then it must be set before any connections have been established on the environment handle.

It is recommended that the application set this attribute as an environment attribute with a call to SQLSetEnvAttr(), if necessary, as soon as the environment handle has been allocated. However, since ODBC applications cannot access SQLSetEnvAttr(), they must set this attribute using SQLSetConnectAttr() after each connection handle is allocated, but before any connections have been established.

All connections on an environment handle must have the same SQL_ATTR_CONNECTTYPE setting. An environment cannot have a mixture of concurrent and coordinated connections. The type of the first connection will determine the type of all subsequent connections. SQLSetEnvAttr() will return an error if an application attempts to change the connection type while there is an active connection.

The default connect type can also be set using the ConnectType CLI/ODBC configuration keyword.

This connection attribute can be returned by SQLGetConnectAttr(), but cannot be set by SQLSetConnectAttr(). Any attempt to set this attribute will result in an SQLSTATE of HYC00 (Driver not capable).

This attribute is not supported when accessing Informix database servers.

This attribute is best suited for use with ODBC static processing applications, rather than CLI applications.

This attribute is not supported when accessing Informix database servers.

A null-terminated character string that indicates the schema name (collection identifier) that is used to select the package for subsequent SQL statements. Setting this attribute causes the SET CURRENT PACKAGESET SQL statement to be issued. If this attribute is set before a connection, the SET CURRENT PACKAGESET SQL statement will be issued at connection time.

Setting the CurrentPackageSet CLI/ODBC configuration keyword is an alternative method of specifying the schema name.

The following package set names are reserved: NULLID, NULLIDR1, NULLIDRA.

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

This attribute is not supported when accessing Informix database servers.

To reset this option, specify this option with a zero length string or a null pointer for the ValuePtr argument.

This option is useful when the application developer has coded a generic call to SQLColumns() that does not restrict the result set by schema name, but needs to constrain the result set at isolated places in the code.

This option can be set at any time and will be effective on the next SQLColumns() call where the szSchemaName pointer is null.

• SQL_IBMi_FMT_ISO: Specifies the International Standards Organization (ISO) date format of yyyy-mm-dd.
• SQL_IBMi_FMT_USA: Specifies the United States date format of mm/dd/yyyy.
• SQL_IBMi_FMT_EUR: Specifies the European date format of dd.mm.yyyy.
• SQL_IBMi_FMT_JIS: Specifies the Japanese Industrial Standard date format of yyyy-mm-dd.
• SQL_IBMi_FMT_MDY: Specifies the date format of mm/dd/yy.
• SQL_IBMi_FMT_DMY: Specifies the date format of dd/mm/yy.
• SQL_IBMi_FMT_YMD: Specifies the date format of yy/mm/dd.
• SQL_IBMi_FMT_JUL: Specifies the Julian date format of yy/ddd.
• SQL_IBMi_FMT_JOB: Specifies the job default for the date format.

The default value for the SQL_ATTR_DATE_FMT attribute is determined by the DATETIME bind option that is specified for packages. If the default DATETIME bind option is specified for the CLI packages, SQL_IBMi_FMT_ISO is the default value for the SQL_ATTR_DATE_FMT attribute

• SQL_SEP_SLASH: Specifies a slash ( / ) for the date separator.
• SQL_SEP_DASH: Specifies a dash ( - ) for the date separator.
• SQL_SEP_PERIOD: Specifies a period ( . ) for the date separator.
• SQL_SEP_COMMA: Specifies a comma ( , ) for the date separator.
• SQL_SEP_BLANK: Specifies a blank for the date separator.
• SQL_SEP_JOB: Specifies the job default for the date separator.

The default value for the SQL_ATTR_DATE_SEP attribute is determined by the DATETIME bind option that is specified for packages. If the default DATETIME bind option is specified for the CLI packages, SQL_SEP_SLASH is the default value for the SQL_ATTR_DATE_SEP attribute

• SQL_IBMi_FMT_MDY
• SQL_IBMi_FMT_DMY
• SQL_IBMi_FMT_YMD
• SQL_IBMi_FMT_JUL

This connection attribute can be returned by SQLGetConnectAttr(), but cannot be set by SQLSetConnectAttr().

This attribute is not supported when accessing Informix database servers.

This connection attribute can be returned by SQLGetConnectAttr(), but cannot be set by SQLSetConnectAttr().

This attribute is not supported when accessing Informix database servers.

Begins with a three-letter identifier indicating the product, followed by five alphanumeric characters indicating the version, release, and modification level of the product. The characters A-Z indicate a modification level higher than 9. A indicates modification level 10, B indicates modification level 11, and so on. For example, SQL0907C means DB2 Version 9 Release 7 Modification level 12.

If SQLCODE indicates an error condition, then this field identifies the module that returned the error.

This field is also used when a successful connection is completed.

• SQL_DB2EXPLAIN_OFF: Both the Explain Snapshot and the Explain table option facilities are disabled (a SET CURRENT EXPLAIN SNAPSHOT=NO and a SET CURRENT EXPLAIN MODE=NO are sent to the server).
• SQL_DB2EXPLAIN_SNAPSHOT_ON: The Explain Snapshot facility is enabled, and the Explain table option facility is disabled (a SET CURRENT EXPLAIN SNAPSHOT=YES and a SET CURRENT EXPLAIN MODE=NO are sent to the server).
• SQL_DB2EXPLAIN_MODE_ON: The Explain Snapshot facility is disabled, and the Explain table option facility is enabled (a SET CURRENT EXPLAIN SNAPSHOT=NO and a SET CURRENT EXPLAIN MODE=YES are sent to the server).
• SQL_DB2EXPLAIN_SNAPSHOT_MODE_ON: Both the Explain Snapshot and the Explain table option facilities are enabled (a SET CURRENT EXPLAIN SNAPSHOT=YES and a SET CURRENT EXPLAIN MODE=YES are sent to the server).

Before the explain information can be generated, the explain tables must be created.

This statement is not under transaction control and is not affected by a ROLLBACK. The new SQL_ATTR_DB2EXPLAIN setting is effective on the next statement preparation for this connection.

The current authorization ID must have INSERT privilege for the Explain tables.

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

This attribute is not supported when accessing Informix database servers.

• SQL_TRUE: The DB2 for i system naming mode is used. Files are qualified with the slash (/) delimiter. Unqualified files are resolved with the library list for the job.
• SQL_FALSE: The SQL naming mode which is the default value. Files are qualified with the period (.) delimiter. Unqualified files are resolved with either the default library or the current user ID.

cliRC = SQLSetConnectAttr(hdbc,
                          SQL_ATTR_DBC_SYS_NAMING ,
                          (SQLPOINTER)SQL_TRUE,
                          SQL_NTS);

The decimal float rounding mode determines what type of rounding will be used if a value is put into a DECFLOAT variable or column but the value has more digits than are allowed in the DECFLOAT data type. This can occur when inserting, updating, selecting, converting from another type, or as the result of a mathematical operation.

The value of SQL_ATTR_DECFLOAT_ROUNDING_MODE determines the decimal float rounding mode that will be used for new connections unless another mode is specified by a connection attribute for that connection. For any given connection both CLI and DB2 will use the same decimal float rounding mode for all action initiated as part of that connection.

When your applications are connecting to a DB2 for Linux, UNIX, and Windows Version 9.5 server, you must set the decimal float rounding mode on the database client to the same mode that is set on the server. If you set the decimal float rounding mode on the client to a value that is different from the decimal float rounding mode that is set on the database server, the database server will return SQL0713N on connection.

The settings correspond to these decimal float rounding modes:

• 0 = Half even (default)
• 1 = Half up
• 2 = Down
• 3 = Ceiling
• 4 = Floor

The different modes are:

Half even (default)

In this mode CLI and DB2 use the number that will fit in the target variable and that is closest to the original value. If two numbers are equally close, they use the one that is even. This mode produces the smallest rounding errors over large amounts of data.

Half up

In this mode CLI and DB2 use the number that will fit in the target variable and that is closest to the original value. If two numbers are equally close, they use the one that is greater than the original value.

Down

In this mode CLI and DB2 use the number that will fit in the target variable and that is closest to the original value and for which the absolute value is not greater than the absolute value of the original value. You can also think of this as rounding toward zero or as using ceiling for negative values and using floor for positive values.

Ceiling

In this mode CLI and DB2 use the smallest number that will fit in the target variable and that is greater than or equal to the original value.

Floor

In this mode CLI and DB2 use the largest number that will fit in the target variable and that is less than or equal to the original value.

This attribute is not supported when accessing Informix database servers.

• SQL_SEP_PERIOD: Specifies a period ( . ) for the decimal separator.
• SQL_SEP_COMMA: Specifies a comma ( , ) for the decimal separator.
• SQL_SEP_JOB: Specifies the job default for the decimal separator.

The default value for the SQL_ATTR_DECIMAL_SEP attribute is determined by the DECDEL bind option that is specified for packages. If the default DECDEL bind option is specified for the CLI packages, SQL_SEP_PERIOD is the default value for the SQL_ATTR_DECIMAL_SEP attribute.

• 1 = SQL_DESCRIBE_CALL_BEFORE.
• -1 = SQL_DESCRIBE_CALL_DEFAULT.

Setting this attribute can be done using the DescribeCall CLI/ODBC configuration keyword. Refer to the keyword for usage information and descriptions of the available options.

• 0 - no describe information is returned to the client application
• 1 - describe information categorized in level 1 (see Table 2) is returned to the client application
• 2 - (default) describe information categorized in level 2 (see Table 2) is returned to the client application
• 3 - describe information categorized in level 3 (see Table 2) is returned to the client application

The following table lists the fields that form the describe information that the server returns when it receives a prepare or describe request. These fields are grouped into levels, and the SQL_ATTR_DESCRIBE_OUTPUT_LEVEL attribute controls which levels of describe information the CLI driver requests.

SQL_COLUMN_COUNT
SQL_COLUMN_LENGTH
SQL_COLUMN_NULLABLE
SQL_COLUMN_PRECISION
SQL_COLUMN_SCALE
SQL_DESC_CASE_SENSITIVE
SQL_DESC_CONCISE_TYPE
SQL_DESC_COUNT
SQL_DESC_DISPLAY_SIZE
SQL_DESC_FIXED_PREC_SCALE
SQL_DESC_LENGTH
SQL_DESC_LITERAL_PREFIX
SQL_DESC_LITERAL_SUFFIX
SQL_DESC_NULLABLE
SQL_DESC_OCTET_LENGTH
SQL_DESC_PRECISION
SQL_DESC_SEARCHABLE
SQL_DESC_SCALE
SQL_DESC_TYPE
SQL_DESC_UNSIGNED

all fields of level 1 and:
SQL_COLUMN_NAME
SQL_DESC_DISTINCT_TYPE
SQL_DESC_LABEL
SQL_DESC_LOCAL_TYPE_NAME
SQL_DESC_NAME
SQL_DESC_REFERENCE_TYPE
SQL_DESC_STRUCTURED_TYPE
SQL_DESC_TYPE_NAME
SQL_DESC_UNNAMED
SQL_DESC_USER_DEFINED_
         TYPE_CODE
SQL_DESC_USER_TYPE

all fields of levels 1
and 2 and:
SQL_DESC_AUTO_UNIQUE_VALUE
SQL_DESC_BASE_COLUMN_NAME
SQL_DESC_BASE_TABLE_NAME
SQL_DESC_CATALOG_NAME
SQL_DESC_IDENTITY_VALUE
SQL_DESC_SCHEMA_NAME
SQL_DESC_TABLE_NAME
SQL_DESC_UPDATABLE

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

1. DB2® for Linux, UNIX, and Windows Version 10.5 Fix Pack 4 and later fix packs.
2. DB2 for z/OS® Version 11 in new function mode (NFM) with the automatic client reroute and Sysplex workload balancing features enabled.

• SQL_DETECT_READ_ONLY_TXN_ENABLE - indicates that a connection can be seamlessly fail over to a new member when the automatic client reroute feature is enabled, even if the failed statement is not the first SQL statement in a transaction. this setting forces the connected server to return the latest values of special registers and session global variables whenever they are modified.
• SQL_DETECT_READ_ONLY_TXN_DISABLE(default) - disables this feature.

• non-null transaction pointer: The application requests to CLI to change the state of the connection from non-distributed transaction state to distributed state. The connection is enlisted with the Distributed Transaction Coordinator (DTC).
• null: The application requests to CLI to change the state of the connection from distributed transaction state to a non-distributed transaction state.

This attribute is only used in a Microsoft Transaction Server (MTS) environment to enlist or un-enlist a connection with MTS.

• 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): This feature is disabled by default. The user gets an InvalidArgument value error, CLI0124E, if the SQL_UNASSIGNED and SQL_DEFAULT_PARAM are used before enabling this feature using SQL_ATTR_EXTENDED_INDICATORS.
• Extended indicators support is supported on following IBM Data Server products:
  • The DB2 for Linux, UNIX, and Windows database server.
  • The DB2 for z/OS Version 10 server.
  • The DB2 for i V7.1 data servers server.

To call the SQLEndTran() function specifying SQL_ROLLBACK as CompletionType in your applications during a data-at-execution flow, the StreamPutData configuration keyword must be set to 1, and the SQL_ATTR_FORCE_ROLLBACK connection attribute must also be set.

The CLI0150E error message is returned for connections to database servers that are not DB2 for z/OS servers.

• When client fetches the LOB columns from a server that does not support the Dynamic Data Format feature.
• When client calls 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.

cliRC = SQLSetConnectAttr(hdbc,
                          SQL_ATTR_FREE_MEMORY_ON_STMTCLOSE ,
                          (SQLPOINTER)SQL_FREE_MEMORY_ON_STMTCLOSE_YES,
                          SQL_IS_INTEGER);

When the workload balancing (WLB) feature is enabled, the last member address can be a member address, primary group address, or alternate group address. If WLB is not enabled, the SQL_ATTR_GET_LATEST_MEMBER attribute returns the DSN information that is used to establish the logical connection. The DSN information that is returned can be a primary group address or an alternate group address. The DSN information is obtained from the local db2dsdriver.cfg file, or the node directory if the db2dsdriver.cfg file is not in use. The alternate group address is obtained from the local db2dsdriver.cfg file. The value returned is an IP address and a port, expressed as a port number or service name. The format of the returned value is: "hostname:port".

The CLI0106E error message is returned if you try to use this attribute before establishing a database connection.

The SQL_ATTR_GET_LATEST_MEMBER_NAME attribute can be specified only when the client affinities feature is enabled with the db2dsdriver.cfg file.

The member name information is obtained from the <alternateserverlist> subsection of the local db2dsdriver.cfg file.

For connection to DB2 for z/OS servers, the SQL_ATTR_INFO_ACCTSTR attribute is replayed upon connection failover when the automatic client reroute (ACR) feature and the workload balance (WLB) feature are enabled.

The SQL_ATTR_INFO_ACCTSTR attribute is an IBM defined attribute.

For connection to DB2 for z/OS servers, the SQL_ATTR_INFO_APPLNAME attribute is replayed upon connection failover when the automatic client reroute (ACR) feature and the workload balance (WLB) feature are enabled.

If you change the client application name and the accounting string is set by the WLM_SET_CLIENT_INFO procedure, the accounting string that is stored on the server is updated with the value of the accounting string from the client information.

The SQL_ATTR_INFO_APPLNAME attribute is an IBM defined attribute.

The default SQL_ATTR_INFO_CRRTKN attribute value is the DRDA® correlation token that is generated during a connection. A database client typically generates the DRDA correlation token value. However, if the database client cannot generate the value, the database server generates the value.

The SQL_ATTR_INFO_CRRTKN attribute is an IBM defined attribute.

This attribute is only supported for CLI applications accessing DB2 for z/OS Version 8 and later or Informix database server.

When this attribute is set before the connection to the server is established, the value specified overrides the actual client application name and will be the value that is displayed in the appl_name monitor element. When connecting to a DB2 for z/OS server, the first 12 characters of this setting are used as the CORRELATION IDENTIFIER of the associated DB2 for z/OS thread.

 The CLI driver has limit of 255 characters for the SQL_ATTR_INFO_USERID attribute.

For connection to DB2 for z/OS servers, the SQL_ATTR_INFO_USERID attribute is replayed upon connection failover when the automatic client reroute (ACR) feature and the workload balance (WLB) feature are enabled.

If you change the client user ID and the accounting string is set by the WLM_SET_CLIENT_INFO procedure, the accounting string that is stored on the server is updated with the value of the accounting string from the client information.

The SQL_ATTR_INFO_USERID attribute is an IBM defined attribute.

If the SQL_ATTR_INFO_WRKSTNNAME attribute is not specified, a default value that consists of the host name is used. The host name is obtained by calling the gethostname() function. If the host name is not configured or an error is encountered during the gethostname() function call, no value for the SQL_ATTR_INFO_WRKSTNNAME attribute is sent to the server.

For connection to DB2 for z/OS servers, the SQL_ATTR_INFO_WRKSTNNAME attribute is replayed upon connection failover when the automatic client reroute (ACR) feature and the workload balance (WLB) feature are enabled.

The SQL_ATTR_INFO_WRKSTNNAME attribute is an IBM defined attribute.

• 0 - KEEPDYNAMIC functionality is not available; CLI packages were bound with the KEEPDYNAMIC NO option
• 1 - KEEPDYNAMIC functionality is available; CLI packages were bound with the KEEPDYNAMIC YES option

It is recommended that when this attribute is set, the SQL_ATTR_CURRENT_PACKAGE_SET attribute also be set.

This attribute is not supported when accessing Informix database servers.

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

Setting a connection timeout value can also be done using the ConnectTimeout CLI/ODBC configuration keyword. Refer to the keyword for usage information.

• SQL_LD_COMPAT_NO (default): The large object data types are reported as IBM defined types (SQL_BLOB, SQL_CLOB, SQL_DBCLOB).
• SQL_LD_COMPAT_YES: The IBM large object data types (SQL_BLOB, SQL_CLOB and SQL_DBCLOB) are mapped to SQL_LONGVARBINARY, SQL_LONGVARCHAR and SQL_LONGVARGRAPHIC; SQLGetTypeInfo() returns one entry each for SQL_LONGVARBINARY SQL_LONGVARCHAR, and SQL_LONGVARGRAPHIC.

• SQL_MAPCHAR_DEFAULT (default): return the default SQL type representation
• SQL_MAPCHAR_WCHAR: return SQL_CHAR as SQL_WCHAR, SQL_VARCHAR as SQL_WVARCHAR, and SQL_LONGVARCHAR as SQL_WLONGVARCHAR

Setting the default SQL type associated with SQL_CHAR, SQL_VARCHAR, SQL_LONGVARCHAR can also be done using the MapCharToWChar CLI/ODBC configuration keyword.

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 is reached.

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

Setting the MaxLOBBlockSize CLI/ODBC configuration keyword is an alternative method of specifying the maximum size of LOB or XML data block.

SQL_NETWORK_STATISTICS_OFF (default)

Disables network statistics collection for a connection.

SQL_NETWORK_STATISTICS_ON

Enables network statistics collection for a connection.

SQL_NETWORK_STATISTICS_ON_SKIP_NOSERVER

In addition to enabling network statistics collection for a connection, network flows are omitted that are known to have no server time reported, for example explicit COMMIT and ROLLBACK statements.

Requests that have no server time reported can affect the usefulness of returned information, if calculations are made that subtract the server time from the network time. The SQL_NETWORK_STATISTICS_ON_SKIP_NOSERVER option excludes these requests from the values reported. Only explicit, unchained requests are excluded; autocommit and chained COMMIT statements are not skipped.

The CLI driver collects statistics for server time reported on COMMIT and ROLLBACK. The IBM data server must support the feature that reports server time for COMMIT and ROLLBACK.

If you specify the same code page as the database code page, applications can fetch or insert data of CHARACTER data type without any code page conversions.

Setting the SQL_ATTR_OVERRIDE_CHARACTER_CODEPAGE attribute after allocating statement handles results in the CLI0126E (invalid operation) error. Setting the attribute to a value that is not supported by the database results in the CLI0210E error (inconsistent code page value error).

If you set SQL_ATTR_OVERRIDE_CHARACTER_CODEPAGE after setting SQL_ATTR_OVERRIDE_CODEPAGE to SQL_OVERRIDE_CODEPAGE_ON, the CLI driver returns the CLI0126E error message.

During a bind-out operation, ensure that the CLI applications allocate buffers large enough to hold the retrieved data during bind-out operations. If there is insufficient space, the CLI0002W error is returned.

• SQL_OVERRIDE_CODEPAGE_ON: The CLI driver does not perform code page conversions for binding of character or graphic data.
• SQL_OVERRIDE_CODEPAGE_OFF (default): The CLI driver performs code page conversions for binding of character or graphic data.

If you set the SQL_ATTR_OVERRIDE_CODEPAGE attribute to SQL_OVERRIDE_CODEPAGE_ON, you must ensure that data is in the correct code page.

If you set SQL_ATTR_OVERRIDE_CODEPAGE to SQL_OVERRIDE_CODEPAGE_ON after setting SQL_ATTR_OVERRIDE_CHARACTER_CODEPAGE, the CLI driver returns the CLI0126E error message.

If the empty string value is specified for the SQL_ATTR_OVERRIDE_PRIMARY_AFFINITY attribute, the primary member is reverted to first member listed in the db2dsdriver.cfg file for the client affinities feature.

The new temporary primary member that is specified with the SQL_ATTR_OVERRIDE_PRIMARY_AFFINITY attribute is stored in memory and does not affect the order of members that are listed in the db2dsdriver.cfg file.

• SQL_PARC_BATCH_ENABLE: CLI returns the number of rows in a table that were affected by each parameter set.
• SQL_PARC_BATCH_DISABLE (default): CLI returns the total number of rows that were affected for the entire parameter set.

If you set this connection attribute to SQL_PARCH_BATCH_ENABLE, you must indicate an array as the RowCountPtr parameter in the SQLRowCount () function and you must set SQL_ATTR_PARAMOPT_ATOMIC to SQL_ATOMIC_NO. If SQL_ATTR_PARAMOPT_ATOMIC is set to SQL_ATOMIC_YES, the CLI0150E error message is returned when you call the SQLExecute () function.

If an existing connection is dropped by the database, a value of 0 is reported. If the connection is closed by the application, then an SQLSTATE of 08003 is reported. The SQL_ATTR_PING_DB attribute can be returned by the SQLGetConnectAttr() function, but cannot be set by the SQLSetConnectAttr() function. Any attempt to set this attribute results in an SQLSTATE of 7HYC00 (Driver not capable)

If the application has never made a call to SQLSetConnectAttr() with this option, then CLI would return a null parent window handle on SQLGetConnectAttr() for this option and use a null parent window handle to display dialogue boxes. For example, if the end user has asked for (via an entry in the CLI initialization file) optimizer information to be displayed, CLI would display the dialogue box containing this information using a null window handle. (For some platforms, this means the dialogue box would be centered in the middle of the screen.)

If ValuePtr is set to null , then CLI does not display any dialogue boxes. In cases where end user asked for the optimizer estimates to be displayed, CLI would not display these estimates because the application explicitly wants to suppress all such dialogue boxes.

If ValuePtr is not null, then it should be the parent window handle of the application. CLI uses this handle to display dialogue boxes. (For some platforms, this means the dialogue box would be centered with respect to the active window of the application.)

• SQL_READ_ONLY_CONNECTION_ON: Specifies that a new connection is read-only, which is created after you set the SQL_READ_ONLY_CONNECTION_ON attribute.
• SQL_READ_ONLYCONNECTION_OFF: The default value. Specifies that a new connection is not read-only, which is created after you set the SQL_READ_ONLYCONNECTION_OFF attribute. Access to database objects are controlled by the authority that is associated with a user ID on the server.

The SQL_ATTR_READ_ONLY_CONNECTION attribute cannot be specified when you are connecting to Informix database servers.

A 32-bit integer value that is the number of seconds a client waits for a reply from a server on an established connection before terminating the attempt and generating a communication timeout error. The default value of 0 indicates the client waits indefinitely for a reply. The receive timeout has no effect during connection establishment; it is only supported for TCP/IP, and is ignored for any other protocol. Supported values are integers from 0 to 32767.

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

This attribute is not supported when accessing Informix database servers.

• 0 (default): LONG types (LONG VARCHAR, LONG VARCHAR FOR BIT DATA, LONG VARGRAPHIC and LONG VARGRAPHIC FOR BIT DATA) do not have the DBCOLUMNFLAGS_ISLONG flag set, which might cause the columns to be used in the WHERE clause.
• 1: The IBM DB2 OLE DB Provider reports LONG types (LONG VARCHAR, LONG VARCHAR FOR BIT DATA, LONG VARGRAPHIC and LONG VARGRAPHIC FOR BIT DATA) with the DBCOLUMNFLAGS_ISLONG flag set. This will prevent the long columns from being used in the WHERE clause.

This attribute is not supported when accessing Informix database servers.

• SQL_REPORT_SEAMLESSFAILOVER_WARNING_YES: If a seamless failover occurred during a connect or an execute request, a warning message is returned.
• SQL_REPORT_SEAMLESSFAILOVER_WARNING_NO (default): If a seamless failover occurred during a connect or an execute request, a warning message is not returned.

• 0 (default): Datetime overflow results in an error (SQLSTATE 22008).
• 1: Datetime overflow results in a warning (SQLSTATE 01S07).

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.

• SQL_ATTR_SERVER_MSGTXT_MASK_LOCAL_FIRST (default): CLI will check the local message files first to see if the message can be retrieved. If no matching SQLCODE is found, then CLI will request the information from the server.
• SQL_ATTR_SERVER_MSGTXT_MASK_WARNINGS: CLI always requests the message information from the server for warnings but error messages are retrieved from the local message files.
• SQL_ATTR_SERVER_MSGTXT_MASK_ERRORS: CLI always requests the message information from the server for errors but warning messages are retrieved from the local message files.
• SQL_ATTR_SERVER_MSGTXT_MASK_ALL: CLI always requests the message information from the server for both error and warning messages.

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

• SYSIBM.SQLCAMESSAGE: The default procedure called to retrieve the message text from DB2 for z/OS servers. If you do not set this attribute, this procedure is called.
• DSNACCMG: The default procedure called to retrieve the message text from DB2 for z/OS Version 7 servers. The SYSIBM.SQLCAMESSAGE procedure is called to retrieve the message text from DB2 for z/OS Version 8 or later. DSNACCMG has been deprecated in DB2 for z/OS Version 9 and might be removed in a future release.
• Any user-created stored procedure.

Applications using this attribute can also set the SQL_ATTR_SERVER_MSGTXT_MASK attribute to indicate when CLI should call this procedure to retrieve the message information from the server. If the SQL_ATTR_SERVER_MSGTXT_MASK is not set, then the default is to check the local message files first (see SQL_ATTR_SERVER_MSGTXT_MASK_LOCAL_FIRST in SQL_ATTR_SERVER_MSGTXT_MASK).

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

• 0 (default): The IBM DB2 OLE DB Provider returns a row set sorted by the columns TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME.
• 1: The IBM DB2 OLE DB Provider returns a row set sorted by ORDINAL_POSITION.

This attribute is not supported when accessing Informix database servers.

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

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

• 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, also known as progressive streaming, specify 1 to indicate that data buffering is not required. The CLI client will optimize the data output stream.

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

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

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

• SQL_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.

• SQL_IBMi_FMT_ISO: Specifies the International Standards Organization (ISO) time format of hh.mm.ss.
• SQL_IBMi_FMT_USA: Specifies the United States time format of hh:mm xx, where xx is AM or PM.
• SQL_IBMi_FMT_EUR: Specifies the European time format of hh.mm.ss.
• SQL_IBMi_FMT_JIS: Specifies the Japanese Industrial Standard time format of hh:mm:ss.
• SQL_IBMi_FMT_HMS: Specifies the time format of hh:mm:ss.

The default value for the SQL_ATTR_TIME_FMT attribute is determined by the DATETIME bind option that is specified for packages. If the default DATETIME bind option is specified for the CLI packages, SQL_IBMi_FMT_JIS is the default value for the SQL_ATTR_TIME_FMT attribute.

• SQL_SEP_COLON: Specifies a colon( : ) for the time separator.
• SQL_SEP_PERIOD: Specifies a period ( . ) for the time separator.
• SQL_SEP_COMMA: Specifies a comma ( , ) for the time separator.
• SQL_SEP_BLANK: Specifies a blank for the time separator.
• SQL_SEP_JOB: Specifies the job default for the time separator.

The default value for the SQL_ATTR_TIME_SEP attribute is determined by the DATETIME bind option that is specified for packages. If the default DATETIME bind option is specified for the CLI packages, SQL_SEP_COLON is the default value for the SQL_ATTR_TIME_SEP attribute.

Instead of using this connection attribute, the CLI trace facility can be set using the Trace CLI/ODBC configuration keyword. Alternatively, the environment attribute SQL_ATTR_TRACE can be used to configure tracing features. Note that the environment attribute does not use the same syntax as the ODBC Driver Manager's connection attribute.

Instead of using this attribute, the CLI trace file name is set using the TraceFileName CLI/ODBC configuration keyword.

This attribute is not supported when accessing Informix database servers.

A user defined string containing a user ID. Use this on existing trusted connections to switch users. Do not use it when creating a trusted connection.

After setting this attribute the user switch will occur the next time that you execute an SQL statement that accesses the database server. (SQLSetConnectAttr does not access the database server.) If the user switch is successful the user ID in this attribute becomes the new user of the connection. If the user switch fails the call that initiated the switch will return an error indicating the reason for the failure.

The user ID must be a valid authorization ID on the database server unless you are using an identity server, in which case you can use any user name recognized by the identity server. (If you are using an identity server see also SQL_ATTR_USER_REGISTRY_NAME.)

If you set this attribute while the connection handle is not yet connected to a database or if the connection is not a trusted connection then an error (CLI0197E) is returned.

This attribute is not supported when accessing Informix database servers.

• SQL_TXN_READ_UNCOMMITTED - Dirty reads, non-repeatable reads, and phantom reads are possible.
• SQL_TXN_READ_COMMITTED (default) - Dirty reads are not possible. Non-repeatable reads and phantom reads are possible.
• SQL_TXN_REPEATABLE_READ - Dirty reads and reads that cannot be repeated are not possible. Phantoms are possible.
• SQL_TXN_SERIALIZABLE - Transactions can be serialized. Dirty reads, non-repeatable reads, and phantoms are not possible.
• SQL_TXN_NOCOMMIT - Any changes are effectively committed at the end of a successful operation; no explicit commit or rollback is allowed. This is analogous to autocommit. This is not an SQL92 isolation level, but an IBM defined attribute, supported only by the DB2 for i server.

This option cannot be specified while there is an open cursor on any statement handle, or an outstanding transaction for this connection; otherwise, SQL_ERROR is returned on the function call (SQLSTATE S1011).

This attribute (or corresponding keyword) is only applicable if the default isolation level is used. If the application has specifically set the isolation level then this attribute will have no effect.

This attribute is only used when authenticating a user on a server that is using an identity mapping service. It is set to a user defined string that names an identity mapping registry. The format of the registry name varies depending on the identity mapping service used. By providing this attribute you tell the server that the user name provided can be found in this registry.

After setting this attribute the value will be used on subsequent attempts to establish a normal connection, establish a trusted connection, or switch the user id on a trusted connection.

This attribute is not supported when accessing Informix database servers.

• SQL_WCHARTYPE_CONVERT: character codes are converted between the graphic SQL data in the database and the application variable. This allows your application to fully exploit the ANSI C mechanisms for dealing with wide character strings (for example, L-literals, 'wc' string functions) without having to explicitly convert the data to multi-byte format before communicating with the database. The disadvantage is that the implicit conversions might have an impact on the runtime performance of your application, and might increase memory requirements. If you want WCHARTYPE CONVERT behavior then define the C preprocessor macro SQL_WCHART_CONVERT at compile time. This ensures that certain definitions in the DB2 header files use the data type wchar_t instead of sqldbchar.
• SQL_WCHARTYPE_NOCONVERT (default): no implicit character code conversion occurs between the application and the database. Data in the application variable is sent to and received from the database as unaltered DBCS characters. This allows the application to have improved performance, but the disadvantage is that the application must either refrain from using wide-character data in wchar_t (SQLDBCHAR) application variables, or it must explicitly call the wcstombs() and mbstowcs() ANSI C functions to convert the data to and from multi-byte format when exchanging data with the database.

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

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.

This attribute is not supported when accessing Informix database servers.