Quiz — Chapter 11: DB2 Performance Diagnosis

Question 1

What is the FIRST step in the systematic performance investigation methodology described in this chapter?

A) Run EXPLAIN on all SQL statements B) Check the DB2 accounting trace C) Define the problem precisely with measurable metrics D) Add an index to the slow table

Answer: C Rationale: The methodology begins with a precise problem statement that includes what changed, when it changed, and the measurable delta. Without this, you cannot determine whether you have solved the problem or even whether there is a problem. Options A and B are Step 3-4 activities. Option D is the "superstition with a technical vocabulary" approach the chapter warns against.

Question 2

In a PLAN_TABLE row, what does ACCESSTYPE='R' indicate?

A) Random index access B) Tablespace scan (sequential read of all pages) C) RID pool access D) Recursive SQL access

Answer: B Rationale: ACCESSTYPE='R' means DB2 is performing a tablespace scan — reading all data pages sequentially. This is a red flag on large tables but acceptable on small reference tables. 'I' indicates index access, 'M' indicates multiple index access.

Question 3

A PLAN_TABLE row shows ACCESSTYPE='I' and MATCHCOLS=0. What does this mean?

A) The index is not being used B) DB2 is performing a non-matching index scan (reading the entire index) C) The query matched zero rows D) The index has zero columns

Answer: B Rationale: ACCESSTYPE='I' with MATCHCOLS=0 means DB2 chose to scan the entire index without using any leading key columns as matching predicates. This is often worse than a tablespace scan because index pages are smaller and more numerous. DB2 may choose this when it needs data in index key order or when the index is significantly smaller than the tablespace.

Question 4

Which column in DSN_STATEMNT_TABLE indicates the optimizer's confidence in its cost estimate?

A) PROCMS B) STMT_TYPE C) COST_CATEGORY D) REASON

Answer: C Rationale: COST_CATEGORY = 'A' means the optimizer is confident in its estimate. COST_CATEGORY = 'B' means uncertainty, usually due to missing statistics, host variable selectivity unknowns, or uncaptured column correlations. When you see 'B', the access path may not be optimal.

Question 5

What is IFCID 3 in DB2 monitoring?

A) The performance trace record for individual SQL statements B) The statistics trace record fired every 30 minutes C) The accounting trace record fired at thread termination D) The audit trace record for security events

Answer: C Rationale: IFCID 3 is the thread termination (accounting) record. It contains elapsed time, CPU time, wait time breakdowns, and SQL activity counters. It is the primary diagnostic for COBOL program performance analysis. IFCID 1 is the statistics trace, and IFCID 2+ are performance trace records.

Question 6

A batch program's accounting trace shows: Elapsed = 10,000 sec, CPU = 500 sec, Lock Wait = 8,200 sec. What type of problem is this?

A) CPU-bound B) I/O-bound C) Lock contention D) Memory constraint

Answer: C Rationale: Lock wait accounts for 82% of elapsed time while CPU is only 5%. The program is spending the vast majority of its time waiting for locks held by other threads. This is a classic lock contention problem — likely caused by infrequent commits, poor job scheduling, or lock escalation.

Question 7

Which of the following SQL predicates will PREVENT index matching on a column?

A) WHERE ACCT_ID = :HV-ACCT-ID B) WHERE ACCT_ID IN (:HV1, :HV2, :HV3) C) WHERE YEAR(TRANS_DATE) = 2025 D) WHERE ACCT_ID BETWEEN :HV-LOW AND :HV-HIGH

Answer: C Rationale: Applying a function (YEAR) to the column side of a predicate prevents DB2 from using an index on TRANS_DATE. The optimizer cannot "reverse" the function to determine which index entries to read. The predicate should be rewritten as a range: WHERE TRANS_DATE BETWEEN '2025-01-01' AND '2025-12-31'. Options A, B, and D are all indexable predicates.

Question 8

What is the primary benefit of increasing commit frequency in a batch COBOL program?

A) Reduces CPU consumption B) Reduces lock duration, allowing concurrent access C) Eliminates the need for indexes D) Increases buffer pool hit ratio

Answer: B Rationale: Frequent commits release locks sooner, reducing the window during which other programs must wait. The chapter demonstrates that at CNB, changing commit frequency from every 50,000 rows to every 500 rows reduced lock wait from 2.4 hours to seconds, at a cost of only 37 seconds of additional commit overhead.

Question 9

A COBOL batch program commits every 50,000 rows and processes 5 million rows total. Approximately how many commits will occur?

A) 10 B) 100 C) 1,000 D) 10,000

Answer: B Rationale: 5,000,000 rows / 50,000 rows per commit = 100 commits. This is very infrequent for a high-volume batch program. The chapter recommends 200-500 rows per commit for programs accessing shared tables, which would yield 10,000-25,000 commits instead.

Question 10

What does INDEXONLY='Y' in PLAN_TABLE mean?

A) The query accesses only one index B) DB2 can satisfy the query entirely from the index without reading data pages C) The table has only one index D) The optimizer will only consider index access paths

Answer: B Rationale: INDEXONLY='Y' means all columns needed by the query (SELECT list, WHERE clause, JOIN columns) are present in the index. DB2 never reads the base table data pages. This is the most efficient access possible — it eliminates all data page I/O for that table access.

