CLI and ODBC Overview

How do ODBC and CLI fit together? IBM's CLI driver implements most of the function set that is defined by Microsoft's ODBC. Microsoft supplies a Driver Manager that manages many different vendors' drivers such as IBM's CLI for the Windows environment. A Windows application that wants to use ODBC to access a database is dynamically linked to the ODBC Driver Manager and makes a function call to it such as SQLConnect(). The Manager then finds this function in the appropriate vendor supplied driver, such as IBM's CLI driver, and uses it to communicate to the DBMS (in this case, it connects to a database).

A program does not necessarily need to be dynamically linked to the ODBC Driver Manager to access a DB2 database. It could, alternatively, be linked with the CLI driver. This process will remove a layer of function calls that would otherwise be required, and make available CLI specific calls that are not available through the Manager. The disadvantage is that the application becomes DB2 specific and can no longer be used to access a different database such as Oracle. The other disadvantage is that the application no longer has access to specific features found within the Manager, such as connection pooling. Figure 11.1 illustrates the relationship between the application and DB2 in both the ODBC and CLI environments.

So far, we've been primarily talking about the Windows environment, but ODBC Driver Managers are also available for OS2 and many UNIX platforms. Merant is one such supplier of a Manager for UNIX operating systems. IBM now supports the Open Source ODBC Manager for UNIX; applications can make use of this rather than buying a third-party manger. The Merant ODBC Driver Manager uses character code page 1208 to talk to UNICODE drivers. This is contrary to Microsoft's ODBC manager, which uses code page 1200. IBM has decided to also use code page 1200, which means that UNICODE applications that want to use the Merant ODBC manager must use the Merant DB2 driver. Two alternatives exist: statically link to the DB2 driver and not use Merant's manager or use the Open Source ODBC manager instead. Of course, the Merant DB2 driver can be used, but this is not functionally as rich as DB2's driver.

Advantages of CLI

Often accessing a database through DB2 CLI is compared to accessing it through embedded SQL. The DB2 CLI has several key advantages over embedded SQL.

• CLI is ideally suited for a client–server environment, in which the target database is not known when the application is built. It provides a consistent interface for executing SQL statements, regardless of which database server the application is connected to.

• CLI increases the portability of applications by removing the dependence on precompilers. Applications are distributed not as embedded SQL source code, which must be preprocessed for each database product, but as compiled applications or run-time libraries.

• Individual DB2 CLI applications do not need to be bound to each database, only bind files shipped with DB2 CLI need to be bound once for all DB2 CLI applications. This can significantly reduce the amount of management required for the application after it is in general use.

• DB2 CLI applications can connect to multiple databases, including multiple connections to the same database, all from the same application.

• Each connection has its own commit scope. This is much simpler using CLI than using embedded SQL where the application must make use of multithreading to achieve the same result.

• DB2 CLI eliminates the need for application-controlled, often complex data areas, such as the SQLDA and SQLCA, typically associated with embedded SQL applications. Instead, DB2 CLI allocates and controls the necessary data structures and provides a handle for the application to reference them.

• DB2 CLI enables the development of multithreaded, thread-safe applications in which each thread can have its own connection and a separate commit scope from the rest. DB2 CLI achieves this by eliminating the data areas described previously, and associating all such data structures that are accessible to the application with a specific handle.

• Unlike embedded SQL, a multithreaded CLI application does not need to call any of the context management DB2 APIs; this is handled by the DB2 CLI driver automatically.

• DB2 CLI provides enhanced parameter input and fetching capability, allowing arrays of data to be specified on input, retrieving multiple rows of a result set directly into an array, and executing statements that generate multiple result sets.

• DB2 CLI provides a consistent interface to query catalog (Tables, Columns, Foreign Keys, Primary Keys, etc.) information contained in the various DBMS catalog tables. The result sets returned are consistent across DBMSs. This shields the application from catalog changes across releases of database servers, as well as catalog differences among different database servers; thereby saving applications from writing version-specific and server-specific catalog queries.

• Extended data conversion is also provided by DB2 CLI, requiring less application code when converting information between various SQL and C data types.

• DB2 CLI incorporates both the ODBC and X/Open CLI functions, both of which are accepted industry specifications. DB2 CLI is also aligned with the emerging ISO CLI standard. Knowledge that application developers invest in these specifications can be applied directly to DB2 CLI development, and vice versa. This interface is intuitive to grasp for those programmers who are familiar with function libraries but know little about product specific methods of embedding SQL statements into a host language.

• DB2 CLI provides the ability to retrieve multiple rows and result sets generated from a stored procedure residing on a DB2 UDB (or DB2 for MVS/ESA V5 or later) server. However, note that this capability exists for V5 DB2 UDB clients using embedded SQL if the stored procedure resides on a server accessible from a DataJoiner V2 server.

• DB2 CLI supports server-side scrollable cursors that can be used in conjunction with array output. This is useful in GUI applications that display database information in scroll boxes that make use of the <Page Up>, <Page Down>, <Home>, and <End> keys. You can declare a read-only cursor as scrollable and then move forward or backward through the result set by one or more rows. You also can fetch rows by specifying an offset from:

  • The current row

  • The beginning or end of the result set

  • A specific row you have previously set with a bookmark

• DB2 CLI applications can dynamically describe parameters in an SQL statement the same way that CLI and embedded SQL applications describe result sets. This enables CLI applications to dynamically process SQL statements that contain parameter markers without knowing the data type of those parameter markers in advance. When the SQL statement is prepared, describe information is returned detailing the data types of the parameters.