Db2 Optimizer Guide: RUNSTATS, EXPLAIN, Indexes, and Access Paths



Optimizer choices depend on SQL, statistics, and indexes.

A batch job can read ten rows or ten million rows from the same table depending on the access path chosen for one SQL statement. The Db2 optimizer is the part of Db2 that evaluates the SQL, catalog statistics, indexes, predicates, and bind options before choosing how to access the data.

For COBOL programs with static SQL, the access path is usually chosen during bind or rebind. That is why a performance fix often includes more than changing the SQL text. RUNSTATS, indexes, predicate form, and bind timing all matter.

What the Db2 optimizer does

The optimizer estimates the cost of possible access paths and chooses one for the SQL statement. It may choose an index access, a table space scan, a nested loop join, a sort, or another method depending on the data and the SQL shape.

Input	How it affects access path choice
SQL text	Predicates, joins, subqueries, sorting, grouping, and selected columns change the possible paths.
Catalog statistics	RUNSTATS data helps Db2 estimate table size, index usefulness, column distribution, and filter factor.
Indexes	Matching columns, clustering, uniqueness, and index-only access can change the selected path.
Bind options	Static SQL access path selection is tied to bind or rebind, so timing and options matter.
Host variables	For static SQL, Db2 may not know the actual runtime value during bind, so predicate design is important.

RUNSTATS gives the optimizer better facts

RUNSTATS collects information about table spaces, tables, indexes, and column values. Without current statistics, the optimizer can choose a path based on old assumptions. A table that grew from 50,000 rows to 50 million rows should not be planned as if it were still small.

RUNSTATS TABLESPACE APPDB.TSORD
  TABLE(ALL)
  INDEX(ALL)
  SHRLEVEL CHANGE

For static SQL, updated statistics do not automatically change an existing package access path. The package normally needs a rebind for Db2 to choose paths using the new statistics. For dynamic SQL, updated statistics can affect later prepares.

Indexes help only when predicates can use them

An index on CUSTOMER_ID is useful when the SQL searches by CUSTOMER_ID in a form Db2 can match. The same index may not help much if the program wraps the column in a function or uses a predicate that prevents matching index access.

-- Better starting point for an indexed CUSTOMER_ID
SELECT CUSTOMER_NAME, CITY
FROM CUSTOMER
WHERE CUSTOMER_ID = :WS-CUSTOMER-ID;

-- Often harder for index matching
SELECT CUSTOMER_NAME, CITY
FROM CUSTOMER
WHERE SUBSTR(CUSTOMER_ID,1,5) = :WS-CUST-PREFIX;

The second query may still be valid SQL, but it asks Db2 to evaluate an expression on the column. If that query is on a high-volume path, review the predicate and index design before accepting the access path.

EXPLAIN shows what Db2 selected

EXPLAIN writes access path information to explain tables such as PLAN_TABLE. It lets a developer or DBA check whether Db2 plans to use an index, scan a table space, sort rows, or join tables in a certain order.

EXPLAIN PLAN FOR
SELECT ORDER_NO, ORDER_DATE
FROM ORDERS
WHERE CUSTOMER_ID = 'C00001234'
  AND ORDER_DATE >= DATE('2026-01-01');

EXPLAIN output should be reviewed with the real table design and statistics in mind. A table space scan is not always bad, and an index access is not always good. The question is whether the selected path fits the row counts, filter factors, and job runtime target.

Static SQL, bind, and rebind

In a COBOL static SQL program, the DBRM is bound into a package or plan. That bind step is where Db2 selects access paths for the static statements. If a table grows, an index changes, or RUNSTATS is refreshed, the package may need a rebind to pick up a better path.

This is why a release checklist should include both application build evidence and database performance evidence. A program can pass functional testing while still carrying an old access path that hurts the nightly batch window.

Optimizer checklist for COBOL SQL

• Run or confirm RUNSTATS after major load, purge, or reorganization activity.
• Check whether static packages need rebind after statistics or index changes.
• Use EXPLAIN for SQL that reads high-volume tables or appears in long-running jobs.
• Keep predicates simple enough for matching index access where the business rule allows it.
• Review host variable data types so Db2 does not have to handle avoidable conversions.
• Check ORDER BY, GROUP BY, DISTINCT, and join predicates for sort and join cost.

Common optimizer surprises

Symptom	What to check
A job slows down after a large data load	RUNSTATS timing, package rebind, and whether the old access path assumed smaller tables.
An index exists but Db2 scans the table space	Predicate form, column order in the index, filter factor, and whether the scan is actually cheaper.
A query sorts a large work file	ORDER BY, GROUP BY, DISTINCT, index order, and selected columns.
Two similar programs perform very differently	Package bind time, collection, bind options, host variable types, and statement text differences.

Related DB2 topics

The optimizer connects directly to Db2 SQL Optimization Tips for COBOL Programs, Db2 Indexing, Db2 Binding and Rebinding, Db2 Binding an Application, and Db2 Data Types.

FAQ

Does RUNSTATS change a static SQL access path by itself?

No. RUNSTATS updates statistics. For static SQL, a package generally needs a bind or rebind before Db2 can choose a new access path from those statistics.

Is index access always faster than a table space scan?

No. If a query reads a large part of the table, a scan can be cheaper than many index lookups. EXPLAIN and real row counts matter.

Why can the same SQL run differently in test and production?

Statistics, table size, indexes, bind time, bind options, and host variable values can differ between environments. Compare the package and EXPLAIN details before changing code.

For slow SQL, start with the access path evidence. Guessing from the SQL text alone is how small problems grow into long batch nights.