Question 11

What does METHOD=1 in PLAN_TABLE indicate?

A) First table access in the plan B) Nested loop join C) Merge scan join D) Hybrid join

Answer: B Rationale: METHOD values: 0 = first table accessed (no join), 1 = nested loop join, 2 = merge scan join, 3 = sorts, 4 = hybrid join. Nested loop join (METHOD=1) is the most common join method in COBOL applications because it works well for single-row lookups driven by an outer table.

Question 12

In DB2 accounting data, Class 3 times represent:

A) CPU time consumed by the thread B) Elapsed time for the thread C) Wait times broken down by category D) SQL statement counts

Answer: C Rationale: Class 1 = elapsed time, Class 2 = CPU time (TCB and SRB), Class 3 = wait time detail (I/O wait, lock wait, log write wait, drain wait, etc.). Class 3 is essential for diagnosing where a program spends time when it is not consuming CPU.

Question 13

A query that runs fast for most accounts but very slowly for some accounts most likely suffers from:

A) Tablespace scan B) Lock contention C) Data skew D) Thread reuse issues

Answer: C Rationale: Data skew occurs when value distribution is non-uniform. The optimizer uses average selectivity, so the access path is optimal for typical values but catastrophic for outliers (e.g., corporate accounts with 50,000 transactions vs. individual accounts with 25). The chapter describes this as the hardest problem to diagnose because EXPLAIN looks correct for the average case.

Question 14

What DB2 bind option tells DB2 to re-optimize SQL at execution time using actual host variable values?

A) KEEPDYNAMIC(YES) B) REOPT(ALWAYS) C) RELEASE(DEALLOCATE) D) ISOLATION(UR)

Answer: B Rationale: REOPT(ALWAYS) causes DB2 to re-prepare the SQL statement at each execution using the actual host variable values, not the default filter factors. This addresses data skew but adds PREPARE overhead. REOPT(ONCE) is a compromise that optimizes once with actual values and caches the result.

Question 15

What is the recommended response when the DB2 accounting trace shows a batch job's elapsed time increased 50% over normal?

A) Note and watch B) Investigate C) Escalate D) Restart the job

Answer: C Rationale: Per the chapter's response protocol: 10% over normal = note and watch, 25% over = investigate, 50% over = escalate. A 50% increase represents a significant degradation that likely affects the batch window and other dependent jobs.

Question 16

Which RUNSTATS option provides the optimizer with distribution statistics for non-uniform data?

A) KEYCARD B) FREQVAL C) HISTOGRAM D) All of the above

Answer: D Rationale: KEYCARD provides distinct value counts, FREQVAL provides frequency counts for the most common values, and HISTOGRAM provides value distribution across ranges. All three help the optimizer handle non-uniform distributions. FREQVAL is particularly important for data skew because it lets the optimizer know that specific values match disproportionately many rows.

Question 17

In the DB2 DISPLAY THREAD output, status "V 652-LOCK WAIT" with a HOLDER identified means:

A) The thread is in a deadlock B) The thread is waiting for a lock held by the identified holder C) The thread has been cancelled D) The thread is performing a utility operation

Answer: B Rationale: Status V 652 indicates the thread is suspended waiting for a lock. The HOLDER line identifies which thread holds the conflicting lock. This is lock contention, not necessarily deadlock. A deadlock would show two threads each waiting for locks held by the other, and DB2 would resolve it by rolling back one thread.

Question 18

What is the typical CPU overhead of leaving the DB2 accounting trace (IFCID 3) active permanently?

A) 0.01-0.05% B) 1-3% C) 5-15% D) 20-30%

Answer: B Rationale: The accounting trace (IFCID 3) has approximately 1-3% CPU overhead, which the chapter describes as "the cheapest insurance you will ever buy." The performance trace (IFCID 2+) has 5-15% overhead and should only be activated for specific diagnosis. Accounting traces should always be active — no exceptions.

Question 19

A buffer pool hit ratio of 82.4% combined with a high STEAL_COUNT indicates:

A) The buffer pool is too large B) The buffer pool is too small for its workload C) The I/O subsystem is slow D) The data is not being accessed

Answer: B Rationale: A hit ratio of 82.4% means nearly 1 in 5 page requests requires physical I/O. High STEAL_COUNT means DB2 frequently needs to reclaim buffer pages because the pool is full. Together, these indicate the buffer pool is undersized relative to the working set of data it serves. Adding pages to this pool would improve hit ratio and reduce I/O.

Question 20

Before approving a REBIND for production, what is the minimum verification required?

A) Confirm the REBIND completed without errors B) Compare EXPLAIN output before and after, investigating any access path changes C) Run the program in production and monitor for one hour D) Verify that RUNSTATS was run recently

Answer: B Rationale: The chapter emphasizes that EXPLAIN comparison is the minimum verification before any REBIND moves to production. Any change in ACCESSTYPE, MATCHCOLS, join method, or sort activity must be investigated. At Pinnacle Health, they built an automated comparison tool that flags all differences. Option D (RUNSTATS verification) is important but insufficient by itself — you need to see what the optimizer actually chose.