How I Cut 10,000+ Db2 CPU Hours a Year: The Framework
What 23+ years as a z/OS Db2 DBA taught me about turning tuning into a repeatable practice. Db2 13 for z/OS.
For past 10+ years, I optimized 20+ Db2 for z/OS processes each year and cut more than 10,000 CPU hours every year.
No new hardware. No re-architecture. No big-bang project. The savings came from SQL rewrites and index changes, applied through a method I can repeat every month. This post is the overview. Over the coming weeks I’ll break down each stage with real numbers.
The framework at a glance
1. Choose: rank by total CPU, not by complaints
The loudest problem is rarely the most expensive one. I start from SMF 101 and take the top 50 CPU-consuming packages. Then, in IBM Query Monitor, I look inside them for a single SQL that accounts for at least 30% of the package’s CPU and is either heavy (an average of 5K+ getpages per execution) or high-volume (50K+ executions a day).
Get MANOJ PRADHAN’s stories in your inbox
Join Medium for free to get updates from this writer.
I then rank what is left by how fixable it is: clear access path inefficiencies first, such as non-matching index scans, full tablespace scans or a PROCSU of 10K or more, and then the sub-optimal ones, such as a low match column count. Peak-window work and critical-path batch jobs move up the list. Many top consumers get skipped, because they are already optimal and the effort would not pay off.
2. Fix: two levers did the work
Almost all of the savings came from two kinds of change: SQL rewrites and index changes. The fixes are often small. Here are four statements that show the range:
Those four statements alone account for over 1,000 of the 10,000+ hours. None of them looked alarming on its own: one cost 0.35 ms per execution. They only stood out when ranked by total CPU.
3. Rebind safely: never rebind blind
A change only pays off once packages pick it up, and a rebind is the moment the optimizer can change its mind about every statement in a package. So, I preview first with EXPLAIN(ONLY) and APCOMPARE(ERROR), evaluate which access paths would change, rebind only the reviewed list, and check for regression afterward. When a package regresses, SWITCH(PREVIOUS) is the quick fallback if nothing in it improved. If it has both gains and a regression, a hint restores just the bad statement. After one index change, I rebound 100+ dependent packages, 10+ statements in other programs improved, and nothing got worse.
4. Prove: a saving no one believes isn’t a saving
I baseline before changing anything, using IBM Query Monitor for statement-level CPU and SMF 101 for program-level CPU. I annualize as per-execution saving × current volume × run days, so workload changes never count as tuning. I report the figures to the application team and to management as soon as each item is done, and I roll them up monthly, as CPU hours and MSU. Each item is flagged as inside or outside the peak window, and I report the benefits that CPU doesn’t capture: critical-path time recovered, lock waits, deadlocks and timeouts.
5. Repeat: it is a practice, not a project
Workloads change, new code is deployed, and yesterday’s fix moves the next candidate to the top. I rerun the ranking every month. The 10,000 hours did not come from one heroic effort. They came from doing the same disciplined routine, over and over.
The skill is not finding clever fixes. It is knowing where to look, changing it safely, and proving the result.
What’s the biggest CPU win you’ve found in your environment, and what was behind it?
