Chapter 17 Exercises: DB2 Utilities — REORG, RUNSTATS, COPY, RECOVER, LOAD, UNLOAD

These exercises progress from fundamental utility syntax through complex maintenance scenarios. All exercises use the Meridian National Bank environment.

Section A: REORG (Exercises 1-8)

Exercise 1: Basic REORG Concepts

Explain the difference between SHRLEVEL NONE, SHRLEVEL REFERENCE, and SHRLEVEL CHANGE for the z/OS REORG utility. For each, state whether read access and write access are permitted during the reorganization, and describe the typical use case.

Exercise 2: z/OS REORG JCL

Write complete JCL using the DSNUPROC cataloged procedure to REORG the tablespace MERIDIANDB.LOAN_TS with the following requirements: - SHRLEVEL CHANGE (online access required) - FASTSWITCH YES - Mapping table: MERIDIAN.REORG_MAP_LOANS - Include inline statistics with KEYCARD and FREQVAL COUNT 15 - LOG YES for recoverability - Provide four SORTWK datasets of 80 cylinders each

Exercise 3: LUW Inplace REORG

Write the DB2 LUW commands to: a) Start an inplace REORG on the table meridian.transfers with write access allowed b) Check the progress of the REORG using the monitoring view c) Pause the REORG during a peak activity period d) Resume the REORG after the peak period e) Verify completion

Exercise 4: REORG Assessment

Write the z/OS utility control statement to run a REORG in REPORTONLY mode on MERIDIANDB.TRANS_TS. Then write the equivalent LUW command to check whether REORG is recommended for meridian.transactions. Explain what each report tells you.

Exercise 5: Index-Only REORG

Meridian Bank's DBA team notices that the index IX_TRANS_DATE on the TRANSACTIONS table has become fragmented, but the table data itself is still well-clustered. Write the commands for both z/OS and LUW to reorganize only the indexes without touching the table data.

Exercise 6: Partitioned REORG

The TRANSACTIONS tablespace is partitioned by quarter. Write z/OS REORG JCL to reorganize only partition 4 (Q4 2025 data) with SHRLEVEL REFERENCE. Explain why reorganizing a single partition is preferable to reorganizing the entire tablespace in this scenario.

Exercise 7: REORG with LOG NO

A junior DBA proposes running REORG with LOG NO on the ACCOUNTS tablespace to reduce the maintenance window duration. Write the REORG control statement they would use. Then explain: a) What status the tablespace enters after REORG LOG NO b) What must be done immediately after the REORG c) What happens if the system crashes before that action is completed d) Whether you would recommend this approach for ACCOUNTS. Why or why not?

Exercise 8: Multi-Tablespace REORG with LISTDEF

Write a complete z/OS JCL job that uses LISTDEF and TEMPLATE to REORG five tablespaces (ACCOUNTS_TS, TRANS_TS, CUSTOMER_TS, LOAN_TS, BRANCH_TS) in a single job. Use dynamic dataset naming with TEMPLATE. Include inline statistics with histograms.

Section B: RUNSTATS (Exercises 9-14)

Exercise 9: Basic RUNSTATS

Write RUNSTATS commands for both platforms: a) [z/OS] RUNSTATS on MERIDIANDB.ACCOUNTS_TS with TABLE(ALL), INDEX(ALL), KEYCARD, and FREQVAL COUNT 25 b) [LUW] RUNSTATS on meridian.accounts with distribution statistics and detailed index statistics

Exercise 10: Column Group Statistics

The Meridian Bank query SELECT ... FROM transactions WHERE branch_id = 105 AND account_type = 'SAVINGS' is performing poorly. The optimizer estimates 500 rows but the actual result is 45,000 rows. Explain why individual column statistics might cause this misestimate, and write the RUNSTATS commands (both z/OS and LUW) to collect column group statistics on (BRANCH_ID, ACCOUNT_TYPE).

Exercise 11: RUNSTATS Sampling

The TRANSACTIONS tablespace has grown to 500 GB and a full RUNSTATS takes 4 hours. The maintenance window is only 2 hours. Write the z/OS RUNSTATS command using 20% sampling. Then answer: what are the trade-offs of sampling vs. full statistics collection?

Exercise 12: Inline RUNSTATS

Rewrite the following z/OS REORG to include inline statistics collection, eliminating the need for a separate RUNSTATS step:

