Db2 Catalog Guide: SYSIBM Tables, Packages, Indexes, and Statistics



Query catalog tables before changing objects.

When a COBOL Db2 job fails with an object, privilege, or package problem, the catalog is often the fastest place to confirm the facts. It can show whether a table exists, which columns it has, which indexes support it, when statistics were collected, and which package or plan is tied to application SQL.

The Db2 catalog is a set of Db2 tables, mostly under the SYSIBM schema, that records metadata about objects, authorizations, packages, plans, constraints, routines, communications, and optimizer statistics. Db2 updates many catalog rows when DDL, DCL, bind, or utility work changes the system.

Catalog versus directory

The catalog and directory are both used by Db2, but they serve different support roles. The catalog is queryable through SQL and is useful for developers, DBAs, and support teams. The directory contains internal control information that Db2 uses to run and recover the subsystem; it is not a normal application query target.

Area	What it contains	How support teams use it
Db2 catalog	Metadata about tables, columns, indexes, views, privileges, packages, plans, routines, constraints, and statistics.	Query with SQL to investigate object definitions, authorization, bind status, and access-path inputs.
Db2 directory	Internal Db2 control information needed for operation and recovery.	Managed by Db2 and DBA utilities; do not treat it as a normal reporting source.

Catalog tables developers often use

Catalog table	Typical question it answers
SYSIBM.SYSTABLES	Does this table, view, alias, or synonym exist, and who owns it?
SYSIBM.SYSCOLUMNS	What are the column names, data types, lengths, null rules, and column order?
SYSIBM.SYSINDEXES	Which indexes exist for a table, and are they unique or clustering indexes?
SYSIBM.SYSKEYS	Which columns form an index key, and in what order?
SYSIBM.SYSTABLESPACE	Which table spaces exist, and which database owns them?
SYSIBM.SYSTABAUTH	Which table or view privileges are granted?
SYSIBM.SYSUSERAUTH	Which system-level authorities are recorded for an authorization ID?
SYSIBM.SYSPACKAGE	Which packages exist for an application, collection, or version?
SYSIBM.SYSPACKSTMT	Which SQL statements are associated with a package?
SYSIBM.SYSROUTINES	Which stored procedures and user-defined functions exist?

Catalog queries for daily support

Find columns for a table

SELECT NAME,
       COLTYPE,
       LENGTH,
       NULLS,
       COLNO
  FROM SYSIBM.SYSCOLUMNS
 WHERE TBOWNER = 'APP1'
   AND TBNAME  = 'ACCOUNT'
 ORDER BY COLNO;

This is a quick check before changing a COBOL copybook, DCLGEN member, or host variable definition.

List indexes for a table

SELECT I.NAME,
       I.CREATOR,
       I.UNIQUERULE,
       I.CLUSTERING
  FROM SYSIBM.SYSINDEXES I
 WHERE I.TBCREATOR = 'APP1'
   AND I.TBNAME    = 'ACCOUNT'
 ORDER BY I.NAME;

Use this before reviewing an access path or explaining why a predicate is not using the expected index.

Check package existence

SELECT COLLID,
       NAME,
       VERSION,
       VALID,
       OPERATIVE
  FROM SYSIBM.SYSPACKAGE
 WHERE COLLID = 'APP1COLL'
   AND NAME   = 'ACCTUPD';

This helps when a runtime failure points to a missing, invalid, or wrong collection package.

Catalog data used by the optimizer

Db2 uses catalog statistics when it chooses access paths for static and dynamic SQL. RUNSTATS updates statistics such as table cardinality, index cardinality, column distribution, and partition-level information. Stale statistics can lead Db2 to pick a poor access path even when the SQL text has not changed.

Catalog area	Why it matters
SYSTABLES and related statistics rows	Table size and organization influence access-path choice.
SYSINDEXES and key statistics	Index availability, uniqueness, and clustering affect predicate access.
SYSCOLDIST	Column distribution data can help Db2 estimate filter factors for skewed values.
History statistics tables	Useful for comparing recent statistics changes when a query changed behavior after maintenance.

