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 Objects Guide: Tables, Indexes, Views, Sequences, and Packages

Db2 objects are the named pieces SQL uses.

A COBOL program that runs EXEC SQL SELECT depends on more than one table name. Behind that statement are Db2 objects: a database, table space, table, columns, indexes, views, packages, and sometimes sequences or aliases. If one of those objects is missing, changed, or rebound incorrectly, the application can fail before it fetches a single row.

This refresh explains common Db2 objects from a mainframe application point of view. It keeps the broad object-reference intent, while linking out to the focused articles for table spaces, packages, views, indexing, and sequences.

What Is a Db2 Object?

A Db2 object is a named database item that Db2 can create, store, reference, secure, or use while processing SQL. Some objects hold data, some describe access paths, and some control how applications run static SQL.

A developer usually meets these objects through DDL, embedded SQL, bind jobs, promotion scripts, and catalog queries.

Db2 Object Map

Object	Main purpose	Where a developer sees it
Database	Logical container for table spaces and index spaces.	DDL, standards, storage planning.
Table space	Stores table data in Db2-managed structures.	CREATE TABLESPACE, storage, partitioning.
Table	Stores rows and columns.	SELECT, INSERT, UPDATE, DELETE.
Index	Supports access paths and uniqueness.	EXPLAIN, tuning, unique constraints.
View	Presents a named SELECT over tables or views.	Security, simplified SQL, reporting.
Sequence	Generates numeric values.	INSERT processing and surrogate keys.
Package	Stores bound static SQL for a program.	BIND PACKAGE, REBIND, promotion.
Storage group	Defines storage choices for physical objects.	DBA-managed storage definitions.

Database

In Db2 for z/OS, a database is a logical grouping for table spaces and index spaces. It is not the same as a separate server or subsystem. A database can provide defaults and ownership structure for dependent objects.

CREATE DATABASE CUSTDB;

Application developers may not create databases every day, but database names appear in DDL reviews, migration scripts, and object naming standards.

Table Space

A table space is where table data is stored. Table space design affects space, partitioning, locking behavior, utilities, and performance operations. It is one of the key places where logical design meets physical storage.

CREATE TABLESPACE CUSTTS
  IN CUSTDB
  USING STOGROUP SYSDEFLT;

For a deeper treatment of PBG, PBR, page size, and partitioning choices, see Db2 Table Spaces Guide.

Table

A table stores rows and columns for one subject. Tables are the objects that most SQL statements directly reference.

CREATE TABLE CUSTOMER
 (CUST_NO     CHAR(10) NOT NULL,
  CUST_NAME   VARCHAR(60),
  STATUS      CHAR(1),
  PRIMARY KEY (CUST_NO));

The logical structure of tables, rows, columns, and keys is covered in Db2 Relational Database Anatomy.

Column and Data Type

Columns are part of a table definition. Each column has a data type, and that data type matters when a COBOL host variable receives or sends a value.

EXEC SQL
    SELECT CUST_NAME,
           STATUS
      INTO :WS-CUST-NAME,
           :WS-STATUS
      FROM CUSTOMER
     WHERE CUST_NO = :WS-CUST-NO
END-EXEC.

The host variables in the program must be compatible with the Db2 column definitions.

Index

An index is an object based on one or more table columns. Db2 can use indexes to avoid scanning a whole table, enforce uniqueness, support clustering, and improve join access.

CREATE INDEX IX_ACCOUNT_CUST
  ON ACCOUNT (CUST_NO);

An index is not a guarantee of speed by itself. Db2 chooses access paths based on SQL predicates, statistics, indexes, and cost. The related Db2 Indexing article covers this in more detail.

View

A view is a named SQL definition. It can hide columns, join tables, apply filters, or give a program a stable query shape while base tables change behind the view.

CREATE VIEW ACTIVE_CUSTOMER_V AS
SELECT CUST_NO,
       CUST_NAME
  FROM CUSTOMER
 WHERE STATUS = 'A';

Views can be simple, joined, aggregate, read-only, or updatable depending on their definition. See Db2 View Classification for the practical types.

Alias and Synonym

