Call Level Interface Guide and Reference

Appendix C. DB2 CLI and ODBC

This appendix discusses the support provided by the DB2 ODBC driver, and how it differs from DB2 CLI.

Figure 18 below compares DB2 CLI and the DB2 ODBC driver.

an ODBC driver under the ODBC Driver Manager
DB2 CLI, callable interface designed for DB2 specific applications.

DB2 Client refers to all available DB2 Clients. DB2 refers to all DB2 Universal Database products.

In an ODBC environment, the Driver Manager provides the interface to the application. It also dynamically loads the necessary driver for the database server that the application connects to. It is the driver that implements the ODBC function set, with the exception of some extended functions implemented by the Driver Manager. In this environment DB2 CLI conforms to level 2 of ODBC 2.0, and level 1 of ODBC 3.0. In addition it also conforms to the following ODBC 3.0 level 2 interface conformance items:

202: Dynamic parameters described using SQLDescribeParam
203: Support for input, output, and input/output parameters as well as result values of stored procedures
205: Advanced information on the data dictionary from SQLColumnPriviledges, SQLForeignKeys, and SQLTablePrivileges
207: Asynchronous execution of appropriate functions
209: SQL_ATTR_CONCURRENCY statement attribute can be set to at least one value other than SQL_CONCUR_READ_ONLY
211: Default isolation level can be changed. Transactions can be executed with the "serialized" level of isolation.

For ODBC application development, you must obtain an ODBC Software Development Kit (from Microsoft for Microsoft platforms, and from other vendors for non-Microsoft platforms.) When developing ODBC applications that may connect to DB2 servers, use this book (for information on DB2 specific extensions and diagnostic information) in conjunction with the ODBC 3.0 Software Development Kit and Programmer's Reference.

In environments without an ODBC driver manager, DB2 CLI is a self sufficient driver which supports a subset of the functions provided by the ODBC driver. Table 187 summarizes the two levels of support, and Table 12 provides a complete list of ODBC 3.0 functions, and indicates if they are supported.

Table 187. DB2 CLI ODBC Support

ODBC Features DB2 ODBC Driver DB2 CLI
Core Level Functions All All
Level 1 Functions All All
Level 2 Functions All All, except for SQLDrivers()
Additional DB2 CLI Functions All, functions can be accessed by dynamically loading the DB2 CLI library, see Appendix A, Programming Hints and Tips for more information.

SQLSetConnection()
SQLGetEnvAttr()
SQLSetEnvAttr()
SQLSetColAttributes()
SQLGetSQLCA()
SQLBindFileToCol()
SQLBindFileToParam()
SQLExtendedBind()
SQLExtendedPrepare()
SQLGetLength()
SQLGetPosition()
SQLGetSubString()

SQL Data Types All the types listed for DB2 CLI, as well as:

SQL_BINARY
SQL_VARBINARY
SQL_LONGVARBINARY

SQL_BIGINT
SQL_BINARY
SQL_BLOB
SQL_BLOB_LOCATOR
SQL_CHAR
SQL_CLOB
SQL_CLOB_LOCATOR
SQL_DBCLOB
SQL_DBCLOB_LOCATOR
SQL_DECIMAL
SQL_DOUBLE
SQL_FLOAT
SQL_GRAPHIC
SQL_INTEGER
SQL_LONGVARBINARY
SQL_LONGVARCHAR
SQL_LONGVARGRAPHIC
SQL_NUMERIC
SQL_REAL
SQL_SMALLINT
SQL_TYPE_DATE
SQL_TYPE_TIME
SQL_TYPE_TIMESTAMP
SQL_VARBINARY
SQL_VARCHAR
SQL_VARGRAPHIC

C Data Types All the types listed for DB2 CLI.

