Quiz: Advanced DB2 Programming
Multiple Choice
1. Which form of dynamic SQL should you use when executing a non-SELECT statement exactly once at runtime?
a) PREPARE and EXECUTE b) EXECUTE IMMEDIATE c) PREPARE with CURSOR d) DESCRIBE
2. What is the primary advantage of using parameter markers (?) instead of string concatenation in dynamic SQL?
a) Parameter markers make the SQL shorter b) Parameter markers allow DB2 to cache the access plan c) Parameter markers prevent SQL injection and enable plan reuse d) Parameter markers are required by the DB2 precompiler
3. What SQLCODE is returned when you SELECT a nullable column into a host variable WITHOUT an indicator variable and the column value is NULL?
a) +100 b) -305 c) -811 d) 0 (with the host variable set to spaces/zeros)
4. Which indicator variable value means the column contains NULL?
a) 0 b) -1 c) -2 d) 1
5. What keyword must you add to a cursor declaration to enable FETCH PRIOR and FETCH FIRST?
a) DYNAMIC b) BIDIRECTIONAL c) SCROLL d) RANDOM ACCESS
6. In multi-row FETCH, which SQLERRD field contains the number of rows actually fetched?
a) SQLERRD(1) b) SQLERRD(2) c) SQLERRD(3) d) SQLERRD(5)
7. What happens to a cursor declared WITHOUT the WITH HOLD option when a COMMIT is issued?
a) The cursor remains open at its current position b) The cursor is closed c) The cursor resets to the first row d) DB2 returns SQLCODE -501
8. Which isolation level allows dirty reads (reading uncommitted changes from other transactions)?
a) Cursor Stability (CS) b) Read Stability (RS) c) Uncommitted Read (UR) d) Repeatable Read (RR)
9. What does SQLCODE -911 indicate?
a) Authorization failure b) Deadlock or timeout — automatic rollback occurred c) Table not found d) Syntax error in SQL statement
10. In a composite index on columns (A, B, C), what MATCHCOLS value results from a WHERE clause that filters on A and C but not B?
a) 0 b) 1 c) 2 d) 3
11. What does ACCESSTYPE = 'R' in the PLAN_TABLE indicate?
a) Row-level access via index b) Tablespace scan (full table read) c) RID list access d) Result set from a subquery
12. Which stored procedure parameter mode allows data to flow both from the caller to the procedure and back?
a) IN b) OUT c) INOUT d) BIDIRECTIONAL
13. What SQLCODE indicates that a stored procedure returned one or more result sets?
a) 0 b) +100 c) +466 d) -471
14. What is the correct order of operations when using a dynamic SQL cursor?
a) DECLARE, PREPARE, OPEN, FETCH, CLOSE b) PREPARE, DECLARE, OPEN, FETCH, CLOSE c) PREPARE, OPEN, DECLARE, FETCH, CLOSE d) Either (a) or (b) — DECLARE is processed at precompile time
15. Why is the WHERE 1=1 pattern commonly used in dynamic SQL construction?
a) It improves query performance b) It is required by the DB2 precompiler c) It simplifies appending AND conditions dynamically d) It prevents SQL injection
True or False
16. PREPARE should be placed inside a loop when executing the same dynamic SQL with different parameter values. ___
17. DCLGEN output should be stored in a COBOL COPY library and included with the COPY statement. ___
18. An indicator variable value of -2 means the retrieved value was truncated. ___
19. INSENSITIVE SCROLL cursors reflect changes made by other transactions after the cursor is opened. ___
20. Lock escalation occurs when a program acquires too many individual locks and DB2 promotes them to a table-level lock. ___
Short Answer
21. Explain why EXECUTE IMMEDIATE is inappropriate for a SQL statement that will be executed 1,000 times with different parameter values. What should you use instead?
22. A batch program processes 1 million rows and issues COMMIT after every row. Another version commits after every 10,000 rows. A third version commits after every 500 rows. Discuss the trade-offs of each approach in terms of lock duration, restart granularity, and log space.
23. Describe the deadlock scenario that can occur when two programs simultaneously execute fund transfers in opposite directions (Program 1: A→B, Program 2: B→A). How does lock ordering prevent this?
24. You are reviewing a program that uses EXEC SQL SELECT ... INTO to look up provider information inside a cursor loop that processes 500,000 claims. The lookup SELECT is executed once per claim row. Describe two approaches to eliminate this N+1 query problem.
25. Explain the difference between EXEC SQL INCLUDE and COPY when incorporating DCLGEN members. Why does the distinction matter?
Answer Key
1. b) EXECUTE IMMEDIATE — for one-time non-SELECT statements.
2. c) Parameter markers prevent SQL injection and enable plan reuse.
3. b) -305 — DB2 cannot store NULL in a host variable without an indicator.
4. b) -1
5. c) SCROLL
6. c) SQLERRD(3) — this field contains the row count for FETCH, INSERT, UPDATE, and DELETE.
7. b) The cursor is closed. Use WITH HOLD to keep it open across COMMITs.
8. c) Uncommitted Read (UR) — reads data regardless of locks.
9. b) Deadlock or timeout — DB2 automatically rolls back the unit of work.
10. b) 1 — only column A matches because DB2 requires contiguous left-to-right matching.
11. b) Tablespace scan — the entire table is read sequentially.
12. c) INOUT — data flows both directions.
13. c) +466 — indicates result sets are available.
14. d) Either (a) or (b) — DECLARE is a precompiler directive and does not generate executable code, so its position relative to PREPARE does not matter at runtime. However, most shops place DECLARE before PREPARE for readability.
15. c) It simplifies appending AND conditions dynamically — you can always append AND condition without checking if WHERE already exists.
16. False — PREPARE once outside the loop, EXECUTE inside the loop with different USING values.
17. False — DCLGEN should be included with EXEC SQL INCLUDE, which is processed by the DB2 precompiler, not the COBOL COPY statement.
18. True — indicator value -2 signals truncation during retrieval.
19. False — INSENSITIVE cursors create a snapshot; they do NOT reflect subsequent changes.
20. True — lock escalation promotes page/row locks to table locks when thresholds are exceeded.
21. EXECUTE IMMEDIATE re-prepares (re-optimizes) the statement every time it executes, incurring full optimizer overhead 1,000 times. Instead, use PREPARE once to create the access plan, then EXECUTE 1,000 times with different USING parameters. This reduces optimizer overhead by 99.9%.
22. Commit-per-row: Maximum lock release frequency, finest restart granularity, but enormous log overhead and poor throughput (each COMMIT forces a log write). Commit-per-10,000: Good throughput, minimal log overhead, but holds locks for up to 10,000 rows simultaneously (risk of lock escalation) and up to 10,000 rows must be reprocessed on restart. Commit-per-500: A common sweet spot — reasonable lock duration, manageable restart granularity, and acceptable log overhead. The optimal frequency depends on the specific workload, table size, and concurrent access patterns.
23. Program 1 locks account A (debit), then tries to lock account B (credit). Simultaneously, Program 2 locks account B (debit), then tries to lock account A (credit). Each program holds a lock the other needs — deadlock. Lock ordering prevents this by requiring both programs to lock accounts in canonical order (e.g., lower account number first). Program 1 (A→B): locks A then B. Program 2 (B→A): also locks A first, then B. Now Program 2 waits for Program 1 to release A — no deadlock, just a wait.
24. Approach 1: JOIN — include the PROVIDER table in the cursor's SELECT with a JOIN on PROVIDER_ID. This retrieves provider data alongside claim data in a single pass. Approach 2: Load PROVIDER data into a COBOL internal table (working-storage array) before the cursor loop, then look up providers in memory using a SEARCH or binary lookup. The JOIN approach is usually better unless the provider table is very large and infrequently matched.
25. EXEC SQL INCLUDE is processed by the DB2 precompiler, which runs before the COBOL compiler. It can process SQL-specific content like table declarations. COPY is processed by the COBOL compiler, which does not understand SQL syntax. Since DCLGEN output contains EXEC SQL DECLARE TABLE statements, it must be processed by the precompiler via EXEC SQL INCLUDE, not by the COBOL COPY verb.