Aliases and synonyms let SQL reference another object through a different name. They are often used to simplify names or hide location details. In older Db2 notes, aliases and synonyms are often discussed together, but their scope and behavior are not identical.

For application teams, the main check is simple: confirm what object the name resolves to before changing SQL, grants, or deployment scripts.

Sequence

A sequence is a Db2 object that generates numeric values. Programs often use sequences when inserting rows that need generated identifiers.

INSERT INTO ORDER_HDR
       (ORDER_ID, CUST_NO, ORDER_DATE)
VALUES (NEXT VALUE FOR ORDER_SEQ,
        :WS-CUST-NO,
        CURRENT DATE);

For sequence options such as CACHE, NO CACHE, CYCLE, and NEXT VALUE FOR, see Db2 Sequences.

Package and Plan

Packages and plans are application-facing Db2 objects. A package contains bound static SQL for a program. A plan can include packages and is used at runtime by applications.

BIND PACKAGE(COLLID) MEMBER(PROG1) ACTION(REPLACE)
BIND PLAN(APPPLAN) PKLIST(COLLID.PROG1)

When a COBOL program changes its SQL, the DBRM and package path matters. The Db2 Packages Guide for COBOL Static SQL covers DBRM, BIND PACKAGE, REBIND, collections, and promotion checks.

Storage Group and Index Space

Storage groups and index spaces are usually DBA-facing objects. A storage group defines storage choices. An index space holds index data. Application developers do not normally change them in COBOL work, but these objects appear during DDL review, utility planning, and performance discussions.

Materialized Query Table

A materialized query table stores the result of a query definition. It can help avoid repeated expensive joins or aggregations when the design and refresh rules fit the workload. Use MQTs only when the maintenance cost is justified by the query savings.

Object Dependencies

Db2 objects depend on each other. A view depends on its base tables. A package depends on the SQL it was bound with. An index depends on a table. A table depends on a table space.

If this changes	Check this next
Table column definition	Views, packages, COBOL copybooks, host variables.
Index	EXPLAIN output, access paths, RUNSTATS.
Table space	Utilities, storage, partitioning, recovery process.
Package	Bind options, collection, plan/package list, runtime job.

Common Mistakes

• Calling every named item a table when it might be a view, alias, or synonym.
• Changing a column without checking packages and COBOL host variables.
• Adding an index without running statistics or checking the access path.
• Confusing database, table space, and table as if they were the same level.
• Refreshing a package in the wrong collection during promotion.

FAQ

What are the main Db2 objects a COBOL developer should know?

A COBOL developer should know tables, columns, indexes, views, sequences, packages, plans, table spaces, and the basic dependency between those objects.

Is a Db2 package a database object?

Yes. A package is a Db2 object that stores bound static SQL for an application program. It is central to COBOL static SQL execution.

What is the difference between a table and a table space?

A table is the logical object that stores rows and columns. A table space is the storage object that holds table data.

When you know which object you are changing, you know what to check next: SQL text, DDL, indexes, packages, utilities, or COBOL host variables.

Db2 SQL Optimization Tips for COBOL Programs



Db2 SQL tuning starts with access-path evidence.

A batch job named ACCTPOST can look healthy in test and still burn CPU in production when a predicate stops matching an index or catalog statistics are old. In Db2 for z/OS, SQL tuning is not guesswork. Start with the SQL text, host-variable definitions, catalog statistics, and the access path that Db2 selected.

This guide keeps the original post's intent - practical SQL performance tips for Db2 developers - but updates it for a COBOL + Db2 audience. It focuses on what application developers can change safely before asking a DBA to add an index or change database design.

Start With EXPLAIN, Not Opinions

Before rewriting a query, capture the access path. IBM describes EXPLAIN as the Db2 statement that records access-path information for explainable SQL statements in EXPLAIN tables. Use it to check whether Db2 chose an index access, tablespace scan, sort, nested-loop join, merge scan join, or other path.

For dynamic SQL, also look at statement-cache information when it is available. For static COBOL SQL, compare access paths before and after a package rebind, especially after RUNSTATS, index changes, or predicate rewrites.

EXPLAIN PLAN SET QUERYNO = 101
FOR
SELECT CUST_NO,
       BALANCE
  FROM ACCT_BAL
 WHERE BRANCH_ID = 'D01'
   AND STATUS    = 'A'
   AND BALANCE   > 10000;

