Chapter 32 Quiz: Embedded SQL for COBOL and C

Question 1

What does the DB2 precompiler produce from an embedded SQL COBOL source file?

A) An executable load module and a plan B) A DBRM and a modified COBOL source file with SQL replaced by CALLs C) A package in the DB2 catalog and an object module D) A bind file and a compiled COBOL object

Answer: B

Explanation: The precompiler (DSNHPC) extracts all SQL statements into a DBRM (Database Request Module) and replaces them in the COBOL source with CALL statements to the DB2 language interface. The modified source is then compiled by the standard COBOL compiler.

Question 2

What SQLCODE value indicates that a SELECT INTO statement returned no rows?

A) 0 B) -100 C) +100 D) -811

Answer: C

Explanation: SQLCODE +100 indicates "no row found" for SELECT INTO, or "no more rows" for FETCH. SQLCODE -811 would indicate that SELECT INTO returned more than one row. SQLCODE 0 means success.

Question 3

What is the correct COBOL host variable declaration for a DB2 VARCHAR(200) column?

A) 01 HV-DESC PIC X(200). B) 01 HV-DESC. 49 HV-DESC-LEN PIC S9(4) COMP. 49 HV-DESC-TEXT PIC X(200). C) 01 HV-DESC. 05 HV-DESC-LEN PIC S9(9) COMP. 05 HV-DESC-TEXT PIC X(200). D) 01 HV-DESC PIC X(202).

Answer: B

Explanation: VARCHAR columns require a two-part structure at level 49. The first part is a halfword (PIC S9(4) COMP) containing the actual data length. The second part is the character data. Level 49 is mandatory — other levels will not be recognized as a VARCHAR structure by the precompiler.

Question 4

When an indicator variable has a value of -1 after a FETCH, what does this mean?

A) The column value was truncated B) The column contains NULL C) A conversion error occurred D) The host variable is too small

Answer: B

Explanation: An indicator value of -1 means the column is NULL. The content of the host variable is undefined in this case. A positive indicator value means truncation occurred (the value equals the original length). A value of 0 means the column is not NULL and the data is valid.

Question 5

What is the purpose of the WITH HOLD clause on a cursor declaration?

A) It prevents other users from updating the rows in the result set B) It keeps the cursor open across COMMIT operations C) It holds locks on all rows until the cursor is closed D) It prevents the cursor from being closed by ROLLBACK

Answer: B

Explanation: WITHOUT WITH HOLD, a COMMIT closes all open cursors. WITH HOLD keeps the cursor open and positioned after a COMMIT, allowing the program to continue fetching from where it left off. This is essential for batch programs that commit periodically. Note: ROLLBACK always closes cursors, even WITH HOLD.

Question 6

What is the difference between BIND PLAN and BIND PACKAGE?

A) Plans are for COBOL programs; packages are for C programs B) Plans contain optimized SQL; packages contain unoptimized SQL C) A package is bound from a single DBRM; a plan references packages through a package list D) Plans are for batch; packages are for online

Answer: C

Explanation: A package is the binding unit for a single DBRM (one program's SQL). A plan references a collection of packages through a PKLIST. This modular approach means you can rebind a single package when one program changes without affecting the entire plan.

Question 7

SQLCODE -818 indicates a "timestamp mismatch." What causes this error?

A) The system clock is wrong B) A TIMESTAMP column has an invalid value C) The DBRM used to create the load module does not match the DBRM used to bind the package D) The program was compiled at a different time than it was bound

Answer: C

Explanation: When the precompiler creates a DBRM, it embeds a consistency token (timestamp). This token is also embedded in the modified source code. If the program is reprecompiled and recompiled but not rebound (or vice versa), the tokens will not match, resulting in SQLCODE -818. The fix is to ensure the same DBRM is used for both the compile and bind steps.

Question 8

Which BIND ISOLATION level provides the least locking overhead for a read-only batch program?

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

Answer: D

Explanation: UR (Uncommitted Read) acquires no row-level read locks, providing the least overhead. However, it can read uncommitted data (dirty reads). For a read-only reporting program where absolute consistency is not required, UR provides maximum throughput. If consistency is required, CS or RS would be appropriate despite higher overhead.

Question 9

In the EXEC SQL WHENEVER SQLERROR GO TO ERROR-PARA END-EXEC directive, what is the scope of this directive?

A) Only the next SQL statement B) The current COBOL paragraph C) All subsequent SQL statements in the source code until another WHENEVER SQLERROR is coded D) The entire program

Answer: C

Explanation: WHENEVER is a precompiler directive that affects all subsequent SQL statements in the physical source code until overridden by another WHENEVER directive for the same condition. It is not scoped by COBOL paragraph or section — it is purely positional in the source file. This can cause unexpected behavior if not managed carefully.

Question 10

What is the primary performance advantage of multi-row FETCH over single-row FETCH?

A) It uses less memory B) It reduces the number of calls between the application and DB2 C) It allows DB2 to use parallel query processing D) It bypasses the buffer pool

Answer: B

Explanation: Each call from the application to DB2 has fixed overhead (context switching, thread management, etc.). Multi-row FETCH retrieves N rows in a single call instead of one row per call. For a result set of 100,000 rows with a rowset size of 100, you make approximately 1,000 FETCH calls instead of 100,000 — a 99% reduction in call overhead.

Question 11

What does SQLERRD(3) contain after a successful UPDATE statement?

A) The SQL statement number B) The estimated cost of the statement C) The number of rows affected by the UPDATE D) The number of locks held

Answer: C

Explanation: After INSERT, UPDATE, or DELETE, SQLERRD(3) contains the count of rows affected. After a FETCH, it contains the number of rows fetched. This is useful for verifying that your statement affected the expected number of rows.

Question 12

You have a COBOL program with EXEC SQL WHENEVER NOT FOUND GO TO NO-DATA END-EXEC at line 100, and EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC at line 200. A FETCH at line 150 returns SQLCODE +100. What happens?

A) The program continues to the next statement after the FETCH B) The program branches to NO-DATA C) The program abends D) The FETCH is retried

Answer: B

Explanation: The WHENEVER at line 100 establishes GO TO NO-DATA for NOT FOUND. This applies to all SQL statements between line 100 and line 200 (where it is overridden). Since the FETCH at line 150 falls in this range, SQLCODE +100 causes a branch to NO-DATA.

Question 13

Which BIND option controls the default qualifier for unqualified table names in SQL statements?

A) VALIDATE B) QUALIFIER C) DYNAMICRULES D) CURRENTDATA

Answer: B

Explanation: The QUALIFIER option sets the default schema qualifier for unqualified table references in the SQL. This is essential for promoting programs across environments — the same COBOL code with FROM ACCOUNTS can reference DEV.ACCOUNTS in development and PROD.ACCOUNTS in production, depending on the QUALIFIER used at bind time.

Question 14

A cursor is declared FOR UPDATE OF BALANCE. What type of lock does DB2 hold on the current row before the UPDATE is executed?

A) No lock B) Share lock C) Update lock D) Exclusive lock

Answer: C

Explanation: For a cursor declared FOR UPDATE OF, DB2 acquires an update (U) lock on each row as it is fetched. The U lock is compatible with share (S) locks but not with other U or exclusive (X) locks. When the positioned UPDATE executes, the U lock is promoted to an X lock. This pattern prevents conversion deadlocks.

Question 15

What happens when you execute EXEC SQL ROLLBACK END-EXEC and you have an open cursor that was NOT declared WITH HOLD?

A) The cursor remains open and positioned B) The cursor is closed C) The cursor is repositioned to the first row D) An error occurs

Answer: B

Explanation: Both COMMIT and ROLLBACK close all cursors that were not declared WITH HOLD. After a ROLLBACK, all changes made since the last commit point are undone, and non-WITH-HOLD cursors are closed. Even WITH HOLD cursors are closed on ROLLBACK (WITH HOLD only survives COMMIT, not ROLLBACK).