To understand DB2 CLI or any callable SQL interface, it is helpful to understand what it is based on, and to compare it with existing interfaces.
The X/Open Company and the SQL Access Group jointly developed a specification for a callable SQL interface referred to as the X/Open Call Level Interface. The goal of this interface is to increase the portability of applications by enabling them to become independent of any one database vendor's programming interface. Most of the X/Open Call Level Interface specification has been accepted as part of the ISO Call Level Interface International Standard (ISO/IEC 9075-3:1995 SQL/CLI).
Microsoft developed a callable SQL interface called Open Database Connectivity (ODBC) for Microsoft operating systems based on a preliminary draft of X/Open CLI.
Version 3 of ODBC conforms to almost all of ISO SQL/CLI. ODBC 3.0 does contain considerable functionality that is not part of the International Standard; much of this is being added to the the next draft of the standard.
The ODBC specification also includes an operating environment where database specific ODBC Drivers are dynamically loaded at run time by a driver manager based on the data source (database name) provided on the connect request. The application is linked directly to a single driver manager library rather than to each DBMS's library. The driver manager mediates the application's function calls at run time and ensures they are directed to the appropriate DBMS specific ODBC driver. Since the ODBC driver manager only knows about the ODBC-specific functions, DBMS-specific functions cannot be accessed in an ODBC environment. DBMS-specific dynamic SQL statements are supported via a mechanism called an escape clause which is described in Using Vendor Escape Clauses.
ODBC is not limited to Microsoft operating systems; other implementations are available on various platforms.
The DB2 CLI load library can be loaded as an ODBC driver by an ODBC driver manager. 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 Programmer's Reference and SDK Guide.
Applications written directly to DB2 CLI link directly to the DB2 CLI load library. DB2 CLI includes support for many ODBC and ISO SQL/CLI functions, as well as DB2 specific functions. For a list of supported functions, refer to DB2 CLI Function Summary.
For more information on the relationship between DB2 CLI and ODBC, refer to Appendix C, DB2 CLI and ODBC.
The following DB2 features are available to both ODBC and DB2 CLI applications:
DB2 CLI also contains extensions to access DB2 features that can not be accessed by ODBC applications: