Environment attributes (CLI) list

This attribute is not supported when accessing the Informix database server.

• 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 the coordination of the transaction. If an application issues a commit by using the environment handle on SQLEndTran() and not all of the connections commit successfully, the application is responsible for recovery. This is the default.
• SQL_COORDINATED_TRANS: The application can coordinate commit and rollbacks 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 allowed only one open connection per database. There is no impact on the full XA behavior when SQL_ATTR_CONNECTTYPE is set to SQL_COORDINATED_TRANS. In other words, the full XA behavior will work without any issues.

  Earlier, XA transaction was supported only on a single transport mode. With the full XA support, XA transaction is supported on dual and multi-transport mode. For example, previously, xa_start followed by xa_end followed by xa_prepare and xa_commit/xa_rollback had to all be done on one transport. Now, xa_start followed by xa_end can be on one transport while xa_prepare and xa_commit/xa_rollback can go on another transport.

  Note: This connection type results in the default for the SQL_ATTR_AUTOCOMMIT connection option to be SQL_AUTOCOMMIT_OFF.

If you change this attribute from the default, you must set it before any connections are established on the environment handle.

Applications typically set this attribute as an environment attribute with a call to the SQLSetEnvAttr() function. The SQLSetEnvAttr() function is called as soon as the environment handle is allocated. However, because ODBC applications cannot access SQLSetEnvAttr() function, ODBC applications must set this attribute by using the SQLSetConnectAttr() function after each connection handle is allocated, but before any connections are established.

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

You can also set the default connection type by using the ConnectType CLI/ODBC and IBM data server driverconfiguration keyword.

The SQL_ATTR_CONNECTTYPE attribute is an IBM® defined extension.

This attribute is not supported when accessing the Informix® database server.

• 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

Description

A 32-bit integer value that allocates the Db2 trace buffer in MB.

Values

The SQL_ATTR_DB2TRC_STARTUP_SIZE attribute value must be in the range of 1-1024 MB and in power of 2. If the SQL_ATTR_DB2TRC_STARTUP_SIZE attribute value is not set to a value in power of 2, the specified SQL_ATTR_DB2TRC_STARTUP_SIZE value is rounded down to the closest power of 2 value.

Usage notes

The SQL_ATTR_DB2TRC_STARTUP_SIZE attribute value takes effect only if the following conditions are met:

• No environment handle is allocated by the process that uses Db2 libraries.
• A trace buffer is not already allocated before you set the SQL_ATTR_DB2TRC_STARTUP_SIZE attribute value.

You cannot change the trace facility buffer that is allocated by the SQL_ATTR_DB2TRC_STARTUP_SIZE attribute value while the Db2 libraries are still loaded in the operating system.

The trace facility buffer is deallocated when all running processes that use Db2 libraries exits.

If the trace facility buffer is already allocated, the SQL_ATTR_DB2TRC_STARTUP_SIZE attribute value cannot be greater than the buffer size that is already allocated. You can allocate the trace facility buffer by using any of the following methods:

• The db2start command.
• The db2trc on or db2trc alloc command.
• The db2trcStartupSize keyword in the db2dsdriver.cfg file.
• The SQL_ATTR_DB2TRC_STARTUP_SIZE environment attribute.

If the allocated trace buffer size is smaller than the value specified for the SQL_ATTR_DB2TRC_STARTUP_SIZE attribute, the SQLSetEnvAttr() function returns an SQL_SUCCESS_WITH_INFO message. You cannot retrieve any warning message details if the SQLSetEnvAttr() function is called with SQL_NULL_HANDLE. You can obtain the value of allocated trace buffer by calling the SQLGetEnvAttr() function with the SQL_ATTR_DB2TRC_STARTUP environment attribute.

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

Description

A 32-bit integer value which represents the diagnostic level. This is equivalent to the database manager DIAGLEVEL parameter.

Values

Valid values are: 0, 1, 2, 3, or 4. (The default value is 3.)

For details about these values, see diaglevel - Diagnostic error capture level configuration parameter.

Usage notes

You must set this attribute before any connection handles are created.

Description

A pointer to a null-terminated character string that contains the name of the directory where diagnostic data is to be placed. The SQL_ATTR_DIAGPATH is equivalent to the database manager DIAGPATH parameter.

Values

The default value is the db2dump directory on UNIX and Linux® operating systems, and the db2 directory on Windows operating systems.

Usage notes

You must set this attribute before any connection handles are created.

Description

A pointer to a null-terminated character string that is used to identify the client accounting string that is sent to a database.

Values

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

Database servers enforce different limitation in the length of the value and can truncate it. Note the following conditions:

