Chapter 17: Key Takeaways

The Six Core Utilities

1. REORG eliminates fragmentation and restores physical clustering order. Without it, sequential scans degrade and index efficiency drops as rows scatter across pages. On z/OS, SHRLEVEL CHANGE enables online REORG for 24x7 systems; on LUW, inplace REORG with ALLOW WRITE ACCESS provides equivalent capability.

2. RUNSTATS updates the catalog statistics that the optimizer depends on for access path selection. Stale statistics are one of the most common and most preventable causes of poor query performance. KEYCARD, FREQVAL, HISTOGRAM, and column group statistics give the optimizer the information it needs to make accurate cardinality estimates.

3. COPY (z/OS) / BACKUP (LUW) creates recoverable images of your data. Full copies capture everything; incremental copies capture only changes since the last copy. FlashCopy on z/OS enables near-instant backups of even the largest tablespaces.

4. RECOVER (z/OS) / RESTORE + ROLLFORWARD (LUW) restores data from backup images and applies log records to bring data to a consistent point in time. Point-in-time recovery using TOCOPY, TOLOGPOINT, or specific timestamps allows precise restoration after logical errors.

5. LOAD provides high-speed data ingestion at 5 to 20 times the throughput of equivalent INSERT statements by writing directly to tablespace pages and minimizing logging overhead. Use it for any bulk data movement.

6. UNLOAD (z/OS) / EXPORT (LUW) extracts data from tables into flat files or exchange formats for migration, archival, reporting, or cross-platform transfers.

Critical Principles

• The maintenance chain is REORG, then RUNSTATS, then COPY. This sequence ensures data is physically optimized, statistics reflect the optimized layout, and backups capture the clean state. Inline statistics with REORG can merge the first two steps.

• Backup without tested recovery is false security. Perform test recoveries monthly, rotating through critical tablespaces. Document recovery times. Know your recovery window before disaster strikes.

• Stale statistics cause bad access paths. A query that runs in 2 seconds with current statistics can take 90 seconds with stale statistics — and nobody changed the SQL. RUNSTATS is how you prevent this.

• Fragmentation is cumulative and invisible until it hurts. Monitor disorganization metrics proactively. Set alerting thresholds. Do not wait for application teams to report performance problems.

• SHRLEVEL CHANGE and ONLINE operations make maintenance feasible for 24x7 systems. There is no valid excuse for skipping maintenance because of availability requirements. Modern DB2 provides online variants for every major utility.

• Automate utility execution. Manual maintenance is sporadic maintenance. Use JCL with LISTDEF/TEMPLATE on z/OS and shell scripts with cron on LUW. Monitor for success and alert on failure.

Platform Quick Reference

Meridian Bank Maintenance Summary

Warning Signs That Maintenance Is Overdue

• Query runtimes increase with no SQL or data model changes
• Buffer pool hit ratios decline for specific tablespaces
• REORG REPORTONLY shows disorganization above 20-30%
• STATS_ROWS_MODIFIED exceeds 15-20% of table cardinality
• Tablespace extent counts grow without corresponding data growth
• Recovery time estimates exceed the organization's RTO
• Any tablespace in COPY-pending, CHECK-pending, or RECOVER-pending status