Catalog safety rules

• Use catalog SELECT queries for investigation; do not update catalog tables directly unless IBM documentation and site procedure explicitly allow a specific task.
• Prefer Db2 DDL, DCL, BIND, REBIND, RUNSTATS, and utilities to make supported changes.
• Use qualified names, especially owner, creator, database, table space, collection, and package name.
• Check the Db2 subsystem before comparing test and production catalog rows.
• Save catalog query output when it explains a production incident or release issue.

Common support scenarios

SQLCODE says an object is not found

Check SYSTABLES, SYSTABLESPACE, package collection, qualifier, and bind options. A table can exist in one subsystem or schema while the program is bound to another.

A query changed after RUNSTATS

Check catalog statistics and package bind time. If a rebind occurred after statistics changed, the package might have a new access path.

A user cannot run a query

Check table privileges, system privileges, role or group handling, and whether the application runs under a different authorization ID than the interactive user.

Related Db2 topics

Use this guide with Db2 Directory, Db2 Packages, Db2 Indexing, Db2 Utilities, and Db2 Optimizer.

FAQ

What is the Db2 catalog?

The Db2 catalog is a set of Db2 tables that stores metadata about objects, columns, indexes, privileges, packages, plans, routines, constraints, communications, and optimizer statistics.

Can developers query the Db2 catalog?

Yes, when they have the required authority. Catalog SELECT queries are common for checking object definitions, package status, privileges, and statistics.

Should catalog tables be updated manually?

No for normal work. Use supported Db2 statements, bind commands, utilities, and DBA procedures. Direct catalog updates are dangerous unless a documented IBM or site procedure specifically requires them.

Db2 Sort Pool and DSNDB07: Work Files, Sort Spills, and Tuning Checks

Sorts spill to DSNDB07 when memory is not enough.



A query with ORDER BY, GROUP BY, DISTINCT, or a join that cannot use a useful index may force Db2 to sort rows. If the sort cannot stay in memory, Db2 writes intermediate ordered runs to work files in DSNDB07, then reads them back and merges them. That extra write and read path is where many slow reports and batch steps start to hurt.

The Db2 sort pool is memory in the DBM1 address space used during sort processing. It works with sort algorithms, work files, buffer pools, SQL access paths, and catalog statistics. Tuning sort problems means checking more than one knob.

What the Db2 sort pool does

At startup, Db2 allocates sort-related memory in the private area of DBM1. Db2 can sort data in memory when the input size and access path allow it. If the sort is too large, Db2 creates sorted intermediate runs and uses work files, commonly associated with DSNDB07, to complete the merge work.

The goal is not to remove every sort. Some sorts are expected. The goal is to avoid unnecessary large sorts, give required sorts enough resources, and prevent work files from becoming a bottleneck.

SQL patterns that often cause sorts

SQL pattern	Why Db2 may sort	What to check
ORDER BY	Rows must be returned in a requested order.	Index key order, descending columns, and whether the access path already provides order.
GROUP BY	Rows must be grouped before aggregate output can be produced.	Grouping columns, filter predicates, and matching indexes.
DISTINCT	Duplicate rows must be removed.	Whether duplicate removal is needed or can be avoided by better predicates.
Join processing	Certain join methods may need sorted input.	Join columns, statistics, join order, and available indexes.
Union processing	UNION removes duplicates, unlike UNION ALL.	Whether duplicate removal is required by the business rule.

Example: avoiding an unnecessary sort

This query may sort if no useful index supports the predicate and requested order:

SELECT ACCT_NO,
       ACCT_STATUS,
       OPEN_DATE
  FROM ACCOUNT
 WHERE BRANCH_ID = :WS-BRANCH
 ORDER BY OPEN_DATE;

An index such as (BRANCH_ID, OPEN_DATE) may let Db2 filter and return rows in order, depending on the rest of the access path and statistics. Do not add indexes blindly; confirm with EXPLAIN, cardinality, update cost, and workload impact.

What happens when the sort spills

