Connection attributes (CLI) list

• SQL_MODE_READ_ONLY: the application is indicating that it will not be performing any updates on data from this point on. Therefore, a less restrictive isolation level and locking can be used on transactions: uncommitted read (SQL_TXN_READ_UNCOMMITTED). CLI does not ensure that requests to the database are read-only. If an update request is issued, CLI will process it using the transaction isolation level it has selected as a result of the SQL_MODE_READ_ONLY setting.
• SQL_MODE_READ_WRITE (default): the application is indicating that it will be making updates on data from this point on. CLI will go back to using the default transaction isolation level for this connection.

There must not be any outstanding transactions on this connection.

• 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_AA_TRUE (default): the application is an ANSI application. All character data is passed to and from the application in the native application (client) codepage using the ANSI version of the CLI/ODBC functions.
• SQL_AA_FALSE: the application is a Unicode application. All character data is passed to and from the application in Unicode when the Unicode (W) versions of the CLI/ODBC functions are called.

• 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 using SQLBindCol(), an Invalid conversion error will be returned by the SQLFetch() function.

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

This attribute allows an application to control at a connection level when CLI appends the "FOR FETCH ONLY" clause. For example, an application is binding the CLI packages using different bind BLOCKING options (for example, BLOCKING UNAMBIG) and wants to suppress the blocking in order to keep positioned on a given row.

The attribute should be set either after the connection is allocated or immediately after it is established and should be set once for the duration of the execution of the application. Application can query the attribute with SQLGetConnectAttr() after connection is established or after this attribute is set.

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

• SQL_ASYNC_ENABLE_OFF (default) = Off
• SQL_ASYNC_ENABLE_ON = On

This attribute can be set whether SQLGetInfo(), called with the InfoType SQL_ASYNC_MODE, returns SQL_AM_CONNECTION or SQL_AM_STATEMENT.

Once a function has been called asynchronously, only the original function, SQLAllocHandle(), SQLCancel(), SQLGetDiagField(), or SQLGetDiagRec() can be called on the statement or the connection associated with StatementHandle, until the original function returns a code other than SQL_STILL_EXECUTING. Any other function called on StatementHandle or the connection associated with StatementHandle returns SQL_ERROR with an SQLSTATE of HY010 (Function sequence error).

• 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: the application must manually, explicitly commit or rollback transactions with SQLEndTran() calls.
• SQL_AUTOCOMMIT_ON (default): CLI operates in auto-commit mode by default. Each statement is implicitly committed. Each statement that is not a query is committed immediately after it has been executed or rolled back if failure occurred. Each query is committed immediately after the associated cursor is closed.
  Note: If this is a coordinated distributed unit of work connection, then the default is SQL_AUTOCOMMIT_OFF

Since in many DB2 environments, the execution of the SQL statements and the commit might be flowed separately to the database server, autocommit can be expensive. It is recommended that the application developer take this into consideration when selecting the auto-commit mode.

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

SQLINTEGER iUnicode = 1208;
cliRC = SQLSetConnectAttr(hdbc,
      SQL_ATTR_CLIENT_CODEPAGE, 
      (SQLPOINTER)iUnicode, 
SQL_IS_INTEGER);

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

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

• SSQL_COMMITONEOF_OFF (default): A COMMIT is not implicitly issued after reading the entire result set. You have to explicitly call the SQLFreeStmt() function to close the cursor and release resources.
• SQL_COMMITONEOF_ON: An implicit COMMIT is issued after reading the entire result set.

• 0 = No setting. The client does not supply a prepare option.
• 1 = Use currently committed semantics. CLI 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 can be skipped. This setting applies when the isolation level in effect 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 as well as a non read-only statement. The settings for the registry variables DB2_EVALUNCOMMITTED, DB2_SKIPDELETED, and DB2_SKIPINSERTED do not apply to scans using currently committed. However, the settings for these registry variables still apply to scans that do not use currently committed.
• 2 = Wait for outcome. CLI flows "wait for outcome" on every prepare, which means that Cursor Stability and higher scans wait for the commit or rollback when encountering data in the process of being updated or deleted. Rows in the process of 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. CLI flows "skip locked data" on every prepare, which means that currently committed semantics are used and rows in the process of being inserted are skipped. This option is not supported on DB2 for Linux, UNIX, and Windows. If specified, this setting is ignored.

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

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 Informix database server.

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

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 described above, 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 server.

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

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

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.

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

