Explain DB2 Performance Tuning Principles in IBM Mainframes

38 / 60

DB2advanced

DB2 Performance Tuning Principles

Bring together indexing, statistics, isolation levels, and SQL-writing discipline into a coherent approach to diagnosing and fixing slow DB2 queries.

"This query is slow" is a diagnosis problem with a fixed sequence, not a list of tricks. The sequence itself is what the question is testing.

Tuning a slow DB2 query is like diagnosing why a delivery route suddenly takes twice as long — you check the map app's chosen route first (EXPLAIN), then whether the map data is current (RUNSTATS/REORG), then whether a shortcut even exists (indexing), then whether the delivery instructions themselves are oddly worded and confusing the app (SQL rewrite), before finally considering whether it's just rush hour traffic everywhere (environmental/concurrency factors).

Key Concepts

1. EXPLAIN before anything. Find out what the optimiser actually chose. Everything after this depends on it, and changing things before knowing is how afternoons disappear.

EXPLAIN

2. Are the statistics current? A plan that looks wrong is usually a plan built from numbers that were right two years ago.

    RUNSTATS   ->  measure the table as it is now
    REBIND     ->  let the optimiser choose again

This is cheap, it is often the whole answer, and it requires no code change.

3. Is the table physically disorganised? Heavy update activity relocates rows and destroys clustering order, so a REORG may fix what looks like a query problem.

REORG

4. Is an index missing — or being defeated?

    WHERE YEAR(CREATED_DATE) = 2026        function on the column
    WHERE ACCT_NUM = 12345                 numeric literal, CHAR column
    WHERE NAME LIKE '%son'                 leading wildcard

Check MATCHCOLS in the plan. An index that is used but matches only its first column is doing a fraction of the work you assumed.

MATCHCOLS

5. Is the SQL asking for more than it needs?

    SELECT *                    every column, including ones you ignore
    DISTINCT "just in case"     forces a sort
    ORDER BY you do not use     forces another

6. Only then, the environment. Isolation level — is this reading at RR when CS would do, serialising everyone for no benefit? Buffer pools — is a hot table space competing for too little memory, so every access is physical I/O?

7. And the application shape. A program issuing a SELECT per row inside a loop is not a query problem. Ten thousand statements each taking 2 ms is twenty seconds of perfectly tuned SQL.

SELECT

Set-based SQL, or a bulk utility, replaces it.

What the answer should sound like. Not "I would add an index". That is step four of seven, it is the step with an ongoing cost, and it is wrong more often than the cheap steps above it.

The sequence is the answer: measure, check the statistics, check the physical state, check the predicates, check the SQL, then look wider. Each step is cheaper than the one after it, and each rules something out.

What the interviewer is probing.1. "Where do you start?" Probing: the method. Stalls: "Add an index." Moves up: find the expensive SQL from the accounting and statistics traces — tuning a statement nobody runs costs effort and buys nothing.

2. "What is usually the cause of a slow batch program?" Probing: experience. Stalls: "The database." Moves up: SQL inside a loop doing per-row access where a single set-based statement or a cursor would do, which is the mainframe version of N+1.

3. "Why does buffer pool tuning matter?" Probing: the lever. Stalls: "It does not." *Moves up:* a well-sized pool turns disk reads into memory hits, and separating pools by object type stops a scan flushing everything else.

4. "What is the cost argument for tuning here?" Probing: the commercial reality. Stalls: "Speed." Moves up: CPU is billed on the mainframe, so reducing getpages and CPU time shows up directly in the monthly software and hardware charge.