When the sort pool is not enough, Db2 writes sorted runs to work files. Later, Db2 reads the runs back and merges them. If work files are undersized, poorly distributed, or backed by slow I/O, the query can spend much of its time outside the core table access path.

• More rows entering the sort means more memory and work-file pressure.
• Bad statistics can cause Db2 to choose an access path that sorts more data than expected.
• Missing or mismatched indexes can force sorts for ordering or grouping.
• Work-file contention affects other SQL running at the same time.

Tuning checks for sort problems

Check	Why it matters
EXPLAIN output	Shows whether the access path needs a sort and which predicates drive the row count.
RUNSTATS freshness	Stale statistics can make Db2 underestimate sort size or pick a poor join order.
Index design	Index key order can reduce or remove sorts for common ORDER BY and GROUP BY paths.
DSNDB07 sizing	Work files need enough space and distribution for concurrent sort and temporary table work.
Buffer pool behavior	Work-file I/O can stress related buffer pools and storage paths.
SQL row reduction	Filtering earlier means fewer rows enter the sort.

Signs the work files are hurting performance

• A report or batch cursor runs fast for small input but slows sharply for large date ranges.
• EXPLAIN shows a sort where the developer expected an index-ordered result.
• Multiple large queries run at the same time and contend for work-file resources.
• Utility, temporary table, and SQL sort work overlap during a busy batch window.

Safe support workflow

1. Confirm the SQL text and host variable values used by the slow job.
2. Run or review EXPLAIN for the package or dynamic statement.
3. Check whether RUNSTATS is current for the table spaces and indexes involved.
4. Review whether an index can support filtering, joining, ordering, or grouping.
5. Ask the DBA team to check work-file sizing and buffer pool pressure when spills are likely.

Related Db2 topics

Use this article with Db2 Optimizer, Db2 Indexing, Db2 Utilities, Db2 Catalog, and Db2 SQL Optimization Tips.

FAQ

What is the Db2 sort pool?

The Db2 sort pool is memory used by Db2 during SQL sort processing. If a sort cannot complete in memory, Db2 can write intermediate runs to work files and merge them later.

What is DSNDB07 used for?

DSNDB07 is commonly associated with Db2 work-file processing, including sort runs and other temporary work needed by SQL and utilities.

How can I reduce large Db2 sorts?

Review EXPLAIN output, update statistics with RUNSTATS where needed, filter rows earlier, check index key order, and confirm work-file capacity with the DBA team.

Db2 Utilities Guide: COPY, RUNSTATS, REORG, LOAD, UNLOAD, and RECOVER



Use the right utility for the table space state.

A Db2 table space can be perfectly designed and still cause trouble if utilities are skipped. A missing image copy can block recovery, old statistics can lead to bad access paths, and a badly timed REORG can collide with batch work that needs the same object.

Db2 utilities are the operational tools used to load data, collect statistics, reorganize objects, create recovery copies, check consistency, and recover damaged or lost data. On z/OS, these jobs are usually controlled through JCL and utility control statements.

Common Db2 utilities and when to use them

Utility	Main purpose	Typical trigger
COPY	Create an image copy for recovery	Before risky change, after load, or on a backup schedule
RUNSTATS	Update catalog statistics for the optimizer	After large data change, new index, load, or REORG
REORG	Reorganize data or indexes and reclaim space	Poor clustering, high disorganization, or space issues
LOAD	Load high-volume input data into a table	Initial load, refresh, conversion, or warehouse feed
UNLOAD	Extract table data into a sequential data set	Archive, migration, test data, or reload process
RECOVER	Restore an object using image copies and logs	Object damage, application error, or point-in-time recovery plan
CHECK DATA	Check referential and table check constraints	After load, repair, or data movement where integrity needs proof
REBUILD INDEX	Rebuild index structures	Damaged index, recovery action, or index rebuild requirement

COPY is for recovery images

The Db2 COPY utility creates image copies of table spaces or index spaces. It is not the same thing as copying rows from one table to another with SQL. A recovery plan depends on these image copies and the logs that follow them.