Do not tune from elapsed time alone. Elapsed time can change because of locking, buffer pool residency, batch window load, or another job holding resources. EXPLAIN tells you what Db2 planned to do.

Keep RUNSTATS Current

The optimizer depends on statistics. IBM's Db2 documentation says RUNSTATS gathers information about table spaces, indexes, and partitions, records that information in the Db2 catalog, and uses it during access-path selection at bind time.

Old statistics can make a good SQL statement look bad. A table that had 10,000 rows last year may have 40 million rows now. A column that used to have five status values may now be skewed, with 90 percent of rows in one status. Without current statistics, Db2 can choose an access path that looks cheap on paper and expensive in production.

Symptom	Check	Likely action
Query changed from index access to scan after data growth.	Table, index, and column statistics date.	Run RUNSTATS and rebind static packages when your site process requires it.
Predicate on a skewed column picks the wrong access path.	Frequency and cardinality statistics.	Collect distribution statistics for the right columns or column groups.
Join order looks wrong.	Cardinality of join columns and indexes.	Refresh statistics and review index design.

Make Predicates Indexable

The fastest SQL is often the statement that lets Db2 reject rows early. Avoid wrapping indexed columns in scalar functions inside the WHERE clause when a range predicate can express the same rule.

-- Weak predicate for an index on HIREDATE
WHERE YEAR(HIREDATE) = 2026

-- Better range predicate
WHERE HIREDATE >= DATE('2026-01-01')
  AND HIREDATE <  DATE('2027-01-01')

The second form leaves HIREDATE by itself on the left side of the comparison. If a useful index exists and statistics support it, Db2 has a better chance of using matching index access.

Move Arithmetic Away From Indexed Columns

Arithmetic on an indexed column can stop Db2 from using the index in the way you expect. Move the calculation to the constant or host-variable side when the business rule is the same.

-- Weak
WHERE SALARY * 1.10 > :WS-LIMIT

-- Better
WHERE SALARY > :WS-LIMIT / 1.10

Do this only when the rewrite keeps the same rounding and data type behavior. For packed-decimal or decimal columns, confirm the scale of the host variables and test boundary values such as exactly 50000.00.

Select Only the Columns the Program Uses

SELECT * makes the program read columns that it may never move to an output record. Extra columns can increase I/O, enlarge sort work, prevent index-only access, and make FETCH handling slower in COBOL.

-- Weak
SELECT *
  FROM CUSTOMER
 WHERE CUST_NO = :WS-CUST-NO

-- Better
SELECT CUST_NO,
       CUST_NAME,
       STATUS
  FROM CUSTOMER
 WHERE CUST_NO = :WS-CUST-NO

If the selected columns are all in an index, Db2 may be able to avoid reading the data page. For index design basics, see DB2 Indexing.

Use DISTINCT Only When Duplicates Are Real

DISTINCT can require sort or duplicate elimination work. Do not add it as a defensive habit. If duplicates appear because a one-to-many table is joined only to test existence, an EXISTS predicate can express the intent more clearly.

-- Often expensive if many project rows exist per employee
SELECT DISTINCT E.EMPNO,
       E.LASTNAME
  FROM EMP E,
       EMPPROJACT P
 WHERE P.EMPNO = E.EMPNO

-- Clear existence check
SELECT E.EMPNO,
       E.LASTNAME
  FROM EMP E
 WHERE EXISTS
       (SELECT 1
          FROM EMPPROJACT P
         WHERE P.EMPNO = E.EMPNO)

Do not assume the rewrite is faster every time. Explain both statements. Data distribution, indexes, and query transformation can change the result.

Test IN and EXISTS Both Ways

IN and EXISTS can return the same rows while giving Db2 different rewrite choices. For a small lookup list, IN may be clear. For a correlated existence check, EXISTS often states the access rule better.

SELECT E.EMPNO,
       E.LASTNAME
  FROM EMP E
 WHERE EXISTS
       (SELECT 1
          FROM DEPARTMENT D
         WHERE D.MGRNO = E.EMPNO
           AND D.DEPTNO LIKE 'D%')

