DB2 work file database

What the work file database really is

A work file database is a specially designated Db2 database whose table spaces support temporary processing. Its most important characteristic is ownership: Db2 manages the internal work. Applications do not treat it as a durable system of record, and operations teams should not regard its pages as recoverable business data. Rows and work records exist only as long as the relevant statement, cursor, transaction, or application process requires them.

The word temporary does not mean unimportant. A statement can fail when Db2 cannot obtain enough work file space. A subsystem can also remain technically available while response times deteriorate because thousands of temporary pages are being written and read. Capacity, placement, monitoring, and access-path design therefore make the work file database a production-critical resource.

Do not confuse this database with a user-created database that happens to contain short-lived rows. The work file database has a defined subsystem role. Db2 directs qualifying temporary structures to its work file table spaces and manages their allocation and reuse according to the capabilities and rules of the installed release.

Work file table spaces

The physical capacity comes from work file table spaces. These table spaces have page sets and underlying data sets like other Db2 storage, but they are intended for transient work. A production configuration normally provides multiple work file resources so that one data set, volume, or allocation path does not become the only place where every temporary operation competes.

Exact table space types, page sizes, naming conventions, and creation options vary by Db2 version and installation practice. Follow IBM documentation for the installed function level rather than copying obsolete DDL. The design principle is stable: provide enough eligible temporary pages, spread demand sensibly, and verify that Db2 can extend or reuse the underlying storage under peak conditions.

1
2
3
4
5
6
7
8
9
10
11
-- Representative inventory query; confirm catalog columns for your Db2 level
SELECT DBNAME,
       NAME,
       BPOOL,
       PARTITIONS
  FROM SYSIBM.SYSTABLESPACE
 WHERE DBNAME = 'DSNDB07'
 ORDER BY NAME;
 
-- DSNDB07 is the traditional work file database name.
-- Your subsystem configuration and release documentation are authoritative.

Inventory is only the starting point. Pair catalog information with storage management reports, Db2 statistics, accounting traces, and operating-system I/O measurements. A table space can exist and still be poorly placed, unable to extend, concentrated on a busy device, or too small for the concurrent workload.

Which operations use temporary storage?

Db2 chooses an access path and execution methods based on available statistics, indexes, predicates, join relationships, and subsystem settings. Two SQL statements that look similar can create very different amounts of work. Likewise, the same statement can use more temporary storage after data growth, skew, a statistics change, or a package rebind. Capacity planning must therefore account for access-path change as well as normal volume growth.

Sort work and sort spills

Sorting appears in more places than an explicit ORDER BY. GROUP BY, DISTINCT, merge operations, duplicate removal, some joins, and selected access paths can require ordered work. Db2 first uses available in-memory sort resources when appropriate. If the operation cannot remain in memory, it writes temporary sorted runs to work file storage and later reads or merges them. That transition is called a sort spill.

A spill is not automatically a defect. A very large report may reasonably exceed memory, and reserving enough memory for every possible sort could harm the subsystem more than controlled I/O does. The warning sign is avoidable or rapidly growing spill activity: frequent small queries spilling, a new package sorting millions of unnecessary rows, or concurrent batch jobs driving work file I/O and online response time at the same time.

1
2
3
4
5
6
7
8
9
10
11
-- This query may require a large sort when no useful ordering access path exists
SELECT CUSTOMER_REGION,
       COUNT(*) AS ORDER_COUNT,
       SUM(ORDER_TOTAL) AS TOTAL_VALUE
  FROM APP.ORDERS
 WHERE ORDER_DATE >= CURRENT DATE - 365 DAYS
 GROUP BY CUSTOMER_REGION
 ORDER BY TOTAL_VALUE DESC;
 
-- Reduce work by examining predicates, indexes, statistics,
-- selected columns, result cardinality, and the complete access path.

Diagnose the cause before increasing a limit. If a predicate is not stage-one eligible, statistics are stale, or a join order produces a huge intermediate set, more space merely lets inefficient work run longer. Compare access paths before and after the problem, check estimated and actual row counts, and identify the statement and package responsible for the surge.

Temporary tables: an important distinction

Db2 uses internal temporary structures that applications never name. Separately, applications can use declared global temporary tables (DGTTs). A DGTT is explicitly declared for an application process, referenced through the SESSION schema, and populated by application SQL. Its definition and rows are temporary; it is not a permanent catalog table shared by every user.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DECLARE GLOBAL TEMPORARY TABLE SESSION.RECENT_ORDERS
  (ORDER_ID      BIGINT NOT NULL,
   CUSTOMER_ID   BIGINT NOT NULL,
   ORDER_TOTAL   DECIMAL(11,2))
  ON COMMIT PRESERVE ROWS
  NOT LOGGED;
 
