Call Level Interface Guide and Reference

SQLSetEnvAttr - Set Environment Attribute

Purpose

Specification: DB2 CLI 2.1 ISO CLI

SQLSetEnvAttr() sets an environment attribute for the current environment.

Syntax

SQLRETURN   SQLSetEnvAttr    (SQLHENV           EnvironmentHandle, /* henv */
                              SQLINTEGER        Attribute,
                              SQLPOINTER        ValuePtr,          /* Value */
                              SQLINTEGER        StringLength);

Function Arguments

Table 162. SQLSetEnvAttr Arguments

Data Type Argument Use Description
SQLHENV EnviornmentHandle Input Environment handle.
SQLINTEGER Attribute Input Environment attribute to set, refer to Table 163 for the list of attributes and their descriptions.
SQLPOINTER ValuePtr Input The desired value for Attribute.
SQLINTEGER StringLength Input Length of ValuePtr in bytes if the attribute value is a character string; if Attribute does not denote a string, then DB2 CLI ignores StringLength.

Data Type	Argument	Use	Description
SQLHENV	EnviornmentHandle	Input	Environment handle.
SQLINTEGER	Attribute	Input	Environment attribute to set, refer to Table 163 for the list of attributes and their descriptions.
SQLPOINTER	ValuePtr	Input	The desired value for `Attribute`.
SQLINTEGER	StringLength	Input	Length of `ValuePtr` in bytes if the attribute value is a character string; if `Attribute` does not denote a string, then DB2 CLI ignores `StringLength`.

Usage

Once set, the attribute's value affects all connections under this environment.

The application can obtain the current attribute value by calling SQLGetEnvAttr().

Table 163. Environment Attributes

Attribute Contents
SQL_ATTR_CONNECTION_POOLING
32-bit integer value that enables or disables connection pooling at the environment level. The following values are used:

SQL_CP_OFF = Connection pooling is turned off. This is the default.
SQL_CP_ONE_PER_DRIVER = A single, global connection pool is supported for each DB2 CLI application. Every connection in a pool is associated with the application.
SQL_CP_ONE_PER_HENV = A single connection pool is supported for each environment. Every connection in a pool is associated with one environment.

Connection pooling is enabled by calling SQLSetEnvAttr() to set the SQL_ATTR_CONNECTION_POOLING attribute to SQL_CP_ONE_PER_DRIVER or SQL_CP_ONE_PER_HENV. This call must be made before the application allocates the shared environment for which connection pooling is to be enabled. The environment handle in the call to SQLSetEnvAttr() is set to null, which makes SQL_ATTR_CONNECTION_POOLING a process-level attribute. After connection pooling is enabled, the application then allocates an implicit shared environment by calling SQLAllocHandle() with the InputHandle argument set to SQL_HANDLE_ENV.
After connection pooling has been enabled and a shared environment has been selected for an application, SQL_ATTR_CONNECTION_POOLING cannot be reset for that environment, since SQLSetEnvAttr() is called with a null environment handle when setting this attribute. If this attribute is set while connection pooling is already enabled on a shared environment, the attribute only affects shared environments that are allocated subsequently.
SQL_ATTR_CONNECTTYPE A 32-bit integer value that specifies whether this application is to operate in a coordinated or uncoordinated distributed environment. If the processing needs to be coordinated, then this option must be considered in conjunction with the SQL_ATTR_SYNC_POINT connection option. The possible values are:

SQL_CONCURRENT_TRANS: 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 transaction. If an application issues a commit using the environment handle on SQLTransact() and not all of the connections commit successfully, the application is responsible for recovery.
The current setting of the SQL_ATTR_SYNC_POINT attribute is ignored.
This is the default.
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 and must be considered in conjunction with the SQL_ATTR_SYNC_POINT connection option. In contrast to the SQL_CONCURRENT_TRANS setting described above, the application is permitted only one open connection per database.

This attribute must be set before allocating any connection handles, otherwise, the SQLSetEnvAttr() call will be rejected.
All the connections within an application must have the same SQL_ATTR_CONNECTTYPE and SQL_ATTR_SYNC_POINT values. This attribute can also be set using the SQLSetConnectAttr function. We recommend that the application set the SQL_ATTR_CONNECTTYPE attribute at the environment level rather than on a per connection basis. ODBC applications written to take advantage of coordinated DB2 transactions must set these attributes at the connection level for each connection using SQLSetConnectAttr() as SQLSetEnvAttr() is not supported in ODBC.

