Chapter 22 Quiz: Embedded SQL and DB2 Fundamentals

Test your understanding of embedded SQL concepts, COBOL-DB2 integration, cursor processing, SQLCODE handling, and host variable usage. Answers are hidden behind expandable sections.

Question 1 — Multiple Choice

What is the correct way to reference a host variable named WS-ACCOUNT-NO inside an embedded SQL statement?

A. WS-ACCOUNT-NO B. :WS-ACCOUNT-NO C. &WS-ACCOUNT-NO D. #WS-ACCOUNT-NO

Answer **B. `:WS-ACCOUNT-NO`** Host variables are prefixed with a colon (`:`) when referenced inside `EXEC SQL ... END-EXEC` blocks. Outside of SQL statements, they are referenced normally without the colon.

Question 2 — True/False

The EXEC SQL INCLUDE SQLCA END-EXEC statement must be placed in the PROCEDURE DIVISION of a COBOL program.

Answer **False.** The `INCLUDE SQLCA` statement is placed in the WORKING-STORAGE SECTION of the DATA DIVISION. The SQLCA is a data structure, not executable code.

Question 3 — Fill in the Blank

When a SELECT INTO statement finds no matching rows, DB2 sets SQLCODE to __.

Answer **+100** An SQLCODE of +100 indicates that no row was found (for SELECT INTO) or that the end of the result set has been reached (for FETCH).

Question 4 — Multiple Choice

Which of the following is NOT a valid cursor operation in COBOL-DB2?

A. EXEC SQL DECLARE ... CURSOR FOR ... B. EXEC SQL OPEN ... C. EXEC SQL READ ... D. EXEC SQL FETCH ... E. EXEC SQL CLOSE ...

Answer **C. `EXEC SQL READ ...`** There is no `READ` operation for cursors. The correct sequence is DECLARE, OPEN, FETCH (in a loop), and CLOSE. `READ` is a COBOL file I/O verb, not an SQL cursor operation.

Question 5 — Short Answer

Explain the purpose of the DB2 precompiler in the COBOL-DB2 development process.

Answer The DB2 precompiler scans the COBOL source for `EXEC SQL` statements and performs two tasks: (1) it replaces each embedded SQL statement with COBOL CALL statements to the DB2 runtime interface, producing a modified COBOL source that can be compiled by a standard COBOL compiler; and (2) it extracts the SQL statements into a Database Request Module (DBRM) that is later bound into a DB2 plan or package for access path optimization.

Question 6 — Multiple Choice

SQLCODE -805 at runtime most likely indicates:

A. A syntax error in the SQL statement. B. The program's package or plan was not found in DB2. C. A deadlock was detected. D. A column value exceeded its defined length.

Answer **B. The program's package or plan was not found in DB2.** SQLCODE -805 means that the DBRM or package name specified in the plan could not be found. This typically occurs when the BIND step was not executed after precompilation, or the plan references an outdated or missing package.

Question 7 — True/False

A cursor declared with FOR UPDATE OF BALANCE allows you to use a positioned UPDATE (UPDATE ... WHERE CURRENT OF cursor-name) to modify the BALANCE column of the currently fetched row.

Answer **True.** The `FOR UPDATE OF` clause in the cursor declaration enables positioned updates. After fetching a row, you can issue `EXEC SQL UPDATE ... SET ... WHERE CURRENT OF cursor-name END-EXEC` to update the specified columns of that row.

Question 8 — Fill in the Blank

The SQLCA field that contains the number of rows affected by an INSERT, UPDATE, or DELETE statement is __.

Answer **SQLERRD(3)** The third element of the SQLERRD array in the SQLCA holds the count of rows affected by the most recent INSERT, UPDATE, or DELETE operation.

Question 9 — Multiple Choice

Which isolation level provides the highest degree of data consistency but the lowest concurrency?

A. Uncommitted Read (UR) B. Cursor Stability (CS) C. Read Stability (RS) D. Repeatable Read (RR)

Answer **D. Repeatable Read (RR)** Repeatable Read locks all rows that qualify for the result set for the duration of the unit of work, preventing other transactions from inserting, updating, or deleting those rows. This eliminates phantom reads but holds locks the longest, reducing concurrency.

Question 10 — Short Answer

What is the difference between EXEC SQL COMMIT END-EXEC and EXEC SQL COMMIT WORK END-EXEC?

Answer There is no functional difference. Both statements commit the current unit of work. The keyword `WORK` is optional in DB2 SQL. Both forms release locks (except for cursors declared `WITH HOLD`) and make all changes since the last commit point permanent.

Question 11 — Multiple Choice

What does SQLCODE -818 indicate?

A. A table was not found. B. A timestamp mismatch between the DBRM and the loaded module. C. A constraint violation occurred. D. The SQL statement is too long.

Answer **B. A timestamp mismatch between the DBRM and the loaded module.** SQLCODE -818 occurs when the precompiler timestamp in the DBRM (bound into the plan/package) does not match the timestamp in the load module. This happens when the program is recompiled but not rebound, or vice versa. The fix is to precompile, compile, link-edit, and bind in a consistent sequence.

