Chapter 36 Quiz: Capstone — Complete System Review, Stress Test, and Disaster Recovery Drill

Test your mastery of the comprehensive system review process. These questions draw on material from across the entire book, reflecting the integrative nature of the capstone.

Questions

Question 1

During a system architecture review of a DB2 for z/OS data sharing group, you discover that one of three members shows STATUS = 'QUIESCED'. What does this indicate, and what is your recommended action?

A) The member has crashed and needs to be restarted immediately; escalate to z/OS systems programming. B) The member was intentionally quiesced (likely for maintenance) and is not processing work; verify with the operations team whether this is planned. C) The member's coupling facility connection has failed; rebuild the lock structure. D) The member is running but has exhausted its thread pool; increase MAXDBAT.

Question 2

You are reviewing HADR status on the LUW reporting database and see HADR_STATE = 'REMOTE_CATCHUP' with HADR_SYNCMODE = 'NEARSYNC'. Which statement is most accurate?

A) This is a normal operational state; NEARSYNC always operates in REMOTE_CATCHUP. B) The standby is temporarily behind the primary, likely due to network latency or a recent reconnection; it should return to PEER state once caught up. C) This indicates a critical failure; the standby database is corrupt and must be re-initialized. D) REMOTE_CATCHUP means the standby has been promoted and is now the primary.

Question 3

Your schema audit reveals a table with 2.3 million rows that has no primary key. Which of the following is NOT a valid concern about this situation?

A) Replication tools may not function correctly without a unique row identifier. B) Audit and data lineage requirements cannot be fully satisfied. C) The optimizer cannot generate efficient access plans for the table. D) Duplicate rows may exist without detection, violating data integrity.

Question 4

During the stress test, you observe that the buffer pool hit ratio for the OLTP tablespace drops from 99.2% to 87.4%. The root cause is a batch statement generation job performing sequential scans. What is the BEST remediation?

A) Increase the size of the existing buffer pool to accommodate both OLTP and batch workloads. B) Assign the batch workload to a separate buffer pool sized for sequential prefetch, isolating batch I/O from OLTP pages. C) Disable prefetch for the batch workload to reduce buffer pool consumption. D) Schedule the batch job to run only when no OLTP workload is active.

Question 5

An EXPLAIN analysis reveals that the optimizer chose a table scan on TRANSACTION_HISTORY (480 million rows) instead of using an index on (ACCOUNT_ID, TRANSACTION_DATE). The index exists and is valid. What is the MOST LIKELY cause?

A) The index is corrupted and needs to be rebuilt. B) Statistics are stale — the optimizer does not have accurate cardinality information to recognize the index as beneficial. C) The query uses SELECT *, which always forces a table scan. D) DB2 automatically disables indexes on tables larger than 100 million rows.

Question 6

Your security audit finds that an application service account has been granted DBADM authority. The development team says they need it for the application to function. What is the correct response?

A) Accept the finding — if the application needs it, DBADM is appropriate. B) Revoke DBADM and grant only the specific privileges the application actually needs (SELECT, INSERT, UPDATE, DELETE on specific tables, EXECUTE on specific routines). C) Revoke DBADM and grant DATAACCESS instead, which is slightly less powerful. D) Change the audit report to exclude application service accounts.

Question 7

During the disaster recovery drill, the HADR TAKEOVER BY FORCE command completes in 12 seconds. However, the total RTO is measured at 4 minutes and 40 seconds. What accounts for the remaining time?

A) Database crash recovery (replaying uncommitted transactions). B) Failure detection time, operator decision and authorization time, and application reconnection time. C) Backup restoration from tape. D) Rebuilding all indexes on the standby database.

Question 8

You discover three deadlocks during the stress test, all involving the payroll posting process. The deadlock graph shows two sessions acquiring locks on ACCOUNT rows in opposite order. What is the fundamental fix?

A) Increase the lock timeout to allow sessions more time to acquire locks. B) Change the isolation level to Uncommitted Read (UR) for the payroll process. C) Restructure the payroll process to acquire locks in a consistent order (e.g., ascending ACCOUNT_ID). D) Add more members to the data sharing group to distribute the lock contention.

Question 9

Your maintenance review reveals that RUNSTATS has not been executed on the TRANSACTION_HISTORY table for 45 days, during which 38 million new rows were inserted. What is the primary risk?

A) The table will become read-only until RUNSTATS is executed. B) The optimizer may choose suboptimal access paths because its cardinality and distribution estimates are inaccurate. C) New rows will not be visible to queries until RUNSTATS updates the catalog. D) Backup operations will fail because the catalog is inconsistent.

Question 10

Your capacity plan shows that OLTP storage utilization is at 65% with a projected annual growth rate of 33%. Assuming linear growth, approximately how many months until storage reaches 90% utilization?

A) 6 months B) 9 months C) 12 months D) 18 months

Question 11

You are preparing the executive summary for the board of directors. Which of the following is the BEST way to communicate a lock contention finding?

A) "Lock wait time averaged 340ms under stress load, exceeding the 50ms threshold." B) "The ACCOUNT table experienced X-lock contention between concurrent UOWs during simulated peak load." C) "During peak processing, some customer transactions experienced 3-second delays at the ATM due to internal database contention, which has been resolved." D) "The IRLM suspend rate exceeded 5% on global lock requests during the payroll posting window."

Question 12

After implementing all stress test remediations and re-running the test, one metric still fails: the 95th percentile response time is 520ms against a 500ms threshold. The average response time is 28ms. What does this pattern indicate?

A) The entire system is slow and needs a hardware upgrade. B) A small number of queries are significantly slower than the average, creating a long tail in the response time distribution; investigate those specific outlier queries. C) The monitoring system is inaccurate; 520ms and 500ms are within measurement error. D) The threshold should be raised to 600ms since the average is well within limits.

