The following describes the difference between version 2.1.0 and 1.x.
Previous versions of DB2 CLI did not support autocommit (each statement is a transaction), and was thus equivalent to having AUTOCOMMIT set to off. DB2 CLI Version 2.1 now supports autocommit, but in order to be consistent with ODBC, the default autocommit behavior is on.
To enable existing DB2 CLI applications to run with the same behavior as previous versions, set the AUTOCOMMIT keyword to 0. This keyword applies to all applications; for this reason new applications should explicitly override the keyword and set the autocommit connection option to the required value.
Support for specifying CURSOR WITH HOLD was also added in Version 2.1, with a default of with hold on. Since this was not supported in previous versions, with hold was effectively set off.
Although this change does not affect the behavior of applications as much as autocommit, the CURSORHOLD keyword should be set to 0 (cursors are not maintained after a commit). This keyword applies to all applications, for this reason new applications should explicitly override the keyword and set the CURSORHOLD statement option to the required value.
The #define values
have been changed in Version 2.1 in order that they can be used with ODBC applications. DB2 CLI will still accept the old values, but it is recommended that existing applications that use these valued be recompiled.
These valued are defined in the sqlcli.h header file.
In previous versions, DB2 CLI returned the S1009 SQLSTATE instead of the more explicit S1090 to S1110 series of SQLSTATES defined by ODBC.
As a result of X/Open also using this range of SQLSTATES, DB2 CLI Version 2.1 will also return the more explicit SQLSTATES.
DB2 CLI's Version 2.1 support of multiple connections may affect existing applications that mix the use of embedded SQL and DB2 CLI.
If your application:
the second connect will fail since it is not same type of connection as the first connect.
The application must issue a CONNECT RESET before calling a DB2 CLI connect function.
| Note: | An application should always explicitly reset a connection. |
Character data defined with the FOR BIT DATA clause is associated with a default C buffer type of SQL_C_BINARY. If data is defined as FOR BIT DATA, it is transferred to:
Existing applications that explicitly use SQL_C_CHAR with data defined as FOR BIT DATA, will get a different result and may receive only half of the original data. The initialization keyword, BITDATA, can be set to 0 to force DB2 CLI to treat FOR BIT DATA in the same was as previous versions.
Existing applications may be affected if tables are modified to make use of User Defined Types.
If a parameter marker is used in a predicate of a query statement, and the parameter is a user defined type, the statement must use a CAST function to cast either the parameter marker or the UDT.
For example, if the following type and table is defined:
CREATE DISTINCT TYPE CNUM AS INTEGER WITH COMPARISONS
CREATE TABLE CUSTOMER (
Cust_Num CNUM NOT NULL,
First_Name CHAR(30) NOT NULL,
Last_Name CHAR(30) NOT NULL,
Phone_Num CHAR(20) WITH DEFAULT,
PRIMARY KEY (Cust_Num) )
The following statement would fail since the parameter marker cannot be of type CNUM and thus the comparison fails due to incompatible types:
SELECT first_name, last_name, phone_num FROM customer where cust_num = ?
Casting the column to integer (its base SQL type), allows the comparison to work since a parameter can be provided for type integer:
SELECT first_name, last_name, phone_num from customer where cast( cust_num as integer ) = ?
Alternatively the parameter marker can be cast to INTEGER. This informs the server of the parameter marker's type and allows the default INTEGER to CNUM conversion to be applied:
SELECT first_name, last_name, phone_num FROM customer where cust_num = cast( ? as integer )
Refer to the custrep.c sample file for a full working example.
Refer to the SQL Reference for more information about:
In versions prior to Version 2.1, DB2 CLI returned a set of bitmasks for the fInfoTypes which started with SQL_CONVERT_ (for example, SQL_CONVERT_INTEGER). fInfoTypes which where used with corresponding comparison bitmasks which started with SQL_CVT_ (for example, SQL_CVT_CHAR).
Since this fInfoType is defined by ODBC to indicate supported conversion functions, and these functions are not supported, DB2 CLI Version 2.1 now (correctly) returns zero for all SQL_CONVERT fInfoTypes.
In order to better align with X/Open and ODBC, some DB2 CLI function arguments have changed to unsigned data types, and two new types have been introduced, SQLUINTEGER and SQLUSMALLINT.
DB2 CLI applications prior to version 2.1 will generate complier errors for mismatched function arguments when compiled with version 2.1 header files unless DB2CLI_VER is defined, see Setting the DB2CLI_VER Define.
We recommend that existing source files eventually be modified to declare the necessary arguments using the SQLUINTEGER and SQLUSMALLINT types.
The DB2CLI_VER define allows the application to specify that the DB2 CLI header files are to remain compatible with previous versions of DB2 CLI.
DB2CLI_VER must be set to a hex value either as a compile flag, or as a #define before the DB2 CLI header files are included. For example, using the -D compiler flag:
-DDB2CLI_VER=0x0110
sets the DB2CLI_VER to Version 1.1.
In Version 2.1, if DB2CLI_VER is not defined it defaults to 0x0210.