Db2 work file database overview

What the work file database is

IBM defines the work file database as storage for processing SQL statements that require working space—such as the space required for a sort—and as storage for created global temporary tables and declared global temporary tables. At installation time, Db2 creates a work file database and some table spaces in it. You can create additional work file table spaces later, and you can drop, recreate, or alter the database and its spaces when capacity or layout must change.

In a non-data-sharing environment the name is DSNDB07. In a data sharing environment, each member has its own work file database. That distinction matters: shared user data and a shared catalog do not mean shared scratch pads. The member that runs the SQL owns the work files for that execution.

Think of work files as the subsystem’s whiteboard and scratch paper. They are not where you permanently store customer balances. They are where Db2 parks intermediate work so a single SQL statement—or a temporary table instance—can finish correctly under load.

What uses work file storage

Developers meet work files first through mysterious failures: “sort capacity exceeded,” sudden elapsed-time spikes after data growth, or temporary-table inserts that compete with overnight batch sorts. The SQL text may look fine. The missing ingredient is often enough concurrent work file space with the right page sizes.

Sorts and the memory story

Db2 prefers to sort in memory using sort-related pools when possible. When the working set does not fit, intermediate runs spill to work file table spaces. Larger sorts, more concurrent users sorting at once, and poorly filtered queries all increase pressure. Tuning is a partnership: rewrite SQL and indexes to reduce sort volume, and size work files for the residual spill you cannot eliminate.

1
2
3
4
5
6
7
8
-- Innocent-looking SQL can still need work files:
SELECT WORKDEPT, COUNT(*) AS EMP_COUNT
  FROM HR.EMPLOYEE
 GROUP BY WORKDEPT
 ORDER BY EMP_COUNT DESC;
 
-- Large joins, DISTINCT, and UNION similarly
-- may materialize intermediate results.

Temporary tables live here too

Created global temporary tables and declared global temporary tables use work file database storage. IBM notes you can use the work file database for all temporary tables. For declared temporary tables, you must have a work file database that includes at least one table space with a 32 KB page size. If that prerequisite is missing, DECLARE GLOBAL TEMPORARY TABLE fails even when DSNDB07 exists with only smaller pages.

Heavy CGTT or DGTT materialization therefore shares the same oxygen as sorts. Performance traces and monitoring that show work file saturation often point at temporary-table design as much as at ORDER BY clauses. The companion page on temporary tables goes deeper on CREATE versus DECLARE; here the takeaway is shared infrastructure.

Data sharing: one work file database per member

In a Parallel Sysplex data sharing group, members share the catalog, directory, and user table spaces on shared disks. They do not share a single DSNDB07 for all members. Each member keeps local work files sized for the SQL that lands on that member. Workload balancing can shift heavy sort or temp-table traffic between members, so capacity planning must consider peak-per-member behavior—not only group averages.

If you add a new member, part of standup is ensuring that member’s work file database exists, has suitable table spaces (including 32 KB if DGTTs are used), and is monitored like any other critical local resource.

Design and operations basics

Installation defaults are a starting point, not a forever contract. Growing warehouses, more concurrent online reporting, and wider use of temporary tables all justify additional work file table spaces. Prefer several spaces over one gigantic bottleneck when concurrency is high—Db2 can spread work more effectively when multiple spaces are available.

What DBAs watch

• Failures and messages that cite sort or work file shortages
• Elapsed time growth on known sort-heavy batch jobs after data volume jumps
• Temporary table usage patterns that coincide with work file contention
• Page size mix, especially presence of 32 KB spaces for DGTTs
• Per-member capacity in data sharing, not only group totals

What developers can do

You rarely CREATE objects in DSNDB07 yourself as an application task, but you influence demand: avoid sorting huge unfiltered result sets when an index or earlier filter would shrink them; prefer set-oriented SQL that lets Db2 choose efficient access paths; and design temporary-table usage so you are not materializing giant intermediates on every transaction. When production fails with work file symptoms, partner with DBAs—do not assume “add indexes” or “add disks” alone without measuring which statements consume the space.

1
2
3
4
5
6
7
Mental model
------------
User databases  -> permanent business tables
DSNDB06         -> catalog metadata
DSNDB01         -> directory runtime structures
Work file DB    -> scratch space for SQL + temp tables
                   (DSNDB07, or per-member name in data sharing)

How this fits the storage hierarchy

Buffer pools cache pages. The work file database supplies table spaces whose pages hold temporary data when memory is not enough or when temporary tables need a home. Logs still protect recoverable work elsewhere; temporary table logging rules are special and covered on the temporary tables page. For beginners, separate three ideas: permanent user data, system catalog/directory, and disposable working storage. Confusing DSNDB07 with DSNDB06 is a common early mistake—catalog is metadata you query; work files are scratch space you rarely SELECT from on purpose.

Explain It Like I'm Five

When you do a big puzzle on the floor, you need empty carpet space to sort pieces. The work file database is Db2’s empty carpet. Permanent toys stay on the shelves (user tables). The binder of toy names is the catalog. When too many kids sort puzzles at once, the carpet fills up and somebody cannot finish—that is a work file shortage. In a house with several playrooms (data sharing members), each playroom needs its own carpet, even if they share the same toy shelves.

Exercises

1. Name the default work file database in non-data-sharing Db2 and state how data sharing differs.
2. List four SQL patterns that often drive work file use.
3. Why can heavy declared temporary table use hurt overnight sort-heavy batch?
4. What page-size prerequisite do declared temporary tables impose on the work file database?
5. Draft three monitoring questions you would ask a DBA after a “sort work file” production incident.