Here’s a practical, field-tested 10-point Db2 database health check you can use to quickly assess the state of your environment and identify risk areas.
1. Buffer Pool Efficiency
- Check hit ratios (GETPAGE vs. I/O)
- Look for excessive synchronous reads
- Ensure critical objects reside in appropriately sized buffer pools
2. SQL Performance & Access Paths
- Identify top CPU-consuming and elapsed time SQL
- Look for table scans, non-matching index scans, and sort activity
- Validate access paths haven’t regressed (especially after RUNSTATS or REBIND)
3. Index Effectiveness
- Check for unused or duplicate indexes
- Identify missing indexes for high-frequency queries
- Review clustering ratios and index levels
4. RUNSTATS Currency & Quality
- Ensure statistics are up to date on critical tables and indexes
- Verify distribution stats (FREQVAL, HISTOGRAM) where needed
- Watch for stale or missing stats causing poor optimizer choices
5. Locking & Concurrency
- Monitor lock waits, timeouts, and deadlocks
- Identify hot objects or pages
- Evaluate isolation levels and commit frequency
6. I/O Performance
- Review I/O response times by dataset and volume
- Check for hotspots or imbalance across storage groups
- Ensure proper striping and dataset placement
7. Logging & Recovery Health
- Monitor active log utilization and archive activity
- Check for long-running units of work delaying log reuse
- Validate backup and recovery procedures (image copies, PIT recovery readiness)
8. Utilities & Maintenance
- Ensure REORGs are run where needed (based on RTS, not just schedule)
- Validate COPY, RUNSTATS, and REORG cadence
- Look for objects in advisory REORG or COPY pending states
9. System Resource Utilization
- Track CPU consumption (general purpose vs. zIIP if applicable)
- Monitor memory usage and EDM pool (for Db2 for z/OS)
- Watch for thread reuse and connection pooling efficiency
10. Configuration & ZPARM Settings
- Review key ZPARMs (or Db2 LUW configuration parameters)
- Validate they align with workload characteristics
- Check for outdated defaults that may no longer be optimal
Bonus Tip: Trend, Don’t Snapshot
A single point-in-time check is useful—but trending over time is where the real insight lives. Look for gradual degradation, not just obvious breakage.