Question 13

During the security audit, you find that RCAC row permissions are defined on the CUSTOMER table but the ENABLE column shows 'N'. What does this mean?

A) The permissions are active and filtering rows correctly. B) The permissions are defined but NOT enforced — all users can see all rows regardless of the permission rules. C) The permissions are temporarily suspended during a maintenance window and will re-enable automatically. D) The permissions apply only to users with SECADM authority.

Question 14

Your capacity plan recommends moving archive storage to cloud object storage in Year 2. Which DB2 feature directly supports this strategy?

A) HADR log shipping to cloud storage. B) DB2 storage groups with cloud-tier containers. C) External tables that can read from object storage (e.g., IBM Cloud Object Storage via S3 API). D) Automatic data lifecycle management that moves tablespaces to the cloud.

Question 15

The capstone report includes a finding that the statement generation batch job uses Cursor Stability (CS) isolation when Uncommitted Read (UR) would suffice. What is the business impact of changing to UR?

A) No impact — UR and CS are functionally identical for read-only workloads. B) Reduced lock contention during peak processing, resulting in faster response times for concurrent OLTP transactions — but the batch job may read uncommitted (dirty) data. C) The batch job will run faster because UR skips all I/O operations. D) Changing isolation levels requires a database restart, causing downtime.

Answer Key

Answer 1: B

A quiesced member was intentionally stopped from processing new work, typically for maintenance (e.g., applying a fix pack). The correct response is to verify with the operations team that this is planned. If it is not planned, the member should be resumed.

Answer 2: B

REMOTE_CATCHUP indicates the standby is receiving and applying log data but has not yet caught up to the primary. This typically occurs after a network interruption or standby restart. It should transition to PEER once the log gap is closed. It is a warning-level finding, not critical.

Answer 3: C

The optimizer CAN generate efficient plans for tables without primary keys — the optimizer uses all available indexes, not just primary key indexes. The lack of a primary key is a data integrity concern (duplicate rows, no unique identifier), not a query optimization concern.

Answer 4: B

Assigning the batch workload to a separate buffer pool is the standard solution for buffer pool pollution from sequential scans. This isolates the batch I/O pattern from the OLTP working set. Simply increasing the buffer pool size (A) is less effective because the sequential scan will still flush OLTP pages. Disabling prefetch (C) would make the batch job much slower. Scheduling around OLTP (D) is not practical for a 24/7 banking environment.

Answer 5: B

Stale statistics are the most common cause of the optimizer ignoring an applicable index. If the catalog shows the table has far fewer rows than reality, the optimizer may estimate that a table scan is cheaper than an index scan. Running RUNSTATS corrects this. DB2 does not corrupt indexes silently (A), SELECT * does not force table scans (C), and there is no size-based index disabling (D).

Answer 6: B

The principle of least privilege requires granting only the specific permissions the application needs. DBADM includes the ability to drop tables, alter the database, and perform other destructive operations that no application should need. The correct approach is to identify the exact operations the application performs and grant only those privileges.

Answer 7: B

The TAKEOVER command itself is fast (12 seconds). The majority of the RTO is consumed by: detecting the failure (monitoring poll interval), obtaining operator authorization to proceed with the takeover, and waiting for applications to reconnect through Automatic Client Reroute.

Answer 8: C

Consistent lock ordering is the fundamental solution to deadlocks. If all sessions acquire locks in the same order (e.g., ascending ACCOUNT_ID), two sessions can never hold locks in opposing order, which is the necessary condition for a deadlock cycle. Increasing timeout (A) delays but does not prevent deadlocks. UR (B) would compromise data integrity for a write operation. Adding data sharing members (D) does not address the ordering issue.

Answer 9: B

The primary risk of stale statistics is suboptimal access path selection. The optimizer relies on catalog statistics (cardinality, distribution, clustering) to estimate the cost of different access strategies. With 38 million new rows unaccounted for, these estimates may be significantly wrong. Stale statistics do not affect data visibility (C) or backup operations (D), and the table remains fully accessible (A).

Answer 10: B

At 65% utilization growing at 33% per year (approximately 2.75% per month), reaching 90% requires growing 25 percentage points. 25 / 2.75 is approximately 9 months. This is an approximation — actual growth may not be linear — but 9 months is the closest answer.

Answer 11: C

Board members need business impact, not technical metrics. Option C translates the technical finding (lock contention) into a customer-facing impact (ATM delays) and notes that it has been resolved. The other options use technical terminology that a non-technical audience would not understand.

Answer 12: B

A low average with a high 95th percentile indicates a long-tailed distribution — most queries are fast, but a few are very slow. The correct approach is to identify those specific outlier queries and optimize them individually. Raising the threshold (D) is avoiding the problem. A hardware upgrade (A) is overkill. The difference is not measurement error (C) — 520ms vs. 500ms is a meaningful 4% overage.

Answer 13: B

When RCAC row permissions have ENABLE = 'N', they are defined but not enforced. This is a critical security finding — the permissions are providing no protection. They must be enabled with ALTER TABLE ... ACTIVATE ROW ACCESS CONTROL.

Answer 14: C

External tables in DB2 can read data directly from object storage via the S3 API. This allows archived data to reside in cloud object storage while remaining queryable from DB2. This is the most direct DB2 feature for the described use case.

Answer 15: B

Changing from CS to UR eliminates lock acquisition for the batch job's read operations, reducing contention with concurrent OLTP updates. The trade-off is that UR may read uncommitted data — but for a statement generation job reading historical data, this risk is minimal. UR does not skip I/O (C), and changing the isolation level of an application connection does not require a database restart (D).