REORG TABLESPACE MERIDIANDB.TRANS_TS
  SHRLEVEL CHANGE
  FASTSWITCH YES
  LOG YES
  SORTDATA YES
  SORTKEYS YES

Include KEYCARD, FREQVAL COUNT 20, and HISTOGRAM NUMQUANTILES 100 in your inline statistics.

Exercise 13: Automatic Statistics (LUW)

a) Write the commands to enable automatic RUNSTATS on the meridiandb database. b) Explain two scenarios where automatic statistics collection is sufficient and two scenarios where explicit RUNSTATS with custom options is preferable. c) Write a query to check which tables have stale statistics (more than 20% of rows modified since last RUNSTATS).

Exercise 14: Statistics Freshness Monitoring

Write a monitoring query for LUW that produces a report showing, for each table in the MERIDIAN schema: - Table name - Row count (CARD) - Last RUNSTATS time - Rows modified since last RUNSTATS - A status column showing "CRITICAL" (>30% modified), "WARNING" (>15% modified), "OK" (<15% modified), or "NEVER" (no statistics collected)

Sort by status severity descending.

Section C: COPY and BACKUP (Exercises 15-19)

Exercise 15: z/OS Full Image Copy

Write complete JCL to take a full image copy of MERIDIANDB.ACCOUNTS_TS with: - Dual copies: COPY1 to DASD, COPY2 to tape - SHRLEVEL REFERENCE - Appropriate dataset names including the date

Exercise 16: z/OS Incremental Copy

Explain the difference between a full and incremental image copy. Then write the z/OS control statement for an incremental copy of MERIDIANDB.TRANS_TS. Answer: if you have one full copy and three incremental copies, describe the recovery process that DB2 will follow when RECOVER TABLESPACE is executed.

Exercise 17: LUW Online Backup

Write the DB2 LUW commands to: a) Take a full online backup of meridiandb with compression and 4-way parallelism b) Take an incremental backup the next day c) Take a delta backup later that same day d) Verify the most recent backup image

Exercise 18: FlashCopy

Explain how FlashCopy works at the storage level and why it can complete a 500 GB tablespace copy in seconds. Write the z/OS COPY control statement that enables FlashCopy. Under what circumstances would FlashCopy not be available?

Exercise 19: Backup Strategy Design

Design a backup strategy for Meridian Bank's z/OS environment with the following requirements: - RPO (Recovery Point Objective): 4 hours maximum data loss - RTO (Recovery Time Objective): 2 hours maximum recovery time - Critical tablespaces: ACCOUNTS_TS (50 GB), TRANS_TS (200 GB), CUSTOMER_TS (30 GB) - Maintenance window: Sunday 1-5 AM only for full outage - The system must be available 24x7 for reads; write outage limited to 30 minutes

Specify the copy types (full/incremental), frequencies, SHRLEVEL, and storage targets. Justify each decision.

Section D: RECOVER (Exercises 20-23)

Exercise 20: Basic Recovery

Write the z/OS utility control statement to recover MERIDIANDB.ACCOUNTS_TS to its most current state. Then describe the step-by-step process DB2 follows internally to complete the recovery.

Exercise 21: Point-in-Time Recovery

At 2:15 PM, a developer accidentally runs DELETE FROM MERIDIAN.TRANSACTIONS WHERE STATUS = 'PENDING' without a WHERE clause on the date column, deleting 2 million rows. You need to recover the tablespace to 2:14 PM. a) Write the z/OS RECOVER command for point-in-time recovery b) Write the LUW RESTORE and ROLLFORWARD commands for point-in-time recovery c) What happens to indexes after point-in-time recovery on z/OS? What must you do? d) What happens to referential integrity? Will other tables that reference TRANSACTIONS be affected?

Exercise 22: Recovery Failure Scenario

You attempt to recover MERIDIANDB.TRANS_TS but the recovery fails with a message indicating that required archive logs are unavailable. The last full image copy is 10 days old, and archive logs are retained for 7 days. a) Explain why the recovery failed b) What data has been lost? c) What changes should be made to the backup/retention strategy to prevent this? d) Write the monitoring query or command to proactively detect this risk before a failure occurs

Exercise 23: LUW Database Recovery

Write the complete LUW recovery sequence to: a) Restore the meridiandb database from the most recent backup b) Roll forward to end of logs c) Roll forward to a specific timestamp (2026-03-16 at 14:30:00) d) Explain the difference between AND COMPLETE and AND STOP in the ROLLFORWARD command

