Static SQL

Most DB2 application programs use static SQL to access DB2 tables. A static SQL statement is a complete, unchanging statement hard-coded into an application program. It cannot be modified during the program's execution except for changes to the values assigned to host variables .

Static SQL is powerful and more than adequate for most applications. Any SQL statement can be embedded in a program and executed as static SQL. The following listing shows several examples of static SQL statements embedded in a COBOL program.

 WORKING-STORAGE SECTION.        .        .        .      EXEC SQL        INCLUDE SQLCA      END-EXEC.      EXEC SQL  TABLE  INCLUDE EMP  DECLARE  END-EXEC.         .         .         .      EXEC SQL  CURSOR  DECLARE CSR1 FOR          SELECT EMPNO, COMM  STATIC  FROM   EMP  SQL  WHERE  SALARY > 60000  SELECT  FOR UPDATE OF COMM  STATEMENT  END-EXEC.         .         .         . PROCEDURE DIVISION.         .         .         .      PERFORM OPEN-CSR1.      MOVE 'N' TO END-OF-DATA.      PERFORM FETCH-AND-MODIFY        UNTIL END-OF-DATA = 'Y'.      STOP RUN. FETCH-AND-MODIFY.      EXEC SQL        FETCH CSR1 INTO :HOST-EMPNO,  EMBEDDED  :HOST-COMM  FETCH  END-EXEC.      IF SQLCODE < +0        PERFORM ERROR-ROUTINE      ELSE        IF SQLCODE = +100          MOVE 'Y' TO END-OF-DATA        ELSE          PERFORM MODIFY-COMM. MODIFY-COMM.      IF HOST-COM < 1000        COMPUTE HOST-COMM = HOST-COMM + 100.      EXEC SQL        UPDATE  EMP  STATIC  SET COMM = :HOST-COMM  SQL  WHERE CURRENT OF CSR1  UPDATE  END-EXEC.  STATEMENT  IF SQLCODE < 0          PERFORM ERROR_ROUTINE. OPEN-CSR.      EXEC SQL        OPEN CSR1      END-EXEC.  OPEN &  CLOSE-CSR.  CLOSE  EXEC SQL  CURSOR  CLOSE CSR1  STATEMENTS  END-EXEC. 

To embed static SQL in a host program, you must prepare for the impedance mismatch between a high-level language and SQL. Impedance mismatch refers to the difference between set-at-a-time processing and record-at-a-time processing. High-level languages access data one record at a time, whereas SQL accesses data at a set level. Although DB2 always accesses data at the set level, the host program uses a structure called a cursor to access the set-level data one row at a time. SQL statements are coded with cursors that are opened, fetched from, and closed during the execution of the application program.

Static SQL is flexible enough that most application programmers never need to know any other means of embedding SQL in a program using a high-level language. Coding methods and guidelines are covered comprehensively in Chapter 11, "Using DB2 in an Application Program," where embedded SQL programming is discussed.

Sometimes, static SQL cannot satisfy an application's access requirements. For these types of dynamic applications, you can use another type of SQL: dynamic SQL .