IBM DB2 UDB Call-Level Interface (CLI)

The DB2 Call-Level Interface (DB2 CLI) is an IBM standard for the DB2 family of database servers. It is tuned up for C/C++ API programming, and it is used for establishing connections, passing dynamic SQL statements, or calling persistent modules (like stored procedures).

DB2 CLI is based on the ODBC specification and the international standard for SQL/CLI, discussed earlier in this chapter. The DB2 CLI driver acts like a comparable ODBC driver, and it conforms to level 2 of ODBC 2.0 and level 1 of the ODBC 3.0. Some features of ODBC 3.0 level-2 conformance are also supported.

Figure 16-6 illustrates the way an application accesses DB2 RDBMS server using the CLI driver, the main difference being that the ODBC driver scenario does not have the additional access layer of the ODBC Driver Manager.

Figure 16-6: Accessing IBM DB2 UDB through CLI

Table 16-4 lists DB2 CLI driver features and ODBC driver for DB2 side by side with the ODBC Standard Specification.

Table 16-4: DB2 CLI vs. DB2 ODBC Driver Comparison
ODBC Features	DB2 CLI	DB2 ODBC driver
Core level functions	Implemented.	Implemented.
Level 1 functions	Implemented.	Implemented.
Level 2 functions	Implemented (except for SQLDrivers(), as nonapplicable).	Implemented.
Additional DB2 CLI functions	SQLSetConnection(), SQLGetEnvAttr(), SQLSetEnvAttr(), SQLSetColAttributes(),SQLGetSQLCA(), SQLBindFileToCol(),SQLBindFileToParam(), SQLExtendedPrepare(), SQLGetLength(), SQLGetPosition(), SQLSetSubstring()	Implemented all functions; can be accessed by dynamically loading DB2 CLI Library.
SQL data types	SQL_BIGINT, SQL_BINARY,SQL_BLOB, SQL_BLOB_LOCATOR, SQL_CHAR, SQL_CLOB,SQL_CLOB_LOCATOR, SQL_DCLOB, SQL_DCLOB_LOCATOR, SQL_DECIMAL, SQL_DOUBLE, SQL_FLOAT, SQL_GRAPHIC, SQL_INTEGER, SQL_LONGVARBINARY, SQL_LONGVARCHAR, SQLLONGVARGRAPHIC, SQL_NUMERIC, SQL_REAL, SQL_SMALLINT, SQL_TYPE_DATE, SQL_TYPE_TIME, SQL_TYPE_TIMESTAMP, SQL_VARBINARY, SQL_VARCHAR, SQL_VARGRAPHIC.	Supports all types listed for DB2 CLI, in addition to the ODBC standard.
C data types	SQL_C_BINARY, SQL_C_BIT, SQLC_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_SBIGINT, SQL_C_UBIGINT. SQL_C_NUMERIC (supported for Win32 environment only).
Return codes	SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_STILL_EXECUTING, SQL_NEED_DATA, SQL_NO_DATA_FOUND, SQL_ERROR, SQL_INVALID_HANDLE.	Supports all types listed for DB2 CLI, in addition to the ODBC standard.
SQLSTATES (SQL standard compliance)	Some codes are not present (e.g., ODBC Type 08S01); otherwise the same as ODBC driver.	Mapped to X/Open standard SQLSTATES(in addition to the IBM-specific SQLSTATES codes).
Multiple connections for the client application	Supported.	Supported.
Dynamic loading of driver	N/A	Supported.

The main advantage of using DB2 CLI over embedded SQL is flexibility and convenience. Embedded SQL requires a precompiler to convert SQL Statements into executable code, which must be then bound to the DB2 database. DB2 CLI implementation is not tied to a particular product or environment, resulting in portable code; it is SQL standards (X/Open) compliant, can connect to a multiple databases (so much for the binding concept!), can use dynamic parameters, and, in general, is more suited for GUI (graphical user interface) applications. There is a performance penalty to pay for all this; embedded SQL is inherently faster because it does not go through all the layers of communication and translation.

Compared to DB2 ODBC driver, there's not much difference. True, elimination some of the middleware (ODBC Driver Manager) offers increased speed of execution and fewer environment setup problems (no ODBC Driver Manager). But it comes at the price of more limited set of features, and it relies squarely on IBM implementation (as opposed third-party ODBC drivers for DB2).