For production tuning, compare EXPLAIN output and real test data. A rewrite that wins on a 10-row test table may lose when the production table has millions of rows and skewed department codes.

Match COBOL Host Variables to Db2 Columns

A COBOL host variable should match the Db2 column type as closely as possible. Mismatched types can force conversion work and can affect predicate matching. Use DCLGEN output instead of hand-written copybook fields when your site allows it.

Db2 column	COBOL host variable pattern	Risk when mismatched
INTEGER	PIC S9(9) COMP or site-standard binary equivalent	Conversion or range issues.
DECIMAL(9,2)	PIC S9(7)V99 COMP-3	Scale or rounding errors at predicate boundaries.
CHAR(10)	PIC X(10)	Padding and comparison surprises.
DATE	PIC X(10) in ISO form, or the site standard date host variable	Invalid date strings or nonmatching formats.

When tuning a COBOL program, review the copybook before blaming Db2. The related DB2 Host Variables and Structures post is a useful companion.

Be Careful With OR Logic

OR can make predicate evaluation harder, especially when one branch is indexable and another is not. Sometimes the same logic can be expressed with common predicates factored out.

-- Harder to read and tune
WHERE (ADMRDEPT = 'E01' AND DEPTNAME LIKE 'BRANCH%')
   OR (DEPTNO   = 'D01' AND DEPTNAME LIKE 'BRANCH%')

-- Same rule with common predicate moved once
WHERE (ADMRDEPT = 'E01' OR DEPTNO = 'D01')
  AND DEPTNAME LIKE 'BRANCH%'

Another option is UNION ALL when each branch can use a different strong index and duplicates are not possible or can be handled deliberately. Always test the rewrite with production-like data.

Limit Rows Early

A COBOL program that needs 50 rows should not fetch 50,000 rows and stop in application code. Put the row limit and ordering rule in SQL when the business result allows it.

SELECT CUST_NO,
       BALANCE
  FROM ACCT_BAL
 WHERE STATUS = 'A'
 ORDER BY BALANCE DESC
 FETCH FIRST 50 ROWS ONLY

For repeated batch processing, also consider multi-row fetch when the SQL returns many rows and the program processes them sequentially. See COBOL DB2 Multi-Row Fetch for the rowset pattern.

Use SQL Diagnostics When Tuning Batch Jobs

Tuning work often needs simple instrumentation. Record row counts after update, delete, insert, or fetch statements. GET DIAGNOSTICS ROW_COUNT gives the program a direct count for many statement types.

EXEC SQL
    GET DIAGNOSTICS :WS-ROW-COUNT = ROW_COUNT
END-EXEC.

That count helps separate an access-path problem from a data-volume problem. If a nightly job suddenly updates 4 million rows instead of 40,000, the first question is why the qualifying row count changed. For details, see Db2 GET DIAGNOSTICS Statement Information Items.

Db2 SQL Tuning Checklist

• Capture EXPLAIN before and after the rewrite.
• Check RUNSTATS age for tables, indexes, and key columns.
• Keep indexed columns bare in predicates where possible.
• Move scalar functions and arithmetic away from predicate columns.
• Select only columns the COBOL program uses.
• Remove defensive DISTINCT when duplicate rows are not possible.
• Compare IN, EXISTS, and join rewrites with real data.
• Match host variable data types to Db2 columns.
• Review OR predicates for indexability and possible rewrites.
• Limit rows in SQL when the program needs only a small ordered set.

FAQ

What is the first step in Db2 SQL tuning?

Capture the access path with EXPLAIN. Without access-path evidence, it is too easy to tune the wrong part of the statement.

Does RUNSTATS improve SQL performance by itself?

RUNSTATS updates catalog statistics. Db2 uses those statistics for access-path selection. Static SQL may also need the normal site rebind process before a package uses a new path.

Should every slow query get a new index?

No. First check predicates, selected columns, host variables, statistics, and access path. Add or change an index only when the access pattern justifies the extra storage and insert/update cost.

The best tuning change is the one you can explain from the access path, row counts, and data distribution. Make one change, capture the new EXPLAIN output, and keep the result tied to the production problem you are solving.