Chapter 11. Using DB2 in an Application Program

IN THIS CHAPTER

• Embedded SQL Basics

• Embedded SQL Guidelines

• Host Variables

• Programming with Cursors

• Embedded SELECT and Cursor Coding Guidelines

• Modifying Data with Embedded SQL

• Application Development Guidelines

• Batch Programming Guidelines

• Online Programming Guidelines

• General SQL Coding Guidelines

DB2 application development consists of the construction of DB2 application programs. This statement begs the question: What is a DB2 application program? Let me begin to answer this question by reviewing standard application program development.

The development of an application system usually requires the use of a high-level language to encode the processing requirements of the application. A high-level language is any language that you can use to operate on data. You can break down high-level languages into the following categories:

• Database sublanguages, such as SQL

• 3GLs (third-generation languages), such as COBOL and FORTRAN, which are procedural

• 4GLs ( fourth-generation languages), such as RAMIS and FOCUS, which are procedural but raise the level of abstraction a notch , often enabling non-MIS personnel to develop applications

• GUI-based programming languages, such as Visual Basic and PowerBuilder, which are used to build distributed, client/server applications

• Internet and Web-based programming languages, using CGI scripts or Java applets, servlets, and programs

• CASE (computer-aided software engineering) tools, which enable analysts to analyze and specify application models and parameters (upper CASE) and automatically generate application programs (lower CASE)

• Productivity tools, such as report writers and QMF, which are useful for developing portions of an application but usually not robust enough to be used for the development of a complete application

Sometimes you can develop a complete application system entirely with SQL, 4GLs, code generators, or productivity tools. However, these systems are rare (although code generation is gaining approval and support in many DP shops ). Even though an application system can be coded without the use of a true programming language (3GL or GUI programming language), often a 3GL is still used because it generally outperforms the other application development tools just mentioned. This case is particularly true with code generators because the SQL that is generated is basic and not optimized for performance.

Back to the initial question: What is a DB2 application program? I consider a DB2 application program to be any program ”developed using any of the preceding methods ”that accesses data stored in DB2.

Most of the information in Part II of this book covers developing DB2 programs using third-generation languages, which constitute the bulk of DB2 applications. This is true for many reasons. Third-generation languages have been around longer than other application development tools and therefore have a larger installed base and a wider selection of professional programmers who understand them. Batch interfaces abound, but few online interfaces (CICS and IMS/TM) exist for most 4GLs and report writer tools.

Of course, GUI-based programming is on the rise, and many client/server applications are being developed to access DB2 data using these tools. The issues surrounding GUI-based DB2 programming are covered in Chapter 14, "Alternative DB2 Application Development Methods."

3GLs have proliferated for several other reasons. Their procedural nature eases the coding of complex logic structures (for example, IF-THEN-ELSE logic and looping). Other methods cannot usually meet complex reporting needs, such as the explosion of a hierarchy or side-by-side reporting of multiple, joined repeating groups. In addition, the performance of applications developed using alternative methods usually does not compare to the superb performance that you can achieve using 3GLs.