Question 12 — True/False

An indicator variable must be declared as PIC S9(4) COMP (a halfword binary integer) in COBOL.

Answer **True.** DB2 indicator variables must be declared as halfword binary integers, which in COBOL is `PIC S9(4) COMP` (or equivalently `PIC S9(4) BINARY` or `PIC S9(4) COMP-4` depending on the compiler).

Question 13 — Fill in the Blank

To execute a dynamically constructed SQL statement that does not return a result set, you use EXEC SQL ________ :WS-SQL-STMT END-EXEC.

Answer **EXECUTE IMMEDIATE** `EXECUTE IMMEDIATE` prepares and executes a dynamic SQL statement in a single step. It is suitable for DDL and DML statements that do not return rows.

Question 14 — Multiple Choice

Which of the following best describes the purpose of the WITH HOLD option on a cursor?

A. It prevents other programs from accessing the same table. B. It keeps the cursor position open after a COMMIT. C. It locks the fetched rows until the cursor is closed. D. It allows the cursor to be shared across programs in a transaction.

Answer **B. It keeps the cursor position open after a COMMIT.** By default, all cursors are closed when a COMMIT is issued. The `WITH HOLD` option overrides this behavior, allowing the cursor to remain open and positioned after a COMMIT. This is essential in batch programs that commit periodically to release locks.

Question 15 — Short Answer

Describe the complete compile-and-bind process for a COBOL-DB2 program, listing each step in order.

Answer 1. **Precompile** — The DB2 precompiler processes the COBOL source, replacing `EXEC SQL` statements with CALL statements and extracting SQL into a DBRM. 2. **Compile** — The standard COBOL compiler compiles the modified source into an object module. 3. **Link-edit** — The linkage editor combines the object module with the DB2 language interface module to produce a load module. 4. **Bind** — The DB2 BIND utility processes the DBRM, optimizes the SQL access paths, and stores the resulting package (or plan) in the DB2 catalog. All four steps must be performed in sequence whenever the program's SQL statements change.

Question 16 — True/False

A SELECT INTO statement that returns more than one row will set SQLCODE to +100.

Answer **False.** If a `SELECT INTO` returns more than one row, DB2 sets SQLCODE to **-811**, indicating that the subquery or SELECT INTO returned more than one row. SQLCODE +100 indicates no rows found, not multiple rows.

Question 17 — Multiple Choice

In the SQLCA, what does a value of 'W' in SQLWARN0 indicate?

A. A fatal error occurred. B. At least one of the other SQLWARN flags is set. C. The statement was executed successfully with no warnings. D. The connection to DB2 was lost.

Answer **B. At least one of the other SQLWARN flags is set.** `SQLWARN0` is a summary warning flag. When it contains 'W', it means that one or more of the other SQLWARN indicators (`SQLWARN1` through `SQLWARN10`) have been set, signaling conditions such as string truncation (`SQLWARN1`) or null value elimination in an aggregate function (`SQLWARN2`).

Question 18 — Fill in the Blank

The DB2 utility that generates COBOL host variable declarations and a DECLARE TABLE statement from an existing table definition is called __.

Answer **DCLGEN** (Declarations Generator) DCLGEN reads the DB2 catalog for a specified table and produces a COBOL copybook containing the host variable group item and an SQL DECLARE TABLE statement.

Question 19 — Short Answer

A COBOL-DB2 program needs to process millions of rows from the TRANSACTIONS table in a batch run. Explain why committing periodically (e.g., every 1000 rows) is important and what cursor option is required to support this.

Answer Committing periodically is important for three reasons: (1) **Lock management** — without periodic commits, the program accumulates locks on every accessed row, which can exhaust the lock table and escalate to table-level locks, blocking other applications. (2) **Recovery** — if the program fails, DB2 must roll back all uncommitted work; a long-running unit of work leads to prolonged recovery. (3) **Log space** — uncommitted changes consume DB2 log space, and very large units of work can fill the active log. To support periodic commits while continuing to process through a cursor, the cursor must be declared with the `WITH HOLD` option. Without `WITH HOLD`, the cursor would be closed at each COMMIT, and the program would lose its position.

Question 20 — Multiple Choice

SQLCODE -911 with reason code 68 indicates:

A. A deadlock. B. A lock timeout. C. An authorization failure. D. A log-full condition.

Answer **B. A lock timeout.** SQLCODE -911 indicates a rollback due to a lock contention issue. Reason code 68 specifically means a lock timeout occurred (the program waited longer than the `IRLMRWT` or resource timeout interval). Reason code 2 would indicate a deadlock.

Question 21 — True/False

When using dynamic SQL with PREPARE and EXECUTE, the prepared statement persists across COMMIT boundaries within the same program execution.

Answer **True.** A prepared statement remains valid for the duration of the application's thread (or connection). A COMMIT does not invalidate a prepared statement. However, some DDL changes to referenced objects may invalidate it.

Question 22 — Fill in the Blank

To add FOR FETCH ONLY to a cursor declaration is an optimization hint that tells DB2 the cursor will not be used for __ updates or deletes.

