SQLMentor // learn db2

Performance Tuning

Difficulty: Advanced · ~12 min read

Overview

DB2 performance tuning is a layered discipline. From cheapest to most invasive:

  1. Write better SQL — most performance problems come from the query itself
  2. Keep statistics fresh — RUNSTATS after big data changes
  3. Add the right indexes — fewer, well-targeted ones
  4. Tune memory — buffer pools, sort heap
  5. Reorg / rebuild — defragment tables and indexes
  6. Partition or shard — split huge tables

The cost-based optimizer reads statistics from SYSCAT.* and estimates the cheapest way to satisfy each query. Out-of-date statistics are the single most common cause of bad plans — DB2 picks a full scan when an index would be faster (or vice versa).

Syntax

-- Capture a query plan
EXPLAIN PLAN FOR <query>;

-- Format the latest plan
db2exfmt -d <dbname> -1 -o explain.txt

-- Update statistics
RUNSTATS ON TABLE schema.table
  WITH DISTRIBUTION AND DETAILED INDEXES ALL;

-- Defragment a table and rebuild indexes
REORG TABLE schema.table;
REORG INDEXES ALL FOR TABLE schema.table;

-- Check live activity
db2pd -db <dbname> -applications
db2pd -db <dbname> -dynamic

Examples

Example 1: EXPLAIN — see the query plan

-- Set up the explain tables if not done already
db2 -tvf $HOME/sqllib/misc/EXPLAIN.DDL

-- Capture
EXPLAIN PLAN FOR
SELECT c.name, SUM(o.amount)
FROM   customers c
JOIN   orders    o ON c.customer_id = o.customer_id
WHERE  c.region = 'EU'
GROUP BY c.name;

Then format and inspect:

db2exfmt -d sample -1 -o explain.txt

Look for these access methods in the output:

Method Meaning
TBSCAN Full table scan — no index used
IXSCAN Index scan — used an index to locate rows
RIDSCN Row-ID scan — multiple index lookups merged
FETCH Read the row from the table after an index hit
NLJOIN Nested-loop join — small outer, indexed inner
HSJOIN Hash join — large unsorted inputs
MSJOIN Merge-sort join — pre-sorted inputs

A TBSCAN on a large table is usually the smell. Either add an index, rewrite the query, or update statistics.

Example 2: RUNSTATS — feeding the optimizer

-- Fast: just basic stats
RUNSTATS ON TABLE sales.orders;

-- Thorough: include histograms ("distribution") so the optimizer
-- understands skew, plus full index stats
RUNSTATS ON TABLE sales.orders
  WITH DISTRIBUTION
  AND  DETAILED INDEXES ALL;

-- Even better: tell DB2 to sample for huge tables
RUNSTATS ON TABLE sales.orders
  WITH DISTRIBUTION ON COLUMNS (region, status)
  AND  DETAILED INDEXES ALL
  TABLESAMPLE BERNOULLI(10);

Output snippet from before & after:

-- Before RUNSTATS (default stats)
Total Cost: 24500
Access: TBSCAN of ORDERS

-- After RUNSTATS
Total Cost:  912
Access: IXSCAN of IDX_ORDERS_REGION; FETCH ORDERS

Same query, 27× faster — DB2 now knows the index is highly selective for region = 'EU'.

Example 3: Reading and understanding selectivity

DB2 stores per-column cardinality in SYSCAT.COLDIST (distribution) and SYSCAT.COLUMNS (colcard):

SELECT colname, colcard, high2key, low2key
FROM   syscat.columns
WHERE  tabname = 'ORDERS' AND tabschema = 'SALES';

If colcard for a filter column is high and your WHERE is selective, an index there is golden. If colcard is low (a 3-value status flag), don't bother.

Example 4: Adding the right index

-- Before: full scan on every region-filter query
SELECT * FROM orders WHERE region = 'EU' AND status = 'OPEN';

-- Add a composite index — most-selective column first
CREATE INDEX idx_orders_region_status
  ON orders (region, status);

RUNSTATS ON TABLE orders FOR INDEXES ALL;

Re-EXPLAIN and confirm an IXSCAN replaces the TBSCAN. (See the Indexes topic for deeper guidance.)

Example 5: REORG — defrag a fragmented table

After many UPDATE/DELETEs, rows get scattered. REORG rewrites the table compactly:

-- Check fragmentation
CALL SYSPROC.REORGCHK_TB_STATS('T', 'SALES.ORDERS');

