Driver-Based Application Solutions

Embedded SQL applications require that a vendor's precompiler first precompile the application and then the resultant source code be compiled and linked directly with a vendor's library. In most cases, the application will also have to be bound to the database that it is accessing so that the database can generate an access plan based on static queries made within the application. If a developer wants his application to work against another vendor's database, then he needs to repeat these steps for an alternate database. It's a lot of work, but at least the source code should not need to be modified.

An alternative approach to accessing databases is to use a driver-based solution. This solution involves a driver manager to which the application interfaces. The driver manager provides a set of industry standard interfaces (APIs) to access a data source. An application is written to call these APIs and then compiled and linked with the manager's libraries. During the execution of the application, the manager loads the correct vendor-supplied driver to access the appropriate data source. If a developer wants to access a different vendor's data source, then all he needs to do is ensure that the client has the appropriate driver correctly installed. The following sections describe a number of driver-based solutions for DB2.

Application Software Layers

In DB2 v7, the common client interface for all higher-level drivers was the DB2 CLI. For example, the JDBC driver translated its calls into CLI, as did the Perl DBI and Net.Data's DTW_SQL language environment. DB2 v8 has seen an extensive rewrite for the client interface. The common client code has been made standard at a functionally lower level. This allows DB2 to provide separate drivers for several interfaces. The CLI is used for ODBC, the Perl DBI, and for the JDBC type 2 and 3 drivers. However, new drivers that are based on the v8 common client code are now available for OLE DB and the new JCC (Java Common Client) drivers for JDBC type 4 and type 2. At the time of writing, the new JCC drivers were due to be available by FixPak 2.

CLI/ODBC

Both DB2's CLI standard, and Microsoft's ODBC standard were originally based on parts of the X/Open CLI Standard. The ODBC standard provides a set of CLI or API for accessing a vendor's database. The ODBC standard requires that a vendor supply its own driver and provide a set of function calls for accessing that database. How complete a vendor's set of APIs is compared to the set listed by the ODBC standard determines the driver's level of compliance.

IBM's DB2 CLI driver can be used on its own to access a DB2 database or as an ODBC driver. DB2 conforms to most of the level 3 compliance for ODBC. The CLI driver contains additional functions that cannot be used through an ODBC interface. To make use of these functions, the CLI driver has to be linked to the application program directly, rather than be used as an ODBC driver.

To develop CLI applications, you need the IBM DB2 UDB Developer's Software Kit. This kit includes all of the necessary header files and libraries for compiling and linking CLI applications. To develop ODBC applications, you need an ODBC Developer's Software Kit. IBM does not provide this kit, but Microsoft supplies this Kit for Windows platforms, and a number of vendors can be found for UNIX platforms. Details for developing CLI/ODBC applications can be found in Chapter 11.

ADO and OLE DB

ADO is Microsoft's ActiveX Data Objects set of classes that provide a set of methods for accessing data from a multitude of sources. These sources can be from relational databases and also from nonrelational databases, such as HyperText Markup Language (HTML), mail, video, or text, and just about anything else. The data access is handled under the hood of ADO and access can be provided by different services such as Remote Data Services (RDS), ODBC, or OLE DB.

RDS is an old service that is not discussed in this book. ODBC is a popular service that has widespread uses within the data access industry (discussed in Chapter 11). OLE DB is the new data access service of the future. OLE DB is designed to provide access to several relational and nonrelational data sources. We introduce this driver to you at the end of Chapter 11. It has been placed at the end of the chapter that discusses ODBC because OLE DB will likely replace ODBC in the near future.

JDBC

There are several key advantages if you decide to develop your database applications in Java. First and foremost, your Java bytecode can run on any platform, so it is easily portable. Secondly, you can save the cost of expensive compilers and other development tools if you have a small business. Java is also still a relatively new technology so there are many ongoing efforts to extend its functionality and performance.

The UDB programming interface for Java is JDBC. JDBC programs are run using dynamic SQL. DB2 v8 provides support for JDBC 2.1 on Intel and UNIX platforms. DB2 provides a Type II JDBC driver for Java applications and servlets. This is known as the app driver. For Java applets, DB2 provides the net driver, which is a Type III driver. In Fixpak 2 of DB2 v8, a Type IV driver will also be introduced which can replace both the Type II and Type III drivers.

In addition, DB2 provides support for many JDBC 2.1 advanced features that are part of the JDBC 2.1 Standard Extensions API. Among the features supported by DB2's JDBC 2.1 drivers are connection pooling using DataSource objects, Java Naming and Directory Interface (JNDI) support, and the Java Transaction API (JTA) support for XA transactions.

To develop JDBC 2.1 applications, you need to download the Java 2 SDK Standard Edition for Core API support. To use the JDBC 2.1 Standard Extensions API, you need to download the Java 2 SDK Enterprise Edition. You can obtain these by following the links listed in the Additional References section of Chapter 12.

SQLj

SQLj provides a performance boost for some JDBC programs. As mentioned previously, static SQL generally provides better performance than dynamic SQL in many cases because the query access plan is stored in a database package at bind time. SQLj allows you to embed SQL statements into your Java programs and statically bind them to DB2 databases. You still enjoy Java's code portability, and its rich API using this approach. SQLj is discussed in detail in Chapter 12.

Perl Scripts Using the DBI

Perl has become a very popular scripting language for various UNIX platforms and also can be found for the Windows platform. Its source code is available as part of the Open Source Standard and many binaries can be downloaded from the Perl Web site www.perl.com. Perl is especially popular for use with Web servers through the Common Gateway Interface (CGI). It is primarily because of this that Perl has developed and provided the Database Interface (DBI) for accessing relation databases.

DBI provides a set of standard class methods for which database vendors can supply an interface to their databases. These drivers are known as Database Drivers (DBD). Vendors usually supply these drivers free and they can be found at the Perl Web site. To develop Perl applications that access DB2, you need Perl, the DBI module, and the DBD::DB2 driver. Chapter 13 discusses this in detail.