Answer **positioned** `FOR FETCH ONLY` (or `FOR READ ONLY`) informs DB2 that no `UPDATE ... WHERE CURRENT OF` or `DELETE ... WHERE CURRENT OF` statements will be issued against this cursor. This allows DB2 to use less restrictive locking and potentially use block fetch for improved performance.

Question 23 — Short Answer

Explain the difference between a DBRM, a package, and a plan in DB2.

Answer - **DBRM (Database Request Module):** The output of the DB2 precompiler containing the extracted SQL statements from a single COBOL source file. It is a sequential data set used as input to the BIND process. - **Package:** The result of binding a single DBRM. It contains the optimized access paths for the SQL statements from one program. Packages can be rebound independently without affecting other programs. - **Plan:** A collection that references one or more packages (via a package list). An application specifies a plan name when connecting to DB2. The plan acts as the entry point that directs DB2 to the appropriate packages at runtime.

Question 24 — Multiple Choice

Which of the following SQL statements is NOT valid inside a COBOL program using static embedded SQL?

A. EXEC SQL SELECT ... INTO ... END-EXEC B. EXEC SQL CREATE TABLE ... END-EXEC C. EXEC SQL UPDATE ... END-EXEC D. EXEC SQL GRANT SELECT ON ... END-EXEC

Answer **D. `EXEC SQL GRANT SELECT ON ... END-EXEC`** `GRANT` and `REVOKE` are authorization statements that cannot be executed as static SQL in a COBOL program. They must be issued through dynamic SQL (`EXECUTE IMMEDIATE`) or through interactive SQL tools like SPUFI or DSNTEP2. DDL statements like `CREATE TABLE` can be embedded statically, though this is uncommon in practice.

Question 25 — True/False

If a COBOL-DB2 program issues EXEC SQL ROLLBACK END-EXEC, all cursors (including those declared WITH HOLD) are closed.

Answer **True.** Unlike COMMIT, a ROLLBACK closes all open cursors regardless of whether they were declared `WITH HOLD`. The `WITH HOLD` option only preserves cursor position across COMMIT operations, not ROLLBACK operations.

Question 26 — Short Answer

A developer wants to handle NULL values when inserting into a table. The column MIDDLE_NAME is nullable. Show the COBOL code needed to insert a NULL value using an indicator variable.

Answer 
       WORKING-STORAGE SECTION.
       EXEC SQL BEGIN DECLARE SECTION END-EXEC.
       01  WS-MIDDLE-NAME     PIC X(30).
       01  WS-MID-IND         PIC S9(4) COMP.
       EXEC SQL END DECLARE SECTION END-EXEC.

       PROCEDURE DIVISION.
      * Set indicator to -1 to signal NULL
           MOVE -1 TO WS-MID-IND.

           EXEC SQL
               INSERT INTO CUSTOMERS
                   (CUST_ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME)
               VALUES
                   (:WS-CUST-ID, :WS-FIRST-NAME,
                    :WS-MIDDLE-NAME :WS-MID-IND,
                    :WS-LAST-NAME)
           END-EXEC.
When `WS-MID-IND` is set to -1, DB2 ignores the content of `WS-MIDDLE-NAME` and inserts a NULL into the `MIDDLE_NAME` column.

Question 27 — Multiple Choice

What is the maximum number of host variables allowed in a single SQL statement in DB2?

A. 127 B. 750 C. 32,767 D. There is no fixed limit; it depends on statement length.

Answer **C. 32,767** DB2 allows up to 32,767 host variable references in a single SQL statement. In practice, the effective limit is usually constrained by the maximum SQL statement length (approximately 2 MB for static SQL).

Question 28 — Fill in the Blank

The SQL clause ORDER BY ACCT_NO FETCH FIRST 10 ROWS ONLY in a cursor declaration limits the result set to __ rows, which can improve performance when only a subset of data is needed.

Answer **10** The `FETCH FIRST n ROWS ONLY` clause limits the number of rows returned by the query. DB2 can use this information to optimize the access path, potentially avoiding a full table scan or sort when an appropriate index exists.

Question 29 — Short Answer

Explain the difference between SQLCODE and SQLSTATE. When would you prefer to use SQLSTATE over SQLCODE?

Answer - **SQLCODE** is a DB2-specific integer return code. Values and meanings can vary between database products (e.g., DB2, Oracle, SQL Server). SQLCODE 0 means success, +100 means not found, and negative values indicate errors. - **SQLSTATE** is a five-character code defined by the SQL standard (ISO/ANSI). It follows a standardized class-code and subclass-code structure (e.g., '00000' for success, '02000' for not found, '23505' for unique constraint violation). You would prefer `SQLSTATE` over `SQLCODE` when writing COBOL programs that must be portable across different database management systems, since SQLSTATE values are standardized while SQLCODE values are vendor-specific.

Question 30 — True/False

The DB2 precompiler validates SQL syntax and checks that referenced tables and columns exist at precompile time.

Answer **False.** The DB2 precompiler validates SQL syntax but does **not** verify that tables and columns exist. Object validation occurs during the **BIND** step, which accesses the DB2 catalog to resolve table names, check column definitions, and determine access paths.