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.