INSERT INTO SESSION.RECENT_ORDERS
SELECT ORDER_ID, CUSTOMER_ID, ORDER_TOTAL
  FROM APP.ORDERS
 WHERE ORDER_DATE = CURRENT DATE;
 
SELECT CUSTOMER_ID, SUM(ORDER_TOTAL)
  FROM SESSION.RECENT_ORDERS
 GROUP BY CUSTOMER_ID;

Although DGTT data can consume work file resources, a DGTT is not the same thing as every Db2-created intermediate result. The application controls when it declares and references the DGTT. Db2 controls internal materialization used to execute a statement. Also distinguish DGTTs from created global temporary tables, whose definitions are persistent catalog objects even though their data is temporary. Release-specific rules for logging, commit behavior, indexes, and supported SQL must be checked before choosing a design.

CTE work and materialization

A common table expression (CTE) gives a name to a query expression within a statement. The syntax alone does not guarantee that Db2 creates a stored temporary result. The optimizer may merge a nonrecursive CTE into the surrounding query or may materialize it when that is the better or required execution strategy. Recursive CTE processing and repeated references can create substantial working sets.

1
2
3
4
5
6
7
8
9
10
11
12
WITH REGIONAL_TOTALS AS
(
  SELECT CUSTOMER_REGION,
         SUM(ORDER_TOTAL) AS REGION_TOTAL
    FROM APP.ORDERS
   WHERE ORDER_DATE >= CURRENT DATE - 30 DAYS
   GROUP BY CUSTOMER_REGION
)
SELECT CUSTOMER_REGION, REGION_TOTAL
  FROM REGIONAL_TOTALS
 WHERE REGION_TOTAL > 100000
 ORDER BY REGION_TOTAL DESC;

Do not estimate CTE cost by counting lines of SQL. Examine the access path to learn whether materialization occurs, how many rows Db2 expects, and whether sorting or repeated scans are involved. Filtering earlier, maintaining distribution statistics, or changing indexes can reduce the generated work, but rewrites should be verified rather than assumed to be faster.

Intermediate result sets

Complex joins, subqueries, UNION operations, duplicate elimination, and multi-step transformations can require intermediate results. Db2 may pipeline rows directly from one operation to another, keep a structure in memory, or materialize records in work file storage. Materialization can be beneficial because it prevents repeated expensive work, enables a required access method, or provides a stable input for later processing.

Problems arise when the intermediate result is unexpectedly large. A join predicate omitted by mistake can multiply rows. Data skew can make optimizer estimates inaccurate. Selecting wide character or LOB-related values can increase each work record. A query that ultimately returns ten rows may still process and temporarily store millions of rows before applying its final filter. The returned row count alone does not measure work file demand.

Sizing the work file database

There is no universal size such as a fixed percentage of the largest application database. Begin with measured high-water usage over representative online peaks, batch windows, reporting periods, month-end processing, and maintenance overlap. Determine which statements created the peaks and whether they represent valid business demand or tuning opportunities.

Add a deliberate safety margin, but do not use unexplained free space as the entire strategy. Define warning and critical thresholds, assign an owner, and document what can be extended during an incident. A good plan answers how quickly storage can grow, what happens when a volume fills, and which workloads can be delayed to protect critical transactions.

• Track current allocation, used pages, high-water marks, extensions, and failures.
• Separate normal growth from one exceptional statement or runaway workload.
• Include concurrency because ten simultaneous spills differ from ten sequential spills.
• Revisit the model after application releases, data growth, rebinds, and Db2 maintenance.
• Test alerting before capacity becomes exhausted.

Buffer pools for work file table spaces

Work file table spaces are assigned to buffer pools. The buffer pool provides a memory layer for temporary pages, but allocating an extremely large pool does not guarantee that all sorts remain in memory. Sort memory and buffer pool memory serve related but distinct roles. A spilled sort still writes work records to temporary pages; buffering influences how those page accesses interact with storage.

Monitor the actual work file buffer pool rather than applying application-data targets mechanically. Sequential temporary access, rapid page reuse, prefetch behavior, write activity, and I/O latency all matter. Isolating work file table spaces in a suitable buffer pool can make their behavior visible and prevent temporary activity from displacing frequently reused application pages. However, memory moved into that pool is no longer available elsewhere, so changes require subsystem-wide measurement.

