Embedded SQL Basics

To develop application programs that access DB2 tables, you must embed SQL statements in the program statements of the high-level language being used. Embedded DB2 SQL statements are supported in the following high-level languages: ADA, APL2, Assembler, BASIC, C, COBOL, FORTRAIN, Java, PL/I, Prolog, and REXX. Refer to the IBM manuals for the specific release and version numbers for the compiler or runtime environment supported for each language.

These programs can be run in the following execution environments:

MVS batch using CAF

TSO batch

DL/I batch

CICS

WebSphere MQ

IMS/TM (previously known as IMS/DC)

IMS BMP

TSO (interactive)

RRSAF (Recovery Resource Manager Services Attachment Facility)

In this chapter, I focus on the rules for embedding SQL in COBOL application programs because COBOL is the most widely used language in the business data processing community. Much of the information is similar for the other languages. For language-specific information and syntax, consult the appropriate IBM manuals.

Additionally, this chapter will focus on embedded static SQL because this is the predominant method used to develop DB2 application programs. Other methods include embedded dynamic SQL, ODBC, and JDBC. Chapter 12, "Dynamic SQL Programming," focuses on dynamic SQL, and Chapter 14 discusses ODBC and JDBC.

To embed SQL statements in an application program, you must follow strict rules. These rules have been established for a few reasons. One, they enable parsing programs (a DB2 precompiler) to identify embedded SQL statements easily in application code. Two, they ensure that the impedance mismatch between the non-procedural, set-level processing of SQL and the procedural, record-level processing of the high-level language has been taken into account. Three, these rules provide programs with the capability to change variables in the predicates of the embedded SQL at processing time. And four, they enable communication between the DB2 DBMS and the application program (for example, the reception of error and warning messages).

The capability to embed SQL statements in an application program allows high-level programming languages to access DB2 data. This capability provides the mechanism for the development of just about any type of DB2 application system.

All DB2 statements can be embedded in an application program. The list of SQL statements supported for embedding in an application program is presented in Table 11.1.

Table 11.1. Types of Embedded SQL Statements

SQL Type	SQL Statements
DCL	GRANT and REVOKE
DDL	ALTER , CREATE , DROP , COMMENT ON , and LABEL ON
DML	DELETE , INSERT , SELECT , and UPDATE
Dynamic SQL	DESCRIBE , EXECUTE , EXECUTE IMMEDIATE , and PREPARE
Distributed control	CONNECT , RELEASE , SET CONNECTION , RELEASE
Stored Procedures/LOBs	CALL , ALLOCATE CURSOR , ASSOCIATE LOCATORS , FREE LOCATOR , HOLD LOCATOR
Triggers	VALUES , SQL/PSM features
Definition control	BEGIN DECLARE SECTION , INCLUDE
Embedding control	CLOSE , DECLARE , FETCH , and OPEN
Transaction control	COMMIT , ROLLBACK , SAVEPOINT , RELEASE SAVEPOINT
Assignment	SET , VALUES INTO
General	EXPLAIN [*] , LOCK TABLE
Error handling	WHENEVER , SIGNAL SQLSTATE , GET DIAGNOSTICS

^[*] You can embed EXPLAIN only in TSO programs.

A DB2 program with embedded SQL statements is somewhat similar to an application program issuing reads and writes against a flat file or VSAM data set. The SQL statements are similar in function to file I/O. With a little basic understanding of embedded SQL rules and constructs, you, as an application programmer, can learn the methods necessary to embed SQL in a third-generation language, such as COBOL.

In the following sections, I discuss the techniques used to embed SQL statements in DB2 application programs.