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