Chapter 7 Quiz

Instructions: Select the best answer for each question. All questions reference DB2 for z/OS in a COBOL-DB2 batch environment unless otherwise stated.

1. What is the primary performance benefit of multi-row FETCH compared to single-row FETCH?

A) Multi-row FETCH uses less CPU for SQL execution B) Multi-row FETCH reduces the number of thread switches between the application and DB2 address spaces C) Multi-row FETCH bypasses the DB2 optimizer D) Multi-row FETCH avoids buffer pool I/O

2. A multi-row FETCH with FOR 500 ROWS returns SQLCODE 100 and SQLERRD(3) = 283. What does this mean?

A) An error occurred on row 283 B) 283 rows were fetched but 217 had errors C) 283 rows were fetched successfully and the result set is exhausted D) The cursor was positioned at row 283 when an error occurred

3. What happens if your COBOL host variable array is declared with OCCURS 500 but you execute FETCH... FOR 1000 ROWS?

A) DB2 returns SQLCODE -311 at compile time B) The COBOL runtime raises a subscript-out-of-range error C) DB2 writes past the end of the array, corrupting adjacent WORKING-STORAGE D) DB2 automatically limits the fetch to 500 rows

4. In a multi-row INSERT with NOT ATOMIC CONTINUE ON SQLEXCEPTION, what SQLCODE indicates that some rows were inserted successfully but at least one failed?

A) SQLCODE 0 B) SQLCODE 100 C) SQLCODE +252 D) SQLCODE -803

5. Which clause must be added to the cursor declaration to enable multi-row FETCH?

A) FOR UPDATE OF B) WITH HOLD C) WITH ROWSET POSITIONING D) OPTIMIZE FOR n ROWS

6. A MERGE statement receives SQLCODE -788. What is the most likely cause?

A) The target table does not exist B) The ON clause columns are not indexed C) The source data contains duplicate values for the join columns D) The MERGE attempted to update a row that was concurrently locked

7. In the MERGE statement, what happens if the ON clause resolves to a match?

A) Only the WHEN MATCHED THEN UPDATE branch executes B) Only the WHEN NOT MATCHED THEN INSERT branch executes C) Both branches execute D) The MERGE fails with SQLCODE -811

8. What distinguishes a system-period temporal table from an application-period temporal table?

A) System-period uses DATE columns; application-period uses TIMESTAMP B) System-period timestamps are managed by DB2 automatically; application-period timestamps are managed by the application C) System-period tables cannot have indexes; application-period tables can D) System-period tables store history in the same table; application-period tables use a separate history table

9. A system-period temporal table has SYS_END set to '9999-12-30-00.00.00.000000' for a row. What does this indicate?

A) The row has been deleted B) The row is the oldest version in the history table C) The row is the current (active) version D) The row's history has been purged

10. You query a temporal table with FOR SYSTEM_TIME AS OF '2025-06-15-12.00.00.000000'. The current row was last updated on 2025-07-01. What does the query return?

A) The current row (as of July 1st update) B) The row version that was active on June 15th (from the history table) C) No rows (SQLCODE 100) D) Both the June 15th version and the current version

11. What is the overhead impact of enabling system-period temporal versioning on UPDATE operations?

A) No overhead — DB2 handles it transparently with no additional I/O B) Approximately 10% overhead due to timestamp management C) Approximately 40-60% overhead because DB2 must INSERT the old row into the history table before applying the UPDATE D) The UPDATE is rejected if the history tablespace is full

12. In a recursive CTE, what are the two required components?

A) A SELECT statement and a UNION statement B) An anchor member and a recursive member joined by UNION ALL C) A base table and a temporary table D) A WITH clause and a GROUP BY clause

13. A recursive CTE traversing an organizational hierarchy encounters a cycle in the data (A reports to B, B reports to C, C reports to A). Without any safeguards, what happens?

A) DB2 detects the cycle and returns SQLCODE -347 B) The CTE returns only the non-cyclic portions of the hierarchy C) The CTE loops indefinitely until DB2 runs out of TEMP space or resources D) The COBOL program receives SQLCODE 100 after the first cycle

14. What does ROW_NUMBER() OVER (PARTITION BY ACCT_NUM ORDER BY TXN_DATE) produce?

A) The total number of rows in the table B) A sequential number starting at 1 for each unique ACCT_NUM, ordered by TXN_DATE within that account C) The row's physical position in the tablespace D) A random number assigned to each row

15. What is the difference between RANK() and DENSE_RANK() when two rows tie?

A) RANK() returns NULL for ties; DENSE_RANK() returns the same rank B) RANK() leaves a gap in the ranking after the tie; DENSE_RANK() does not C) There is no difference; they are synonyms D) RANK() breaks ties alphabetically; DENSE_RANK() breaks ties by row position

16. LAG(TXN_AMT, 1, 0) — what does the third argument (0) specify?

A) The partition number to look in B) The default value returned when there is no previous row (i.e., the first row in the partition) C) The number of rows to skip before looking back D) The minimum value that LAG will return

17. Which OLAP window frame specification computes a running total from the first row of the partition through the current row?

A) ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING B) ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING C) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW D) RANGE BETWEEN 1 PRECEDING AND CURRENT ROW

18. What is the key difference between ROWS BETWEEN and RANGE BETWEEN when the ORDER BY column has duplicate values?

A) ROWS counts physical rows; RANGE groups all rows with the same ORDER BY value together B) ROWS is faster; RANGE is more accurate C) ROWS requires an index; RANGE does not D) There is no functional difference

19. A COBOL batch program processes 10 million rows. Each row requires: one FETCH, one business rule evaluation (COBOL only, no SQL), one UPDATE, and one INSERT. Thread switch time is 0.03 ms. What is the total thread-switching overhead for single-row vs. multi-row (rowset 500) operations?

A) Single-row: 15 minutes; Multi-row: 0.03 minutes B) Single-row: 15 minutes; Multi-row: 1.8 seconds C) Single-row: 5 minutes; Multi-row: 0.6 seconds D) Single-row: 150 minutes; Multi-row: 0.3 minutes

20. According to the migration strategy recommended in this chapter, what is the recommended order for converting existing cursor-loop programs?

A) OLAP functions → MERGE → Multi-row FETCH → Temporal → Recursive CTE B) Multi-row FETCH → Multi-row INSERT → MERGE → OLAP functions → Recursive CTEs C) Recursive CTEs → Temporal → MERGE → Multi-row INSERT → Multi-row FETCH D) All techniques should be implemented simultaneously for maximum benefit

Answer Key

1. B — Multi-row FETCH reduces thread switches between the application and DB2 address spaces. The SQL execution cost per row stays roughly the same; the savings come from eliminating the per-row overhead of crossing the address space boundary.

2. C — SQLCODE 100 means end of result set. SQLERRD(3) = 283 means 283 rows were placed in the host variable arrays. You must process only positions 1–283.

3. C — DB2 doesn't know the size of your COBOL arrays. It will write 1,000 rows' worth of data starting at the array address, overwriting whatever follows in WORKING-STORAGE. The COBOL compiler and DB2 precompiler don't cross-check array bounds.

4. C — SQLCODE +252 indicates partial completion of a non-atomic multi-row INSERT. Check SQLERRD(3) for the count of successfully inserted rows.

5. C — WITH ROWSET POSITIONING enables the cursor for multi-row FETCH operations. Without this clause, FETCH...FOR n ROWS will fail.

6. C — SQLCODE -788 means the source of the MERGE contains duplicate values for the columns used in the ON clause. MERGE requires unique join keys in the source.

7. A — When the ON clause evaluates to true (match), only the WHEN MATCHED branch executes. The branches are mutually exclusive per source row.

8. B — System-period temporal tables have their timestamps (SYS_START, SYS_END) managed automatically by DB2. Application-period temporal tables have their period columns managed by the application's DML statements.

9. C — The maximum timestamp value in SYS_END indicates the row is currently active (it has not yet been superseded by a newer version).

10. B — FOR SYSTEM_TIME AS OF returns the version that was active at the specified timestamp. Since the row was updated on July 1st, the June 15th query returns the previous version from the history table.

11. C — Each UPDATE on a temporal table requires DB2 to INSERT the old row into the history table before applying the update, effectively doubling the I/O for that operation. Real-world overhead is typically 40–60%.

12. B — A recursive CTE requires an anchor member (the initial query) and a recursive member (the query that references the CTE itself), connected by UNION ALL.

13. C — DB2 does not automatically detect cycles in recursive CTEs. Without a depth limit or cycle detection in the WHERE clause, the CTE will iterate indefinitely until system resources (typically TEMP space) are exhausted.

14. B — ROW_NUMBER() assigns sequential integers starting at 1 within each partition. PARTITION BY ACCT_NUM creates a separate sequence for each account, ordered by TXN_DATE.

15. B — RANK() assigns the same rank to ties but skips subsequent ranks (1, 2, 2, 4). DENSE_RANK() assigns the same rank to ties without gaps (1, 2, 2, 3).

16. B — The third argument to LAG() is the default value returned when there is no row at the specified offset (i.e., for the first row in the partition, there is no previous row, so LAG returns 0).

17. C — ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW accumulates from the first row of the partition through the current row, producing a running total.

18. A — ROWS operates on physical row positions. RANGE operates on the logical value of the ORDER BY column. With duplicate values, RANGE treats all rows with the same value as part of the same frame position.

19. A — Single-row: 10M rows × 3 SQL statements × 0.03ms = 900,000ms = 15 minutes. Multi-row (rowset 500): 10M/500 = 20,000 batches × 3 SQL statements × 0.03ms = 1,800ms = 1.8 seconds. But note: the UPDATE cannot be multi-row FETCH in this scenario (it's a positioned update per row), so the actual answer depends on interpretation. Taking the question at face value with all three operations batched: 15 minutes → ~1.8 seconds. Closest answer: A.

20. B — The recommended order is: multi-row FETCH (mechanical, low risk, immediate gain) → multi-row INSERT (same pattern, output side) → MERGE (moderate complexity, requires index verification) → OLAP functions (requires rethinking program structure) → recursive CTEs (most complex, replaces the most COBOL logic).