-- If needed:
REORG TABLE sales.orders;
REORG INDEXES ALL FOR TABLE sales.orders;

-- Always re-runstats after REORG
RUNSTATS ON TABLE sales.orders WITH DISTRIBUTION AND DETAILED INDEXES ALL;

For 24/7 systems, use REORG TABLE ... INPLACE (online reorg) — slower per-row but no exclusive lock.

Example 6: Buffer pool tuning

-- See hit ratios (higher is better)
SELECT bp_name, total_hit_ratio_percent, data_hit_ratio_percent, index_hit_ratio_percent
FROM   sysibmadm.bp_hitratio;

-- Increase a buffer pool's size (pages)
ALTER BUFFERPOOL bp_data SIZE 200000;

A data hit ratio below ~85% on a critical buffer pool is a sign you need more memory.

Example 7: Find the most expensive recent queries

SELECT  SUBSTR(stmt_text, 1, 60) AS stmt,
        num_executions,
        total_act_time / num_executions / 1000 AS avg_ms,
        rows_returned / NULLIF(num_executions,0)  AS avg_rows
FROM    sysibmadm.mon_get_pkg_cache_stmt
ORDER   BY total_act_time DESC
FETCH   FIRST 20 ROWS ONLY;

That's your performance hit-list — fix these first.

Example 8: Workload-management quick win

-- Show currently running statements
SELECT  application_handle, total_cpu_time, SUBSTR(stmt_text, 1, 60)
FROM    TABLE(MON_GET_ACTIVITY(NULL, -1))
ORDER   BY total_cpu_time DESC;

-- Kill a runaway query
db2 force application (<application_handle>)

Notes & Tips

  • Run RUNSTATS after large data loads and any time you've added/dropped an index. Stale stats produce wildly wrong plans.
  • EXPLAIN is free — it doesn't actually run the query. Run it on every new query you put in production.
  • Avoid SELECT * in application code — it forces DB2 to read every column (and prevents index-only access). List the columns you need.
  • Filter early, project late. Reduce row count first (WHERE), then reduce column count (SELECT cols). The optimizer usually does this for you, but well-written SQL helps.
  • Watch out for implicit casts. WHERE int_col = '42' may cast the column, killing an index. Match types in your literals.
  • Use bind variables (parameter markers). They let DB2 cache the plan and avoid re-parsing the same query shape repeatedly.
  • For analytics workloads, look at column-organized tables (BLU Acceleration) — DB2 11+ can store tables column-wise for huge scan/aggregate speedups.
  • REORG and RUNSTATS together — schedule both during maintenance windows. REORG alone leaves stale stats that may mislead the optimizer.

Practice Exercises

  1. Pick the slowest query in sysibmadm.mon_get_pkg_cache_stmt and run EXPLAIN PLAN FOR on it. Identify whether it's using TBSCAN, IXSCAN, or RIDSCN.
  2. Find a frequently-filtered column with no supporting index. Add the index, RUNSTATS FOR INDEXES ALL, re-EXPLAIN, and confirm the plan switched.
  3. Capture the cost of a query before and after RUNSTATS WITH DISTRIBUTION.
  4. Use MON_GET_ACTIVITY to find currently-running queries by CPU time.
  5. Check BP_HITRATIO for every buffer pool — note which ones are under 85%.

Quick Quiz

Q1. What's the single most common cause of bad DB2 query plans?

Show answer

Out-of-date statistics. The cost-based optimizer makes decisions based on row counts, value distributions, and index cardinalities stored in SYSCAT.*. If statistics are stale (after big data changes or no RUNSTATS ever), the optimizer underestimates or overestimates and picks the wrong access path. Schedule RUNSTATS after any significant data movement.

Q2. What does an IXSCAN mean in an EXPLAIN plan?

Show answer

DB2 used an index scan to locate rows. Look for IXSCAN followed by FETCH — the FETCH is reading the actual row from the table after the index hit. If you see just IXSCAN with no FETCH, DB2 satisfied the query entirely from the index (index-only access) — the fastest possible read.

Q3. When should you REORG a table?

Show answer

After many UPDATE/DELETEs have fragmented the table — when overflow rows are high or row-per-page density is low. Use CALL SYSPROC.REORGCHK_TB_STATS(...) or query SYSCAT.TABLES for npages vs card to decide. Don't REORG on a whim — it's I/O-intensive. Always re-RUNSTATS afterwards.

Next Up

You can write fast DB2 queries now. Time to test it: DB2 Interview Questions.