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.
No comments:
Post a Comment