• Db2 for z/OS Version 11 servers in new function mode (NFM) support a length of up to 255 characters for the CURRENT CLIENT_ACCTNG special register.
• Db2 for z/OS servers remove trailing spaces that are specified in the SQL_ATTR_INFO_ACCTSTR attribute value.
• Db2 for z/OS Version 10 and earlier servers support a length of up to 200 characters.
• CLI applications can set the SQL_ATTR_INFO_ACCTSTR attribute on Db2 for IBM i V6R1 and later servers. Db2 for IBM i servers support a length of up to 255 characters.

To ensure that the data is converted correctly when transmitted to Db2 for z/OS Version 9 and earlier servers, use only the characters A-Z and 0-9 and the underscore (_) or period (.).

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.

Description

A pointer to a null-terminated character string that is used to identify the client application name that is sent to a database.

Values

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

Database servers enforce different limitations in the length of the value and can truncate it. Note the following conditions:

• Db2 for z/OS Version 11 servers in new function mode (NFM) support a length of up to 255 characters for the CURRENT CLIENT_APPLNAME special register.
• Db2 for z/OS servers remove trailing spaces that are specified in the SQL_ATTR_INFO_APPLNAME attribute value.
• Db2 for z/OS Version 10 and earlier servers support a length of up to 32 characters.
• CLI applications can set the SQL_ATTR_INFO_APPLNAME attribute on Db2 for IBM i V6R1 and later servers. Db2 for IBM i servers support a length of up to 255 characters.

To ensure that the data is converted correctly when transmitted to Db2 for z/OS Version 9 and earlier server, use only the characters A-Z and 0-9 and the underscore (_) or period (.).

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

Description

A pointer to a null-terminated character string that is used to identify the client correlation token that is sent to Db2 for z/OS Version 11 and later servers.

Values

• You can specify the SQL_ATTR_INFO_CRRTKN attribute when you are connecting to Db2 for z/OS Version 11 server in new function mode (NFM).
• Db2 for z/OS servers set the CURRENT CLIENT_CORR_TOKEN special register with the SQL_ATTR_INFO_CRRTKN attribute value.
• Db2 for z/OS servers remove trailing spaces that are specified in the SQL_ATTR_INFO_CRRTKN attribute value.
• 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.
• There is no monitoring support for the client correlation token value in the Db2 connect gateway server.
• The SQL_ATTR_INFO_CRRTKN attribute value is sent to the server without any client side validation.
• The SQL_ATTR_INFO_CRRTKN attribute has limit of 255 characters.
• The character string that is provided for the SQL_ATTR_INFO_CRRTKN attribute must be null terminated.
• The SQL_ATTR_INFO_CRRTKN 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_CRRTKN attribute is an IBM defined attribute.

Description

A pointer to a null-terminated character string that is used to identify the client user ID that is sent to a database.

Values

Do not confuse the client user ID with the authentication user ID. The client user ID is for identification purposes only and is not used for any authentication.

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

Database servers enforce different limitations in the length of the value and can truncate it. Note the following conditions:

• Db2 for z/OS Version 11 servers in new function mode (NFM) support a length of up to 128 characters for the CURRENT CLIENT_USERID special register.
• Db2 for z/OS servers remove trailing spaces that are specified in the SQL_ATTR_INFO_USERID attribute value.
• Db2 for z/OS Version 10 and earlier servers support a length of up to 16 characters.
• CLI applications can set the SQL_ATTR_INFO_USERID attribute on Db2 for IBM i V6R1 and later servers. Db2 for IBM i servers support a length of up to 255 characters.

To ensure that the data is converted correctly when transmitted to Db2 for z/OS Version 9 and earlier servers, use only the characters A-Z and 0-9 and the underscore (_) or period (.).

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.

Description

A pointer to a null-terminated character string that is used to identify the client workstation name that is sent to a database.

Values

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

Database servers enforce different limitations in the length of the value and can truncate it. Note the following conditions:

• Db2 for z/OS Version 11 servers in new function mode (NFM) support a length of up to 255 characters for the CURRENT CLIENT_WRKSTNNAME special register.
• Db2 for z/OS servers remove trailing spaces that are specified in the SQL_ATTR_INFO_WRKSTNNAME attribute value.
• Db2 for z/OS Version 10 and earlier servers support a length of up to 16 characters.
• Db2 for z/OS Version 10 and earlier servers support a length of up to 18 characters.
• CLI applications can set the SQL_ATTR_INFO_WRKSTNNAME attribute on Db2 for IBM i V6R1 and later servers. Db2 for IBM i servers support a length of up to 255 characters.

To ensure that the data is converted correctly when transmitted to Db2 for z/OS Version 9 and earlier servers, use only the characters A-Z and 0-9 and the underscore (_) or period (.).

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.

This attribute is not supported when accessing the Informix database servers.

Description

