This document offers some pointers on how to write application programs for DB2, and for relational database management systems (RDBMSs) generally.

An application program (or often called an "APP", for short) for a database is any computer program that interacts with the database. Given that we have SQL for interacting with the database system, why would we need to use a programming language? There are many reasons we might. Basically, SQL was never meant to be a programming language, so there are many things that it cannot do. For anything we need that it cannot do, we must use a programming language. Some such cases are as follows.

• One may need to evaluate a "query" (often) which is beyond SQL's ability.

  • SQL proper is not computationally complete, nor was it designed to be. It is meant to be a query language, not a programming language. By the SQL-2 standards, SQL does not support recursion. (DB2 actually does! The current SQL-3 standards have added recursion.) SQL has no conditional statements, per se; for instance, an if ... then ... else. So it is virtually impossible to write a "program" in "SQL".

    For example, one may want to present certain information about alternative products to the user if the product requested is out of stock (as determined by a query the APP asked behind the scenes), but information about the product itself in the case that it is in stock.

  • Recall that an SQL query always returns a relational table. Sometimes, one wants to present information in some other format. This requires an APP.

• One may want to offer a GUI (graphical user interface) form to users to interact with the database in a more reasonable way (than raw SQL), or even just a nicer shell program interface, for some specific database task. SQL is not a programming language, so it offers no hooks to the window system or OS as do many programming languages.

  Likewise, an APP, since it is a program, can be designed to interact with other programs. (GUI is just a special case of this, really.) Again, SQL is not equipped for this.

• One may need to enforce a "business rule" over the database which cannot be easily enforced some other way, such as via integrity constraints.

  • For this, the APP will usually check for violations of the business rule.
  • Then, if there are violations, the APP repairs the database in a sensible way.

The The basic APP approach is embedded SQL. We recommend this for the project. DB2 supports C, C++, Cobol, Fortran, and Java at the least for embedded SQL programs and for CLI programs. See

• ~db2leduc/sqllib/samples/c/,
• ~db2leduc/sqllib/samples/cpp/,
• ~db2leduc/sqllib/samples/cobol_mf/,
• ~db2leduc/sqllib/samples/fortran/,
  and
• ~db2leduc/sqllib/samples/java/

on db.cs.wm.edu, respectively, for example programs and how to do the pre-compilation, bind, and compile steps.

Even scripting languages of various sorts such as Perl are generally supported. Perl's connection package is called DBI.

We support locally embedded SQL in C, C++, and Java, and "CLI" in Perl for your APP project.

You may write a CLI APP in C, C++, or Java (JDBC), instead. There are examples in the DB2 directories. However, there are no local web-pages up to walk you through it. So only go the CLI route (instead of the embedded SQL route) if you have some experience with it, and/or you are willing to put in the work yorself to look up the library calls and to look over the examples.

Some good resources for learning JDBC and SQLJ are:

• The Java Technology Home Page hosted by SUN covers a tutorial on JDBC programming, and
• The Application Development Guide hosted by IBM provides information on JDBC and on embedded SQL Java programs (SQLJ).

The standard approach for building APPs that the database environments offer is called Embedded SQL. This allows one to write SQL statements in one's programming code. Different vendors support different programming languages for embedded SQL. DB2 here supports C and Java, at least.

The program one writes then is a hybrid of regular (say, C) code and "embedded" SQL statements. DB2 calls such code for C a "SQC" program. Clearly the language compiler (say, cc) will not compile an embedded SQL program, since it will not recognize the "embedded SQL" statements. So first, one must pre-compile the program. This replaces the embedded SQL statements with legitimate program code (albeit quite ugly code) that consists of calls to special libraries for the database system. Thus, the result of the pre-compilation is a pure program (say, a C program). Every database system vendor which supports embedded SQL offers such a pre-compiler. One next compiles the resulting pure program as usual with the language compiler. The last step is then to use the compiler (say, cc) to link the compilation result with the appropriate libraries (for instance, the database system specific libraries mentioned above). Once done, one has an executable program that interacts with the database as programmed whenever it is run.

The CLI (Call Level Interface) approach is newer. In this approach, one writes pure programs. One makes calls to the database via pre-defined (and documented) CLI functions. No pre-compilation stage is needed. One just compiles the program and links the appropriate CLI libraries.

This is more elegant than the embedded SQL approach because no pre-compilation is needed, and because the code is clearer. It is not a hybrid of SQL and program. CLI is also meant to offer another advantage: The CLI calls are defined for most all the RDBMSs out there. So a given CLI program which was written and used with a DB2 database could be ported to work with an ORACLE database without being rewritten! It would only need recompiling, linked with the appropriate libraries.

The current disadvantage of CLI is that it supports a subset of the functionality that any given RDBMS supports. This is because it only supports the intersection of functionality of all the RDBMSs. So sometimes, one simply cannot do what one wants in CLI. Also, embedded SQL is still more common.

The ODBC and JDBC standards are based on CLI.

Perl and some other "scripting" languages (like Python) offer database interactivity. For Perl, a module called DBI has been developed. When employed, one can essentially write database connectivity and processing code in Perl. It follows the CLI paradigm. As with CLI, the DBI interface calls have been standardized to work with many different RDBMSs. So the same Perl program could be, in principle, used with a DB2 database and with an ORACLE database with no rewriting necessary! Also, Perl/DBI does not need the pre-compile stage, as an embedded SQL program does. Of course, this is somewhat necessary since Perl is an "interpreted" language.