//COPYTS  EXEC DSNUPROC,SYSTEM=DSN1,UID='MF.COPY'
//SYSIN   DD *
  COPY TABLESPACE APPDB.TSORD
       FULL YES
       SHRLEVEL CHANGE
/*

Schedule image copies around business risk. For example, take a copy after a successful high-volume load so recovery does not have to replay a large amount of log activity from an older copy.

RUNSTATS supports access path choices

RUNSTATS updates catalog statistics that the Db2 optimizer uses for access path selection. A COBOL program with static SQL normally needs bind or rebind activity before changed statistics can affect the package access path.

//RSTAT   EXEC DSNUPROC,SYSTEM=DSN1,UID='MF.RUNSTATS'
//SYSIN   DD *
  RUNSTATS TABLESPACE APPDB.TSORD
       TABLE(ALL)
       INDEX(ALL)
       SHRLEVEL CHANGE
/*

Run it after major data shifts, new indexes, table reorganizations, and bulk loads. Do not treat it as decoration at the end of a job stream; stale statistics can send the optimizer toward a costly path.

REORG cleans up physical layout

REORG can restore clustering order, reclaim space, and rebuild object layout. It is often paired with RUNSTATS and sometimes followed by package rebind when access paths should be reviewed.

//REORG   EXEC DSNUPROC,SYSTEM=DSN1,UID='MF.REORG'
//SYSIN   DD *
  REORG TABLESPACE APPDB.TSORD
       SHRLEVEL CHANGE
/*

Plan REORG around availability and logging impact. A small reference table and a multi-billion-row transaction table do not have the same utility window.

LOAD and UNLOAD move data at scale

LOAD is used when a large input data set has to be inserted into Db2 faster than ordinary row-by-row application processing. UNLOAD extracts data to a sequential data set for archive, migration, testing, or reload processing.

//LOADTS  EXEC DSNUPROC,SYSTEM=DSN1,UID='MF.LOAD'
//SYSREC  DD DSN=APP.INPUT.ORDERS,DISP=SHR
//SYSIN   DD *
  LOAD DATA INDDN SYSREC
       INTO TABLE APP.ORDERS
/*

After a LOAD, check whether the object needs COPY, RUNSTATS, constraint checks, and package rebind. The right answer depends on the options used and local recovery rules.

RECOVER is the restore path

RECOVER uses image copies and logs to restore Db2 objects. It should be rehearsed before the outage, not first learned during one. Keep utility JCL, copy availability, log retention, and recovery point rules visible in the runbook.

//RECOV   EXEC DSNUPROC,SYSTEM=DSN1,UID='MF.RECOVER'
//SYSIN   DD *
  RECOVER TABLESPACE APPDB.TSORD
/*

For point-in-time work, coordinate application owners, dependent objects, RI relationships, and downstream files. Recovering one object can be technically correct and still break a business process if related data is out of sync.

Utility checklist before production

• Confirm object name, database, table space, and subsystem before submitting utility JCL.
• Check whether the utility needs outage time or can run with SHRLEVEL CHANGE.
• Review image copy requirements before and after high-risk utilities.
• Run or schedule RUNSTATS when data distribution or indexes changed.
• Check whether static SQL packages need rebind after statistics or index changes.
• Keep utility output with the change record so failures and warnings are not lost.

Related DB2 topics

Utilities connect directly to Db2 Table Spaces, Db2 Indexing, Db2 Optimizer, Db2 Binding and Rebinding, and Db2 Commands Quick Reference.

FAQ

Is Db2 COPY used to copy rows between tables?

No. In the Db2 utility context, COPY creates image copies used for recovery. Row movement between tables is handled by SQL, LOAD/UNLOAD processes, or application logic.

Should RUNSTATS be run after every LOAD?

Often yes, especially when row counts or data distribution changed. The exact schedule depends on local standards, LOAD options, and whether related static packages will be rebound.

Does REORG always improve SQL performance?

No. REORG can help when physical layout, clustering, or space use is hurting access paths, but the value depends on the object and workload. Review utility reports and EXPLAIN evidence.

For utility work, the safest habit is simple: know the object state before the job, read the utility output after the job, and record what changed.

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 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.