Note: This is an IBM defined extension.

SQL_ATTR_CP_MATCH A 32-bit value that determines how a connection is chosen from a connection pool. When SQLConnect() or SQLDriverConnect()is called, DB2 CLI (or the Driver Manager when used) determines which connection is reused from the pool. DB2 CLI (or the Driver Manager) attempts to match the connection options in the call and the connection attributes set by the application to the keywords and connection attributes of the connections in the pool. The value of this attribute determines the level of precision of the matching criteria.
The following values are used to set the value of this attribute:

SQL_CP_STRICT_MATCH = Only connections that exactly match the connection options in the call and the connection attributes set by the application are reused. This is the default.
SQL_CP_RELAXED_MATCH = Connections with matching connection string keywords can be used. Keywords must match, but not all connection attributes must match.

SQL_ATTR_MAXCONN A 32-bit integer value corresponding to the number that maximum concurrent connections that an application may desire to set up. The default value is 0, which means no maximum - the application is allowed to set up as many connections as the system resources permit. The integer value must be 0 or a positive number.
This can be used as a governor for the maximum number of connections on a per application basis.
On OS/2, if the NetBIOS protocol is in use, this value corresponds to the number of connections (NetBIOS sessions) that will be concurrently set up by the application. The range of values for OS/2 NetBIOS is 1 to 254. Specifying 0 (the default) will result in 5 reserved connections. Reserved NetBIOS sessions cannot be used by other applications. The number of connections specified by this parameter will be applied to any adaptor that the DB2 NetBIOS protocol uses to connect to the remote server (adapter number is specified in the node directory for a NetBIOS node).
The value that is in effect when the first connection is established is the value that will be used. Once the first connection has been established, attempts to change this value will be rejected. We recommended that the application set SQL_ATTR_MAXCONN at the environment level rather then on a connection basis. ODBC applications must set this attribute at the connection level since SQLSetEnvAttr() is not supported in ODBC.

Note: This is an IBM defined extension.

SQL_ATTR_ODBC_VERSION A 32-bit integer that determines whether certain functionality exhibits ODBC 2.x (DB2 CLI v2) behavior or ODBC 3.0 (DB2 CLI v5) behavior.
It is recommended that all DB2 CLI applications set this environment attribute. ODBC applications must set this environment attribute before calling any function that has an SQLHENV argument, or the call will return SQLSTATE HY010 (Function sequence error.).
The following values are used to set the value of this attribute:

SQL_OV_ODBC3: Causes the following ODBC 3.0 (DB2 CLI v5) behavior:

DB2 CLI returns and expects ODBC 3.0 (DB2 CLI v5) codes for date, time, and timestamp.
DB2 CLI returns ODBC 3.0(DB2 CLI v5) SQLSTATE codes when SQLError(), SQLGetDiagField(), or SQLGetDiagRec() are called.
The CatalogName argument in a call to SQLTables() accepts a search pattern.

SQL_OV_ODBC2 Causes the following ODBC 2.x (DB2 CLI v2) behavior:

DB2 CLI returns and expects ODBC 2.x (DB2 CLI v2) codes for date, time, and timestamp.
DB2 CLI returns ODBC 2.0 DB2 CLI v2) SQLSTATE codes when SQLError(), SQLGetDiagField(), or SQLGetDiagRec() are called.
The CatalogName argument in a call to SQLTables() does not accept a search pattern.

SQL_ATTR_OUTPUT_NTS A 32-bit integer value which controls the use of null-termination in output arguments. The possible values are:

SQL_TRUE: DB2 CLI uses null termination to indicate the length of output character strings.
This is the default.
SQL_FALSE: DB2 CLI does not use null termination in output character strings.

The CLI functions affected by this attribute are all functions called for the environment (and for any connections and statements allocated under the environment) that have character string parameters.
This attribute can only be set when there are no connection handles allocated under this environment.
SQL_ATTR_SYNC_POINT A 32-bit integer value that allows the application to choose between one-phase coordinated transactions and two-phase coordinated transactions. The possible values are:

SQL_ONEPHASE: One-phase commit is used to commit the work done by each database in a multiple database transaction. To ensure data integrity, each transaction must not have more than one database updated. The first database that has updates performed in a transaction becomes the only updater in that transaction, all other databases accessed are treated as read-only. Any update attempts to these read-only database within this transaction are rejected.
SQL_TWOPHASE: Two-phase commit is used to commit the work done by each database in a multiple database transaction. This requires the use of a Transaction Manager to coordinate two phase commits amongst the databases that support this protocol. Multiple readers and multiple updaters are allowed within a transaction.

Refer to the SQL Reference for more information on distributed unit of work (transactions).
All the connections within an application must have the same SQL_ATTR_CONNECTTYPE and SQL_ATTR_SYNC_POINT values. This attribute can also be set using the SQLSetConnectAttr() function. We recommend that the application set these two attributes at the environment level rather than on a per connection basis. ODBC applications written to take advantage of coordinated DB2 transactions must set these attributes at the connection level for each connection using SQLSetConnectAttr() as SQLSetEnvAttr() is not supported in ODBC.

Note: This is an IBM extension. In embedded SQL, there is an additional sync point setting called SYNCPOINT NONE. This is more restrictive than the SQL_CONCURRENT_TRANS setting of the SQL_ATTR_CONNECTTYPE attribute because SYNCPOINT NONE does not allow for multiple connections to the same database. As a result, it is not necessary for DB2 CLI to support SYNCPOINT NONE.

SQL_CONNECTTYPE This Attribute has been replaced with SQL_ATTR_CONNECTTYPE.
SQL_MAXCONN This Attribute has been replaced with SQL_ATTR_MAXCONN.
SQL_SYNC_POINT This Attribute has been replaced with SQL_ATTR_SYNC_POINT.

Return Codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 164. SQLSetEnvAttr SQLSTATEs

SQLSTATE Description Explanation
HY009 Invalid argument value. Given the fOption value, an invalid value was specified for the argument vParam.
HY011 Operation invalid at this time. Applications cannot set environment attributes while connection handles are allocated on the environment handle.
HY024 Invalid attribute value Given the specified Attribute value, an invalid value was specified in *ValuePtr.
HY090 Invalid string or buffer length The StringLength argument was less than 0, but was not SQL_NTS.
HY092 Option type out of range. An invalid Attribute value was specified.
HYC00 Driver not capable. The specified Attribute is not supported by DB2 CLI.
Given specified Attribute value, the value specified for the argument ValuePtr is not supported.

SQLSTATE	Description	Explanation
HY009	Invalid argument value.	Given the `fOption` value, an invalid value was specified for the argument `vParam`.
HY011	Operation invalid at this time.	Applications cannot set environment attributes while connection handles are allocated on the environment handle.
HY024	Invalid attribute value	Given the specified `Attribute` value, an invalid value was specified in *`ValuePtr`.
HY090	Invalid string or buffer length	The `StringLength` argument was less than 0, but was not SQL_NTS.
HY092	Option type out of range.	An invalid `Attribute` value was specified.
HYC00	Driver not capable.	The specified `Attribute` is not supported by DB2 CLI. Given specified `Attribute` value, the value specified for the argument `ValuePtr` is not supported.

Restrictions

None.

Example

/* From CLI sample seteattr.c */
/* ... */
int main() {
 
    SQLHANDLE henv ;
    SQLRETURN rc ;
 
    SQLINTEGER output_nts = SQL_TRUE ;
 
/* ... */
 
    /* allocate an environment handle */
    rc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv ) ;
    if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;
 
    printf( "Setting Environment option SQL_ATTR_OUTOUT_NTS\n" ) ;
 
    rc = SQLSetEnvAttr( henv,
                        SQL_ATTR_OUTPUT_NTS,
                        ( SQLPOINTER ) output_nts,
                        SQL_FALSE
                      ) ;
    CHECK_HANDLE( SQL_HANDLE_ENV, henv, rc ) ;
 
    rc = SQLFreeHandle( SQL_HANDLE_ENV,  henv ) ;
    if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;
 
    return( SQL_SUCCESS ) ;
 
}                                  /* end main */

References

SQLGetEnvAttr - Retrieve Current Environment Attribute Value

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]