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

These exercises are project-based and designed to be completed over several days. They represent the culmination of every skill developed throughout this book. Where possible, execute these against a real DB2 environment (development or test — never production without authorization).

Exercise 36.1: System Architecture Inventory

Objective: Document the complete architecture of a DB2 environment.

  1. Connect to your DB2 instance and capture the following: - DB2 version and fix pack level. - Operating system and version. - Number of database partitions (or data sharing members on z/OS). - High availability configuration (HADR role and state, or data sharing group membership). - All database configuration parameters that differ from defaults.

  2. Create an architecture diagram showing: - All DB2 instances and databases. - Network connectivity between instances (if multiple). - Storage layout (tablespace to container mapping). - Client application connectivity paths.

  3. Compare your documented architecture against any existing documentation. List all discrepancies.

Deliverable: A one-page architecture summary with a diagram and a discrepancy list.

Exercise 36.2: Schema Health Audit

Objective: Conduct a catalog-based audit of a database schema.

Using the queries from Section 36.3, perform the following against your MERIDIAN schema (or any application schema):

  1. Identify all tables without primary keys. For each, explain why a primary key is necessary and propose one.

  2. Find all potentially redundant indexes (where one index's columns are a prefix of another). For each pair, determine whether the shorter index can be safely dropped.

  3. Find all unused indexes (zero scans since the last monitoring reset). For each, investigate whether it supports a periodic process before recommending removal.

  4. Identify columns with potentially inappropriate data types (e.g., VARCHAR(4000) for short values, DECIMAL(31,0) for small integers).

  5. Compile your findings into a table with columns: Finding ID, Object, Issue, Impact, Severity, Recommendation.

Deliverable: A schema audit report with at least 5 findings.

Exercise 36.3: Performance Baseline Capture

Objective: Establish a performance baseline for a DB2 database.

  1. Write a script that captures the following metrics every 15 minutes for 4 hours: - Total commits and rollbacks. - Average request CPU time. - Buffer pool hit ratios (data and index). - Lock wait time and lock waits. - Sort overflows. - Rows read and rows returned.

  2. Calculate the following baseline values: - Average throughput (commits per minute). - Average buffer pool hit ratio. - Average lock wait time per lock wait. - Sort overflow percentage.

  3. Graph the throughput over the 4-hour window. Identify any patterns (e.g., peaks during batch processing).

Deliverable: A baseline report with metric values and a throughput graph.

Exercise 36.4: Stress Test Design and Execution

Objective: Design and run a simulated stress test.

  1. Using the stress-test-workload.sql file as a starting point, customize the workload for your environment: - Replace table and column names with your actual schema objects. - Adjust the data values to match your test data. - Set appropriate transaction mix ratios.

  2. Run the stress test with at least 20 concurrent connections (use whatever concurrency your test environment can support).

  3. During the test, capture metrics at 60-second intervals.

  4. After the test, analyze the results: - Which metrics degraded compared to the baseline? - What were the top 5 resource-consuming statements? - Were there any lock escalations or deadlocks? - Did any sort overflows occur?

  5. For each identified issue, propose a specific remediation.

Deliverable: A stress test results report with findings and recommended remediations.

Exercise 36.5: EXPLAIN Deep Dive

Objective: Capture and analyze access paths for critical queries.

  1. Identify the 5 most expensive queries from your stress test (or from the package cache if you did not run a stress test).

  2. For each query: a. Run EXPLAIN and capture the access plan. b. Identify the access method for each table (table scan, index scan, index-only scan). c. Identify the join method (nested loop, hash join, merge scan). d. Note the estimated cost and the estimated cardinality at each operator.

  3. For at least one query, identify an optimization opportunity (missing index, rewritten predicate, covering index) and implement it.

  4. Re-run EXPLAIN after the optimization and compare the estimated cost before and after.

Deliverable: EXPLAIN analysis for 5 queries, with a before/after comparison for at least one optimization.

Exercise 36.6: Security Audit

Objective: Conduct a security audit of your DB2 environment.

  1. List all users and roles with DBADM, SECADM, DATAACCESS, or ACCESSCTRL authority. For each, verify that the authority is justified.

  2. Check for individual user grants on application tables (as opposed to role-based grants). Count the number of individual grants.

  3. If RCAC policies exist, verify that they are enabled and test them: - Connect as a restricted user and attempt to read masked columns. Verify that masking is working. - Connect as a privileged user and verify they can see the full data.

  4. Check the audit policy configuration. Are all required event categories being audited?

  5. Verify that all DB2 connections use encryption (TLS/SSL).

  6. Map your findings to one compliance framework relevant to your organization (PCI-DSS, SOX, HIPAA, or your internal security policy).

Deliverable: A security audit report with findings mapped to compliance requirements.

Exercise 36.7: Disaster Recovery Drill

Objective: Execute a backup and recovery exercise.

WARNING: Perform this exercise ONLY in a test environment. Do NOT perform on production databases without explicit authorization and a change management approval.

  1. Take a full online backup of your test database: BACKUP DATABASE testdb TO /db2backup ONLINE

  2. Perform some DML operations (insert, update, delete rows) to generate log activity.

  3. Record the current timestamp (this is your recovery target).

  4. Perform additional DML operations (these represent data "after" the recovery point).

  5. Simulate a failure by dropping a table or tablespace.

  6. Restore and recover to the point-in-time recorded in step 3: RESTORE DATABASE testdb FROM /db2backup TAKEN AT [timestamp] ROLLFORWARD DATABASE testdb TO [timestamp] AND COMPLETE

  7. Verify data integrity: - Confirm that the data from step 2 is present. - Confirm that the data from step 4 is NOT present (it was after the recovery point).

  8. Record the time for each step and calculate your total RTO.

Deliverable: A DR drill report with step-by-step timing and data integrity verification results.

Exercise 36.8: Concurrency Analysis

Objective: Analyze and resolve locking issues.

  1. Open two separate DB2 connections to the same database.

  2. In Connection 1, start a transaction and update a row without committing: sql UPDATE MERIDIAN.ACCOUNT SET CURRENT_BALANCE = CURRENT_BALANCE + 100 WHERE ACCOUNT_ID = 1001; -- Do NOT commit

  3. In Connection 2, attempt to read the same row under different isolation levels: - Uncommitted Read (UR) - Cursor Stability (CS) - Read Stability (RS) - Repeatable Read (RR)

  4. Document which isolation levels blocked and which did not. Explain why.

  5. Create a deadlock scenario: - Connection 1: Lock row A, then attempt to lock row B. - Connection 2: Lock row B, then attempt to lock row A.

  6. Capture the deadlock from the monitoring interface. Identify the victim and the deadlock cycle.

  7. Redesign the access pattern to prevent the deadlock using consistent lock ordering.

Deliverable: A concurrency analysis report with deadlock evidence and the redesigned access pattern.

Exercise 36.9: Maintenance Review

Objective: Verify the maintenance schedule for a DB2 database.

  1. Check the REORG status of all tables. List any tables in REORG-pending state or with more than 1,000 overflow rows.

  2. Check the statistics currency for all tables. List any tables where statistics are more than 7 days old.

  3. Review the backup history. Verify that backups are occurring on schedule. Identify any gaps.

  4. Check for any tables in non-normal states (check pending, inoperative, etc.).

  5. Propose a maintenance schedule that includes: - Weekly RUNSTATS for high-activity tables. - Monthly online REORG for tables with significant overflow. - Daily incremental backups and weekly full backups. - Monitoring alert thresholds for each metric.

Deliverable: A maintenance schedule document with current-state findings and the proposed schedule.

Exercise 36.10: Capacity Planning Report

Objective: Create a three-year capacity plan.

  1. Using the queries from capacity-planning-queries.sql, capture current utilization for: - Storage (by tablespace and total). - Memory (buffer pools, sort heap, total database memory). - CPU (total CPU time, requests per second). - Connections (current and maximum configured).

  2. Analyze historical growth (use any available historical data — backup sizes, table cardinalities, transaction counts).

  3. Project storage requirements for Years 1, 2, and 3 based on the observed growth rate.

  4. Identify the first resource that will reach capacity limits. What is the "time to exhaustion"?

  5. Propose hardware upgrades or configuration changes to support the projected growth.

  6. Estimate costs for your recommendations (use approximate market prices for storage, memory, compute).

Deliverable: A capacity planning report with projections, recommendations, and cost estimates.

Exercise 36.11: Executive Summary

Objective: Translate your technical findings into a business-ready report.

Using the findings from Exercises 36.1-36.10, create a one-page executive summary that:

  1. Uses a traffic-light dashboard (Green / Yellow / Red) for the six assessment areas: - Architecture - Schema and Design - Performance - Security - Disaster Recovery - Capacity

  2. Lists the top 5 findings with business impact, recommended action, timeline, and estimated cost.

  3. Includes a three-year investment summary.

  4. Uses NO SQL, NO technical jargon. Write it for a board of directors audience.

Deliverable: A one-page executive summary suitable for presentation to non-technical stakeholders.

Capstone Project: Complete System Health Report

Objective: Combine all of the above exercises into a single, comprehensive system health report.

Compile your deliverables from Exercises 36.1 through 36.11 into a structured document following the template in Section 36.12. This report should:

  1. Have a professional cover page with report date, author, and classification.
  2. Begin with the executive summary (Exercise 36.11).
  3. Include detailed sections for each assessment area.
  4. Include all supporting data, queries used, and evidence for each finding.
  5. End with an appendix listing all queries executed, tools used, and team members involved.

This report is your capstone portfolio piece. It demonstrates the full range of DB2 DBA competence.

Deliverable: A complete system health assessment report (target: 15-25 pages).