Comparison of CLI and ODBC

The level of support that is provided depends on whether you use a Db2® ODBC driver or a CLI driver. In environments without a Db2 ODBC driver manager, CLI is a self-sufficient driver that supports a subset of the functions that are provided by the Db2 ODBC driver.

Figure 1 compares CLI and the Db2 ODBC driver. The left side shows an ODBC driver under the ODBC Driver Manager, and the right side illustrates CLI, the callable interface designed for Db2 applications.

Data Server Client refers to each available IBM Data Server Client. Db2 server refers to all Db2 server products on Linux®, UNIX, and Windows.

Figure 1. CLI and ODBC.
CLI and ODBC

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 CLI conforms to ODBC 3.51.

For ODBC application development, you must obtain an ODBC Software Development Kit. For the Windows platform, the ODBC SDK is available as part of the Microsoft Data Access Components (MDAC) SDK, available for download from http://www.microsoft.com/downloads. For non-Windows platforms, the ODBC SDK is provided by other vendors.

Table 1 summarizes the two levels of support, and the CLI and ODBC function summary provides a complete list of ODBC functions and indicates if they are supported.

Table 1. CLI ODBC support
ODBC features Db2 ODBC Driver CLI
Core level functions All All
Level 1 functions All All
Level 2 functions All All, except for SQLDrivers()
Additional CLI functions All, functions can be accessed by dynamically loading the CLI library.
  • SQLSetConnectAttr()
  • SQLGetEnvAttr()
  • SQLSetEnvAttr()
  • SQLSetColAttributes()
  • SQLGetSQLCA()
  • SQLBindFileToCol()
  • SQLBindFileToParam()
  • SQLExtendedBind()
  • SQLExtendedPrepare()
  • SQLGetLength()
  • SQLGetPosition()
  • SQLGetSubString()
SQL data types All the types listed for CLI.
  • SQL_BIGINT
  • SQL_BINARY
  • SQL_BIT
  • 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_TINYINT
  • SQL_TYPE_DATE
  • SQL_TYPE_TIME
  • SQL_TYPE_TIMESTAMP
  • SQL_VARBINARY
  • SQL_VARCHAR
  • SQL_VARGRAPHIC
  • SQL_WCHAR
C data types All the types listed for CLI.
  • SQL_C_BINARY
  • SQL_C_BIT
  • SQL_C_BLOB_LOCATOR
  • SQL_C_CHAR
  • SQL_C_CLOB_LOCATOR
  • SQL_C_TYPE_DATE
  • SQL_C_DBCHAR
  • SQL_C_DBCLOB_LOCATOR
  • SQL_C_DOUBLE
  • SQL_C_FLOAT
  • SQL_C_LONG
  • SQL_C_SHORT
  • SQL_C_TYPE_TIME
  • SQL_C_TYPE_TIMESTAMP
  • SQL_C_TIMESTAMP_EXT
  • SQL_C_TINYINT
  • SQL_C_SBIGINT
  • SQL_C_UBIGINT
  • SQL_C_NUMERIC 1
  • SQL_C_WCHAR
Return codes All the codes listed for 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
Note:
  1. Only supported on Windows operating systems.
  2. The listed SQL data types are supported for compatibility with ODBC 2.0.
    • SQL_DATE
    • SQL_TIME
    • SQL_TIMESTAMP
    You should use the SQL_TYPE_DATE, SQL_TYPE_TIME, or SQL_TYPE_TIMESTAMP instead to avoid any data type mappings.
  3. The listed SQL data types and C data types are supported for compatibility with ODBC 2.0.
    • SQL_C_DATE
    • SQL_C_TIME
    • SQL_C_TIMESTAMP
    You should use the SQL_C_TYPE_DATE, SQL_C_TYPE_TIME, or SQL_C_TYPE_TIMESTAMP instead to avoid any data type mappings.

Isolation levels

The table 2 maps IBM RDBMSs isolation levels to ODBC transaction isolation levels. The SQLGetInfo() function indicates which isolation levels are available.

Table 2. Isolation levels under ODBC
IBM isolation level ODBC isolation level
Cursor stability SQL_TXN_READ_COMMITTED
Repeatable read SQL_TXN_SERIALIZABLE_READ
Read stability SQL_TXN_REPEATABLE_READ
Uncommitted read SQL_TXN_READ_UNCOMMITTED
No commit (no equivalent in ODBC)
Note: SQLSetConnectAttr() and SQLSetStmtAttr() will return SQL_ERROR with an SQLSTATE of HY009 if you try to set an unsupported isolation level.

Restriction

Mixing ODBC and CLI features and function calls in an application is not supported on the Windows 64-bit operating system.