A 32-bit integer value that represents the notification level. This is equivalent to the database manager NOTIFYLEVEL parameter.

Values

Valid values are: 0, 1, 2, 3, or 4. (The default value is 3.)

For details about these values, see notifylevel - Notify level configuration parameter.

Usage notes

You must set this attribute value before any connection handles are created.

This attribute is not supported when accessing the Informix database servers.

Description

A 32-bit integer that determines whether certain functionality exhibits ODBC 2.x (CLI v2) behavior or ODBC 3.0 (CLI v5) behavior. 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.).

Values

To set the value of this attribute, use one of the following values:

• SQL_OV_ODBC3: Causes the listed ODBC 3.0 (CLI v5) behavior:
  • CLI returns and expects ODBC 3.0 (CLI v5) codes for date, time, and timestamp.
  • CLI returns ODBC 3.0 (CLI v5) SQLSTATE codes when SQLError(), SQLGetDiagField(), or SQLGetDiagRec() functions are called.
  • The CatalogName argument in a call to SQLTables() function accepts a search pattern.
• SQL_OV_ODBC2: Causes the listed ODBC 2.x (CLI v2) behavior:
  • CLI returns and expects ODBC 2.x (CLI v2) codes for date, time, and timestamp.
  • CLI returns ODBC 2.0 (CLI v2) SQLSTATE codes when SQLError(), SQLGetDiagField(), or SQLGetDiagRec() functions are called.
  • The CatalogName argument in a call to SQLTables() function does not accept a search pattern.
• SQL_OV_ODBC3_80: Causes the listed ODBC 3.0 (CLI v5) behavior:
  • CLI returns and expects ODBC 3.x codes for date, time, and timestamp.
  • CLI returns ODBC 3.x SQLSTATE codes when SQLError(), SQLGetDiagField(), or SQLGetDiagRec() functions are called.
  • The CatalogName argument in a call to SQLTables() function accepts a search pattern.

Description

A 32-bit integer value that controls the use of null-termination in output arguments.

Values

The possible values are:

• SQL_TRUE: CLI uses null termination to indicate the length of output character strings (default).
• SQL_FALSE: CLI does not use null termination in output character strings.

The CLI functions that are affected by this attribute are all of the functions that are called for the environment (and for any connections and statements that are allocated under the environment) that have character string parameters.

You can set this attribute only when there are no connection handles that are allocated under this environment.

Description

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

The call to SQLSetEnvAttr() must have the EnvironmentHandle argument set to SQL_NULL_HANDLE. The settings remain in effect for the duration of the process. Generally, use this attribute only 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 that are listed.

Values

You can combine the listed values to form a bit mask:

• 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 are serialized by the application. If set, 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. By default, CLI does not check to see if an application forks a child process. However, if the CheckForFork CLI/ODBC configuration keyword is set, CLI checks the current process ID for each function call for all applications that are connecting to the database for which the keyword is enabled. You can set this attribute so that CLI does not check for forked processes for that application.

The SQL_ATTR_PROCESSCTL attribute is an IBM defined extension.

Description

A 32-bit unsigned integer value that specifies whether the ODBC Driver Manager notifies the ODBC drivers that a connection has been placed in the connection pool on Windows operating systems. If the SQL_ATTR_ODBC_VERSION environment attribute is set to SQL_OV_ODBC3_80, the ODBC Driver Manager sets this attribute before placing a connection in the connection pool so that the driver can reset the other connection attributes to their default values.

Values

The only possible value is:

• SQL_RESET_CONNECTION_YES (default): The ODBC Driver Manager notifies the ODBC drivers that a connection has been placed in the connection pool.

This attribute is not supported when accessing the Informix database servers.

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

Description

A pointer to a null-terminated character string that is used to turn on the CLI/ODBC trace facility.

Values

The string must include the CLI keywords TRACE and TRACEPATHNAME. For example:

"TRACE=1; TRACEPATHNAME=<dir>;"

Usage notes

This attribute is not supported when accessing the Informix database servers.

Description

A 32-bit integer value that specifies whether header information is included in the CLI trace file.

Values

The possible values are:

• 0 - Header information is included in the CLI trace file.
• 1 - No header information is included in the CLI trace file.

You can use the SQL_ATTR_TRACENOHEADER attribute with an SQL_NULL_HANDLE or with a valid environment handle.

This attribute is not supported when accessing the Informix database servers.

Description

This attribute is used only when authenticating a user on a server that is using an identity mapping service.

Values

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

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

Usage notes

On z/OS servers, you can also set the SQL_ATTR_USER_REGISTRY_NAME attribute after connection and after statement allocation. This allows you to specify the registry name and User ID to switch to a different user while in trusted connection.

This attribute is not supported when accessing the Informix database servers.

This attribute is not supported when accessing the Informix database servers.