SQL_C_BINARY
SQL_C_BIT
SQL_C_BLOB_LOCATOR
SQL_C_CHAR
SQL_C_CLOB_LOCATOR
SQL_C_DATE
SQL_C_DBCHAR
SQL_C_DBCLOB_LOCATOR
SQL_C_DOUBLE
SQL_C_FLOAT
SQL_C_LONG
SQL_C_SHORT
SQL_C_TIME
SQL_C_TIMESTAMP
SQL_C_TINYINT
SQL_C_SBIGINT
SQL_C_UBIGINT
SQL_C_NUMERIC **
** Only supported on 32-bit Windows
Return Codes All the codes listed for DB2 CLI.

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_STILL_EXECUTING
SQL_NEED_DATA
SQL_NO_DATA_FOUND
SQL_ERROR
SQL_INVALID_HANDLE

SQLSTATES Mapped to X/Open SQLSTATES with additional IBM SQLSTATES, with the exception of the ODBC type 08S01. Mapped to X/Open SQLSTATES with additional IBM SQLSTATES
Multiple connections per application Supported Supported
Dynamic loading of driver Supported Not applicable

ODBC Features	DB2 ODBC Driver	DB2 CLI
Core Level Functions	All	All
Level 1 Functions	All	All
Level 2 Functions	All	All, except for `SQLDrivers()`
Additional DB2 CLI Functions	All, functions can be accessed by dynamically loading the DB2 CLI library, see Appendix A, Programming Hints and Tips for more information.	`SQLSetConnection()` `SQLGetEnvAttr()` `SQLSetEnvAttr()` `SQLSetColAttributes()` `SQLGetSQLCA()` `SQLBindFileToCol()` `SQLBindFileToParam()` `SQLExtendedBind()` `SQLExtendedPrepare()` `SQLGetLength()` `SQLGetPosition()` `SQLGetSubString()`
SQL Data Types	All the types listed for DB2 CLI, as well as: SQL_BINARY SQL_VARBINARY SQL_LONGVARBINARY	SQL_BIGINT SQL_BINARY SQL_BLOB SQL_BLOB_LOCATOR SQL_CHAR SQL_CLOB SQL_CLOB_LOCATOR SQL_DBCLOB SQL_DBCLOB_LOCATOR SQL_DECIMAL SQL_DOUBLE SQL_FLOAT SQL_GRAPHIC SQL_INTEGER SQL_LONGVARBINARY SQL_LONGVARCHAR SQL_LONGVARGRAPHIC SQL_NUMERIC SQL_REAL SQL_SMALLINT SQL_TYPE_DATE SQL_TYPE_TIME SQL_TYPE_TIMESTAMP SQL_VARBINARY SQL_VARCHAR SQL_VARGRAPHIC
C Data Types	All the types listed for DB2 CLI.	SQL_C_BINARY SQL_C_BIT SQL_C_BLOB_LOCATOR SQL_C_CHAR SQL_C_CLOB_LOCATOR SQL_C_DATE SQL_C_DBCHAR SQL_C_DBCLOB_LOCATOR SQL_C_DOUBLE SQL_C_FLOAT SQL_C_LONG SQL_C_SHORT SQL_C_TIME SQL_C_TIMESTAMP SQL_C_TINYINT SQL_C_SBIGINT SQL_C_UBIGINT SQL_C_NUMERIC Only supported on 32-bit Windows
Return Codes	All the codes listed for DB2 CLI.	SQL_SUCCESS SQL_SUCCESS_WITH_INFO SQL_STILL_EXECUTING SQL_NEED_DATA SQL_NO_DATA_FOUND SQL_ERROR SQL_INVALID_HANDLE
SQLSTATES	Mapped to X/Open SQLSTATES with additional IBM SQLSTATES, with the exception of the ODBC type 08S01.	Mapped to X/Open SQLSTATES with additional IBM SQLSTATES
Multiple connections per application	Supported	Supported
Dynamic loading of driver	Supported	Not applicable

For more information on ODBC refer to ODBC 3.0 Software Development Kit and Programmer's Reference.

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

[ DB2 List of Books | Search the DB2 Books ]