Chapter 27 Quiz: Embedded SQL Fundamentals

Multiple Choice

1. What delimiters surround SQL statements in a COBOL program?

a) SQL BEGIN ... SQL END b) EXEC SQL ... END-EXEC c) EXEC DB2 ... END-DB2 d) {SQL ... SQL}

2. How are COBOL host variables identified within SQL statements?

a) Prefixed with an ampersand (&) b) Prefixed with a colon (:) c) Prefixed with a dollar sign ($) d) Enclosed in square brackets []

3. SQLCODE = +100 indicates:

a) A successful operation b) No row found (or no more rows in a cursor) c) A deadlock occurred d) An authorization failure

4. What COBOL data type maps to DB2 DECIMAL(9,2)?

a) PIC 9(9)V99 DISPLAY b) PIC S9(7)V99 COMP-3 c) PIC S9(9)V99 COMP d) PIC X(11)

5. A VARCHAR column in DB2 maps to which COBOL structure?

a) A simple PIC X(n) field b) A two-part structure with a length (level 49 PIC S9(4) COMP) and a text field (level 49 PIC X(n)) c) A variable-length table with OCCURS DEPENDING ON d) A POINTER data item

6. What happens when SELECT INTO returns more than one row?

a) SQLCODE = 0 and only the first row is returned b) SQLCODE = +100 c) SQLCODE = -811 d) SQLCODE = -305

7. The correct sequence for cursor operations is:

a) OPEN → DECLARE → FETCH → CLOSE b) DECLARE → FETCH → OPEN → CLOSE c) DECLARE → OPEN → FETCH → CLOSE d) OPEN → FETCH → CLOSE → DECLARE

8. SQLERRD(3) contains:

a) The SQL error message length b) The number of rows affected by INSERT, UPDATE, or DELETE c) The position of the error in the SQL statement d) The DB2 subsystem ID

9. A null indicator variable with value -1 means:

a) The column value is zero b) The column value is empty string c) The column value is NULL d) An error occurred reading the column

10. The DB2 precompiler transforms EXEC SQL statements into:

a) Java method calls b) COBOL CALL statements to DB2 runtime modules c) Assembler instructions d) VSAM file I/O statements

11. What is a DBRM?

a) Database Recovery Management module b) Database Request Module (contains extracted SQL) c) Data Buffer Reference Map d) DB2 Runtime Manager

12. SQLCODE = -818 typically means:

a) Table not found b) Authorization failure c) Timestamp mismatch — program was recompiled but not rebound d) Deadlock occurred

13. The WHENEVER statement is processed by:

a) The COBOL compiler at compile time b) The DB2 runtime at execution time c) The DB2 precompiler at precompile time d) The JCL processor

14. Which SQL statement makes changes permanent?

a) SAVE b) CHECKPOINT c) COMMIT d) FLUSH

15. FOR UPDATE OF in a cursor declaration:

a) Prevents any updates to the table b) Acquires update locks on fetched rows for positioned updates c) Automatically updates rows as they are fetched d) Is required for all cursors

True/False

16. Each EXEC SQL block can contain multiple SQL statements separated by semicolons. ___

17. DCLGEN generates COBOL data structures from DB2 table definitions. ___

18. You can use COBOL's COPY statement instead of EXEC SQL INCLUDE for the SQLCA. ___

19. A cursor must be opened before any rows can be fetched. ___

20. COMMIT and ROLLBACK are the only SQL statements that do not require an EXEC SQL wrapper. ___

Short Answer

21. Explain why checking SQLCODE after every SQL statement is considered essential defensive programming. Give a specific example of what can go wrong if you skip the check.

22. Compare SELECT INTO with a cursor for retrieving data. When would you use each? What are the trade-offs?

23. Describe the batch COMMIT pattern (commit every N rows). What happens if you commit after every row? What happens if you commit only at the end? What value of N is appropriate, and what factors influence this choice?

24. Tomás Rivera chose separate queries instead of a JOIN for the MedClaim claim inquiry. Explain his reasoning. When would a JOIN be the better choice?

25. Explain the relationship between the precompiler, the COBOL compiler, and the DB2 BIND process. What does each step produce, and what error messages come from each?

Answer Key

  1. b — EXEC SQL ... END-EXEC
  2. b — Prefixed with a colon (:)
  3. b — No row found (or no more rows)
  4. b — PIC S9(7)V99 COMP-3 (packed decimal maps to DB2 DECIMAL)
  5. b — Two-part structure with level 49 length and text fields
  6. c — SQLCODE = -811 (multiple rows returned for SELECT INTO)
  7. c — DECLARE → OPEN → FETCH → CLOSE
  8. b — Number of rows affected by INSERT, UPDATE, or DELETE
  9. c — The column value is NULL
  10. b — COBOL CALL statements to DB2 runtime modules
  11. b — Database Request Module (contains extracted SQL from the precompiler)
  12. c — Timestamp mismatch — the DBRM and load module are out of sync
  13. c — The DB2 precompiler at precompile time (it is a precompiler directive)
  14. c — COMMIT
  15. b — Acquires update locks for positioned UPDATE/DELETE with WHERE CURRENT OF
  16. False — Each EXEC SQL block contains exactly one SQL statement
  17. True
  18. True — Though EXEC SQL INCLUDE is preferred because the precompiler processes it
  19. True
  20. False — All SQL statements require EXEC SQL ... END-EXEC, including COMMIT and ROLLBACK