Section E: LOAD and UNLOAD/EXPORT (Exercises 24-28)

Exercise 24: z/OS LOAD

Write complete JCL for a z/OS LOAD job that loads daily transaction data from dataset MERIDIAN.DAILY.TRANS.D260316 into MERIDIAN.TRANSACTIONS with: - RESUME YES (append mode) - LOG YES - ENFORCE CONSTRAINTS - Field mapping for: TRANS_ID (CHAR 20), ACCOUNT_ID (CHAR 15), TRANS_DATE (DATE), TRANS_TYPE (CHAR 3), AMOUNT (DECIMAL 13,2) - WHEN clause to load only deposits (DEP) and withdrawals (WDR) - Inline statistics collection

Exercise 25: LUW LOAD

Write DB2 LUW LOAD commands for each of these scenarios: a) Load from a comma-delimited CSV file in INSERT mode (append) b) Load from an IXF file in REPLACE mode (full reload) c) Load from a cursor that selects validated rows from a staging table d) Restart a previously failed LOAD e) Terminate a LOAD that left the table in LOAD PENDING state

Exercise 26: LOAD vs. INSERT Performance

Meridian Bank needs to load 5 million rows into the FACT_TRANSACTIONS table nightly. The current process uses batch INSERT statements with COMMIT every 1,000 rows and takes 3 hours. a) Estimate the performance improvement from switching to LOAD b) Write the LUW LOAD command for this scenario c) What are the trade-offs? (Consider: triggers, constraints, availability, logging) d) If triggers must fire during the load, what alternative approach would you use?

Exercise 27: UNLOAD and EXPORT for Archival

Meridian Bank's data retention policy requires archiving transactions older than 3 years. Write: a) [z/OS] UNLOAD command to extract transactions from 2023 b) [LUW] EXPORT command to extract the same data in IXF format c) The DELETE statement to purge the archived data (both platforms) d) The REORG and RUNSTATS sequence to reclaim space after the purge

Exercise 28: Cross-Platform Data Migration

Meridian Bank is migrating the BRANCHES reference table from z/OS to LUW. Write the complete migration sequence: a) [z/OS] UNLOAD the BRANCHES table b) Describe the file transfer process (encoding, format considerations) c) [LUW] Create the target table d) [LUW] LOAD the data from the transferred file e) [LUW] Verify row counts and data integrity

Section F: Integration and Maintenance Planning (Exercises 29-33)

Exercise 29: Maintenance Chain

Explain why the REORG-RUNSTATS-COPY sequence must execute in that specific order. For each pair of steps, describe what goes wrong if they are reversed: a) RUNSTATS before REORG b) COPY before RUNSTATS c) COPY before REORG

Exercise 30: Utility Chaining with LISTDEF

Write a complete z/OS JCL job that executes the full REORG-RUNSTATS-COPY maintenance chain for five tablespaces using LISTDEF and TEMPLATE. Optimize the job by using inline statistics with REORG (combining REORG and RUNSTATS into a single step).

Exercise 31: LUW Maintenance Script

Write a complete bash shell script that performs weekly maintenance on the Meridian Bank LUW database: 1. Inplace REORG of four tables with progress monitoring 2. RUNSTATS using saved profiles 3. Full online compressed backup 4. Email notification of completion with success/failure status 5. Log all output to a dated log file

Exercise 32: Meridian Bank Complete Maintenance Plan

Design the complete maintenance plan for Meridian Bank covering both z/OS and LUW platforms. Your plan must include: - Daily, weekly, and monthly maintenance tasks - Specific utility commands for each task - Scheduling (times and frequencies) - Monitoring and alerting thresholds - Recovery time estimates for each critical tablespace - Exception handling (what to do when a utility fails)

Present your plan as a table with columns: Task, Platform, Frequency, Schedule, Estimated Duration, and Dependencies.

Exercise 33: Spaced Review Integration

Connect the concepts from this chapter to earlier material: a) Explain how RUNSTATS directly affects the access path selection you studied in Chapter 15 (Indexing Strategies). Give a specific example where stale statistics cause the optimizer to choose a suboptimal access path. b) Explain how REORG relates to the tablespace and buffer pool concepts from Chapter 14 (Physical Storage). How does fragmentation affect buffer pool hit ratios? c) A query from Chapter 3 that once returned instantly now takes 30 seconds. Walk through the diagnostic process using the utilities from this chapter to identify and fix the problem.