Partitioning and spreading the workload

Multiple work file table spaces or partitions can spread allocation and I/O demand, increase usable capacity, and reduce dependence on one physical resource. This is especially valuable when many agents create temporary work concurrently. Distribution is not automatic proof of balance: data sets placed on the same constrained storage path can still contend even when their Db2 names differ.

Review storage groups, volume selection, data set placement, page sizes, extension behavior, and the capabilities of the storage subsystem. In data sharing, consider how each member's workload and configuration contribute to group demand. Use current IBM guidance because work file allocation behavior has evolved across releases.

Performance and spill diagnosis

Effective diagnosis connects a symptom to a consumer. Start with the time window: when did response time, work file usage, or I/O rise? Identify active plans, packages, dynamic statements, and utilities. Compare Db2 accounting and statistics metrics with buffer pool, disk, CPU, and z/OS workload data. Then inspect access paths for statements associated with large sorts or materialized results.

Questions to ask during an incident

• Did SQL volume increase, or did the amount of work per execution increase?
• Did a BIND or REBIND change the access path?
• Are statistics current and representative of skew?
• Is work file space genuinely exhausted, or is I/O latency the main constraint?
• Are many modest spills colliding, or is one statement dominating?
• Did storage placement, buffer pool settings, or subsystem maintenance change?

A capacity alert and a performance alert need different responses. If allocation is near its limit, adding eligible space may be urgent to avoid failures. If abundant space exists but I/O time is high, investigate placement, buffering, and excessive work. If CPU is high without major spill I/O, an in-memory sort or inefficient SQL can still be responsible.

Improving sort performance

The best temporary I/O is often work that the query never needs to perform. Use selective predicates, appropriate indexes, current statistics, and efficient joins to reduce rows before sorting. Avoid selecting wide columns when only keys are needed for an early phase. Check whether an index can satisfy required ordering, but remember that forcing an index merely to avoid a sort can increase random I/O or make other parts of the plan worse.

Next evaluate subsystem resources. Ensure work file table spaces have headroom, their buffer pools are not starved, storage latency is acceptable, and concurrent batch work is scheduled intelligently. Sort-related subsystem parameters should be changed only with current documentation and measurements. Increasing memory per sort can reduce spills but may reduce the number of concurrent operations the system can support safely.

Treat tuning as a loop: capture a baseline, change one justified factor, rerun a representative workload, and compare elapsed time, CPU, getpages, rows processed, sort counts, spill indicators, work file I/O, and impact on other transactions. A faster test query is not a successful change if it destabilizes the production mix.

Operational practices

• Monitor capacity and performance continuously, including business peaks and unusual overlaps.
• Keep DDL, storage placement, buffer pool assignments, thresholds, and emergency extension procedures documented.
• Correlate work file growth with packages and SQL instead of treating all demand as anonymous system overhead.
• Validate the work file design after migration, function-level activation, or storage architecture changes.
• Test failure handling so operators know how to identify the consumer and protect priority workloads.

Explain it like I'm 5

Imagine a teacher asks a class to put thousands of cards in alphabetical order. The children first sort cards on their desks. That is like Db2 sorting in memory. If the cards no longer fit, they place partly sorted piles on special tables around the room. Those tables are the work file database. The piles are important while the job is being done, but they are not the school's permanent records and can be cleared afterward.

A declared global temporary table is different: it is like one child asking for a named tray, putting cards in it, and using that tray during the lesson. The child knows the tray's name, but it is still emptied when that child's session ends. Good planning provides enough tables and trays, spreads them around the room, and avoids asking the class to sort cards that were never needed.

Exercises

1. Draw a flow from SQL execution to an in-memory sort and then to a work file spill. Label where CPU, memory, writes, and reads are consumed.
2. Compare an internal Db2 intermediate result, a declared global temporary table, and a created global temporary table. Describe ownership, visibility, definition lifetime, and data lifetime.
3. Build a sizing worksheet using peak concurrent sorts, DGTT usage, intermediate results, growth, and a safety margin. Explain every assumption instead of choosing an arbitrary percentage.
4. Take a query with GROUP BY and ORDER BY. List three SQL or access-path changes that might reduce sort work and one reason each change could make performance worse.
5. Write an incident checklist for rapidly rising work file usage. Include capacity, responsible SQL, access-path changes, buffer pool evidence, storage latency, and immediate workload protection.
6. Explain why adding space can prevent a failure but cannot by itself correct a query that generates an unnecessarily large intermediate result.