Db2 Indexing Guide: B-Tree, Composite, Clustering, and Index-Only Access



Index design starts with real WHERE clauses.

An index can turn a four-hour customer lookup step into a few minutes, but the wrong index can add insert cost, rebuild time, and no real benefit. Db2 indexing is not about adding indexes everywhere. It is about matching the index key to the SQL predicates, joins, sorting, and access path that the program really uses.

For COBOL programs with static SQL, index design also connects to RUNSTATS, EXPLAIN, bind, and rebind. A new index will not help a package that keeps using an old access path until the package is rebound.

What a Db2 index does

A Db2 index stores key values with pointers to rows. Db2 can use that structure to find a matching value, scan a range, support ordering, enforce uniqueness, or sometimes answer a query without reading the table data pages.

Index use	Example	Why it matters
Matching index access	WHERE CUSTOMER_ID = :WS-CUST-ID	Db2 can navigate to matching key values instead of scanning the table space.
Range scan	WHERE ORDER_DATE BETWEEN :WS-FROM AND :WS-TO	Db2 can start at one key and scan in key order.
Index-only access	Selecting only columns contained in the index	Db2 may satisfy the query from index pages without reading data pages.
Clustering	Rows physically ordered close to the index key	Range reads can touch fewer data pages when clustering is good.

B-tree indexes and matching scans

Db2 uses B-tree index structures for common indexing work. The leaf pages contain ordered key values and row identifiers. Db2 can find a starting key and then move through the leaf pages in key sequence.

CREATE INDEX IX_ORD_CUST_DATE
  ON ORDERS (CUSTOMER_ID, ORDER_DATE);

With that index, this query has a good chance of matching on CUSTOMER_ID and scanning the order date range for that customer.

SELECT ORDER_NO, ORDER_DATE, ORDER_TOTAL
FROM ORDERS
WHERE CUSTOMER_ID = :WS-CUSTOMER-ID
  AND ORDER_DATE >= :WS-FROM-DATE;

If the query only filters on ORDER_DATE, the same composite index may be much less useful because ORDER_DATE is the trailing column. Column order in a composite index is not decoration; it changes matching access.

Composite index column order

A composite index should follow the predicates that matter most in real SQL. Equality predicates usually make strong leading columns. Range predicates are useful, but once Db2 reaches a range condition, later columns may not help matching in the same way.

SQL pattern	Index starting point
WHERE ACCT_NO = ? AND POST_DATE BETWEEN ? AND ?	(ACCT_NO, POST_DATE)
WHERE BRANCH_NO = ? AND STATUS = ? AND OPEN_DATE >= ?	(BRANCH_NO, STATUS, OPEN_DATE)
WHERE STATUS = ? where most rows have the same status	An index may not help much unless combined with a more selective column.

Index-only access

Db2 can sometimes answer a query from the index alone when every needed column is in the index. This can reduce data page reads, especially for high-volume reporting queries.

CREATE INDEX IX_ORD_STATUS_DATE_TOTAL
  ON ORDERS (ORDER_STATUS, ORDER_DATE, ORDER_TOTAL);

SELECT ORDER_DATE, ORDER_TOTAL
FROM ORDERS
WHERE ORDER_STATUS = 'C'
  AND ORDER_DATE >= DATE('2026-01-01');

Index-only access is not free. Wider indexes cost more to maintain and can take more storage. Add columns to support real access paths, not because the index might help someday.

Clustering indexes

A clustering index guides the physical order of rows in a table space. Good clustering can help range queries because rows with nearby key values tend to live near each other. Poor clustering can make Db2 jump across many data pages even when the index key is useful.

Only one clustering order can be favored for a table, so choose it for the access pattern that matters most. For example, a table often read by ACCOUNT_NO and transaction date may benefit from clustering on those columns. A random unique key may enforce uniqueness but may not be the best clustering choice for range reads.

When not to add an index

• The table is tiny and a scan is cheaper than index navigation.
• The leading column has very low selectivity, such as a status column where nearly every row has the same value.
• The indexed columns are updated frequently and the query benefit is weak.
• The SQL wraps the indexed column in a function, preventing normal matching access.
• The index duplicates another index with the same useful leading columns.

Index maintenance costs

Every insert, delete, load, recovery, and some updates must maintain indexes. More indexes can mean slower write activity, larger utilities, longer rebuilds, and more storage. That cost is acceptable when the index protects a critical access path, but it should be visible in design reviews.

After large loads, purges, or reorganizations, check RUNSTATS and package rebind needs. The optimizer needs current statistics, and static SQL packages need a bind or rebind to choose paths from those statistics.

EXPLAIN before and after index changes

Use EXPLAIN to confirm what Db2 plans to do before and after adding an index. Do not assume that an index is used because it exists. The optimizer may still choose a table space scan, another index, a sort, or a different join order.

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

For static SQL in COBOL, compare the package access path after bind or rebind. A test query in SPUFI or another tool can be useful, but it is not the same evidence as the package used by the production program.

Indexing checklist for COBOL SQL

• Collect the actual SQL statements from the COBOL program, not only table names.
• Identify equality predicates, range predicates, join columns, and ORDER BY columns.
• Review host variable data types so indexed columns are compared to compatible values.
• Check existing indexes before proposing another one.
• Run EXPLAIN and compare access paths before and after the index change.
• Run RUNSTATS and rebind static packages when the change is ready for testing.

Related DB2 topics

Indexing ties directly to Db2 Optimizer, Db2 SQL Optimization Tips for COBOL Programs, Db2 Table Spaces, Db2 Data Types, and Db2 Binding and Rebinding.

