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 164. SQLSetEnvAttr Arguments

Data Type	Argument	Use	Description
SQLHENV	EnviornmentHandle	Input	Environment handle.
SQLINTEGER	Attribute	Input	Environment attribute to set, refer to Environment Attributes 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().

Environment Attributes

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 (default).

  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_PROCESSCTRL

A 32-bit mask that sets process level attributes which affect all environments and connections for the process. This attribute must be set before the environment handle is allocated.

The call to SQLSetEntAttr() must have the EnvironmentHandle argument set to SQL_NULL_HANDLE. The settings remain in effect for the life of the process. Generally this attribute is only used for performance sensitive applications, where large numbers of CLI function calls are being made. Before setting any of these bits, ensure that the application, and any other libraries that the application calls, comply with the restrictions listed.

The following values may be combined to form a bitmask:

• SQL_PROCESSCTL_NOTHREAD - This bit indicates that the application does not use multiple threads, or if it does use multiple threads, guarantees that all DB2 calls will be serialized by the application. If set, DB2 CLI does not make any system calls to serialize calls to CLI, and sets the DB2 context type to SQL_CTX_ORIGINAL.
• SQL_PROCESSCTL_NOFORK - This bit indicates that the application will never fork a child process. If set, DB2 CLI does not need to check the current process id for each function call.

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_ATTR_USE_2BYTES_OCTET_LENGTH

A 4-byte integer value that specifies whether the SQL_DESC_OCTET_LENGTH_PTR descriptor field (used as either an APD or ARD) is a pointer to a 2 byte value or a 4 byte value (default). The possible values are:

• SQL_TRUE: SQL_DESC_OCTET_LENGTH_PTR is a pointer to a 2 byte value
• SQL_FALSE: SQL_DESC_OCTET_LENGTH_PTR is a pointer to a 4 byte value (default)

For more information on the descriptor field please see SQLSetDescField - Set a Single Field of a Descriptor Record.

This attribute can be set at any time and will take effect the next time a descriptor function is called. It does not affect the StrLen_or_IndPtr value for SQLGetData() calls.

Note:

This is an IBM defined extension.

SQL_ATTR_USE_LIGHT_OUTPUT_SQLDA

A 32-bit integer value that specifies whether the column name is sent over the network and returned on calls to SQLDescribeCol(), SQLColAttribute(), and SQLGetDescField(). The possible values are:

• SQL_TRUE: Column name is not included in the network flow. Only the column number is returned.
• SQL_FALSE: Column name is included in the network flow (default).

Note:

This is an IBM defined extension.

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

Restrictions

None.

CLI Sample pcall.c

(The complete sample pcall.c is also available here .)

 
/* From the CLI sample PCALL.C */
/* ... */
 
    /* enable sending column names over the network */
    sqlrc = SQLSetEnvAttr( henv,
                        SQL_ATTR_USE_LIGHT_OUTPUT_SQLDA,
                        ( SQLPOINTER ) SQL_FALSE,
                        SQL_FALSE
                      ) ;
    
 

References

• SQLGetEnvAttr - Retrieve Current Environment Attribute Value