This attribute is not supported when accessing Informix database server.

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

This attribute is not supported when accessing Informix database server.

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

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 IDS data servers.

• 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_DESC_COUNT
SQL_COLUMN_COUNT
SQL_DESC_TYPE
SQL_DESC_CONCISE_TYPE
SQL_COLUMN_LENGTH
SQL_DESC_OCTET_LENGTH
SQL_DESC_LENGTH
SQL_DESC_PRECISION
SQL_COLUMN_PRECISION
SQL_DESC_SCALE
SQL_COLUMN_SCALE
SQL_DESC_DISPLAY_SIZE
SQL_DESC_NULLABLE
SQL_COLUMN_NULLABLE
SQL_DESC_UNSIGNED
SQL_DESC_SEARCHABLE
SQL_DESC_LITERAL_SUFFIX
SQL_DESC_LITERAL_PREFIX
SQL_DESC_CASE_SENSITIVE
SQL_DESC_FIXED_PREC_SCALE

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

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

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

• 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 DB2 for Linux, UNIX, and Windows and for DB2 10 for z/OS data servers starts in DB2 Version 9.7 Fix Pack 2. Extended indicators support DB2 for IBM i 7.1 data servers starts in DB2 Version 9.7 Fix Pack 5.

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 data servers that are not DB2 for z/OS and OS/390 servers.

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 used to establish the logical connection. The DSN information 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.

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

Also, in DB2 Version 9.7 Fix Pack 6 and later fix packs, CLI applications can set the SQL_ATTR_INFO_PROGRAMID attribute on DB2 for i V6R1 and later servers. DB2 for i servers support a length of up to 255 characters.

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.

• 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

When you set the SQL_ATTR_KEEP_DYNAMIC attribute, you should also set the SQL_ATTR_CURRENT_PACKAGE_SET attribute.

This attribute is not supported when accessing Informix database server.

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 their respective 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 will include 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 will be used.

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

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.

In Version 9.7 Fix Pack 5 and later fix packs, CLI collects statistics for server time reported on COMMIT and ROLLBACK. The DB2 server must be at a level that supports reporting server time for COMMIT and ROLLBACK.

In Version 9.7 Fix pack 6 and later fix packs, you can obtain the server time for COMMIT or ROLLBACK SQL operations on DB2 for z/OS Version 10 and later by calling the SQLGetDiagField() function and specifying SQL_DIAG_NETWORK_STATISTICS for the DiagIdentifier argument after the SQLEndTran() function call. You must enable the SQL_ATTR_NETWORK_STATISTICS attribute, and DB2 for z/OS Version 10 and later servers must have APAR PM53243 applied to obtain the server time for COMMIT or ROLLBACK SQL operations.

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

If you set this 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, CLI returns the CLI0126E error message.

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 this attribute after allocating statement handles results in error CLI0126E (invalid operation). Setting the attribute to a value that is not supported by the database results in error CLI0210E (inconsistent code page value error).

If you set SQL_ATTR_OVERRIDE_CHARACTER_CODEPAGE after setting SQL_ATTR_OVERRIDE_CODEPAGE to SQL_OVERRIDE_CODEPAGE_ON, CLI 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, error CLI0002W is returned.

• SQL_PARC_BATCH_ENABLE: CLI returns the number of rows in a table that were affected by the 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 a connection has previously been established and has been dropped by the database, a value of 0 is reported. If the connection has been closed by the application, then an SQLSTATE of 08003 is reported. 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 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 the above example where the end user has 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.)

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

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

• SQL_REPORT_SEAMLESSFAILOVER_WARNING_YES: If a seamless failover occurred during an execute request, a warning message is returned.
• SQL_REPORT_SEAMLESSFAILOVER_WARNING_NO (default): If a seamless failover occurred during 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).

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

• MAP (default): SQLCODE mapping is set.
• NOMAP: SQLCODEMapping is turned off.

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

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

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

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.

When SQL_ATTR_TRUSTED_CONTEXT_USERID attribute is set, the user switch will occur 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 server.

• 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 extension, supported only by DB2 UDB for AS/400®.

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 specifically set the isolation level then this attribute has 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.

The SQL_ATTR_USER_REGISTRY_NAME attribute is 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 server.

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