FAQ

Should every Db2 table have an index?

No. Indexes should support real predicates, joins, ordering, uniqueness, or access paths. Small tables and low-selectivity columns may not benefit from another index.

Is a unique index always the best clustering index?

No. A unique index can enforce a rule, but clustering should favor the range reads and access pattern that matter most for the workload.

Does a new index help static SQL immediately?

Not always. For static SQL packages, run RUNSTATS as needed and bind or rebind the package so Db2 can choose an access path that uses the new index.

Index from the SQL outward: predicate, column order, statistics, EXPLAIN, bind, and then production runtime.

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.

Db2 Request to SQL: Translate Business Rules into SELECT Statements



Translate the request before writing the SELECT.

A request like "show active accounts with balances over 10,000" is not SQL yet. Before a COBOL program can use it in embedded SQL, the developer must identify the result columns, source tables, join rules, predicates, ordering, and host variables. Most bad SELECT statements start with a vague request that was never translated into database terms.

This refresh keeps the original beginner intent but rewrites it for Db2 and mainframe developers. The goal is not SQL tuning; the goal is turning a business question into a precise, testable SQL statement.

Start With the Business Request

Write the request in one sentence and remove soft wording. Replace words like "current," "recent," "large," or "active" with the actual rule used by the application.

Vague wording	Database rule
current customers	CUST_STATUS = 'A'
recent payments	PAYMENT_DATE >= :WS-FROM-DATE
large balances	BALANCE > 10000
open accounts	CLOSE_DATE IS NULL

Identify the Columns to Return

The SELECT list should contain the columns the program actually needs. If the report needs account number, customer name, and balance, name those columns. Do not use SELECT * in production COBOL cursors unless there is a rare, documented reason.

SELECT A.ACCT_NO,
       C.CUST_NAME,
       A.BALANCE

Choosing only the needed columns reduces data movement and keeps the COBOL host variable list clear.

Find the Source Tables

Next, map each result column to a table. If columns come from more than one table, write the join rule before writing the final query.

Needed value	Column	Table
Account number	ACCT_NO	ACCT_BAL
Balance	BALANCE	ACCT_BAL
Customer name	CUST_NAME	CUSTOMER
Customer status	CUST_STATUS	CUSTOMER

Add Join Rules

A join rule explains how rows from two tables match. Do not rely on column names that merely look similar. Confirm the relationship from the data model, DCLGEN copybooks, or existing tested SQL.

FROM ACCT_BAL A
JOIN CUSTOMER C
  ON C.CUST_NO = A.CUST_NO

If the relationship is optional, decide whether the query needs an inner join or a left join. A wrong join type can silently drop rows.

Convert Conditions into Predicates

Conditions become the WHERE clause. Each condition should have a column, operator, and value or host variable.

WHERE C.CUST_STATUS = 'A'
  AND A.BALANCE > :WS-MIN-BALANCE
  AND A.BRANCH_ID = :WS-BRANCH-ID

Use host variables for values that change at runtime. Match host variable definitions to Db2 column types. The related DB2 Host Variables and Structures article covers that copybook work.

Write the Final SELECT

After the request is translated into columns, tables, joins, and predicates, the final SQL is much easier to review.

SELECT A.ACCT_NO,
       C.CUST_NAME,
       A.BALANCE
  FROM ACCT_BAL A
  JOIN CUSTOMER C
    ON C.CUST_NO = A.CUST_NO
 WHERE C.CUST_STATUS = 'A'
   AND A.BALANCE > :WS-MIN-BALANCE
   AND A.BRANCH_ID = :WS-BRANCH-ID
 ORDER BY A.BALANCE DESC
 FETCH FIRST 100 ROWS ONLY

The SQL now says exactly what data is needed, where it comes from, how rows match, and which rows qualify.

COBOL Cursor Pattern

In COBOL, the translated SQL often becomes a cursor. Keep the cursor declaration close to the host variable definitions during review.

EXEC SQL
    DECLARE C-ACCT CURSOR FOR
    SELECT A.ACCT_NO,
           C.CUST_NAME,
           A.BALANCE
      FROM ACCT_BAL A
      JOIN CUSTOMER C
        ON C.CUST_NO = A.CUST_NO
     WHERE C.CUST_STATUS = 'A'
       AND A.BALANCE > :WS-MIN-BALANCE
       AND A.BRANCH_ID = :WS-BRANCH-ID
END-EXEC.

Check the SQLCODE after every OPEN, FETCH, and CLOSE. A correct SELECT still needs correct cursor handling.

Review Before Tuning

Before tuning a query, confirm it answers the right question. Then check access path and indexes. The companion Db2 SQL Optimization Tips for COBOL Programs guide covers EXPLAIN, RUNSTATS, and predicate checks.

• Are all selected columns required by the program?
• Are joins based on real relationships?
• Are date and status rules stated precisely?
• Are host variables typed correctly?
• Does the query need ordering or row limiting?

FAQ

What is the first step in translating a request into SQL?

Rewrite the business request as exact data rules: result columns, source tables, join relationships, and predicates.

Should I use SELECT * while translating a request?

No for production COBOL SQL. Name only the columns the program needs so host variables, performance, and review remain clear.

When should I tune the SQL?

Tune after the query answers the right business question. Then use EXPLAIN, current statistics, and index checks to review the access path.

A good SELECT starts as a precise requirement. Once the columns, tables, joins, and predicates are clear, the SQL is easier to test, bind, and maintain.