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.