DB2 Space Management

What Db2 space management controls

Space management has three time horizons. At design time, the DBA chooses table-space type, page size, partitioning, DSSIZE, maximum partitions, storage group, and initial allocation policy. During normal operation, Db2 consumes free pages and extends data sets as rows and index entries arrive. During maintenance, utilities reclaim fragmented space, rebuild compression dictionaries, redistribute free space, materialize pending changes, and produce the statistics needed for the next forecast.

The goal is not to minimize every allocated byte. A space with no headroom can fail during a burst; a space with badly placed free room can suffer page splits or relocated rows; and a space with generous but unused allocations can waste scarce volumes. Good management balances availability, performance, recovery time, utility windows, and storage cost. It also distinguishes allocated space from used pages and from the theoretical object maximum.

From Db2 pages to VSAM data sets

Db2 page sets are defined as VSAM linear data sets. The buffer-pool page size determines the logical page size for a table space, while VSAM control intervals provide the transfer and allocation structure underneath. Db2-managed data sets are created and extended according to the object definition, storage-group policy, SMS classes, and available volumes. A partitioned table space normally has a distinct data set for each partition; sufficiently large nonpartitioned structures can use multiple pieces.

This boundary explains why a Db2 symptom can have a storage-system cause. Db2 asks to extend a data set, but media manager, VSAM, SMS, the catalog, and physical volumes participate in satisfying the request. An extension can fail because no eligible volume has room, a data set reached DSSIZE, a partition-by-growth space reached MAXPARTITIONS, or an extent-related limit was reached. Always preserve the accompanying z/OS and DSNP messages instead of diagnosing from an SQLCODE alone.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Logical object                 Physical allocation
------------------------------   -----------------------------------------
Database
  Table space                    One or more VSAM linear data sets
    Partition 1                  Data set for partition 1
    Partition 2                  Data set for partition 2
  Index                          VSAM data set(s), partitioned if applicable
 
Growth path:
free pages -> unused primary allocation -> secondary extent
           -> larger partition/data set -> new PBG partition
           -> hard boundary if no valid allocation remains
 
The exact path depends on table-space type, partitioning,
DSSIZE, MAXPARTITIONS, SMS policy, and available volumes.

Sizing databases, table spaces, and indexes

Begin with a workload forecast rather than a copied PRIQTY. Estimate current rows, average row length, monthly growth, retention, purge policy, and peak ingestion. Include variable-length expansion, row overhead, page headers, space-map pages, free-space policy, and multiple table versions where relevant. Then estimate each index independently: key columns, included columns, RID size, expected entries, leaf-page density, nonleaf levels, and the effect of partitioning all consume space.

A useful forecast has at least three numbers: expected used space at the end of the planning period, normal allocated headroom, and the physical maximum. Add utility workspace, image copies, sort work, and recovery assets outside the page-set estimate. A 500 GB table can require substantially more than 500 GB of temporary and copy capacity during a major REORG. Work-file sizing is also based on peak concurrent spill demand, not the permanent table size or an average quiet-hour sample.

A simple forecast

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Example planning estimate
-------------------------
Current rows                           180,000,000
Average stored row bytes                       220
Expected annual row growth                     30%
Planning horizon                            2 years
 
Projected rows = 180,000,000 × 1.30 × 1.30
               = 304,200,000
 
Raw row bytes  = 304,200,000 × 220
               = 66,924,000,000 bytes
 
Then add or model:
  page and row overhead
  PCTFREE / expected page occupancy
  compression estimate
  each index separately
  LOB and XML storage separately
  utility, copy, and growth headroom
 
This is a forecast, not a CREATE TABLESPACE calculator.

PRIQTY, SECQTY, and secondary growth

PRIQTY specifies a minimum primary allocation, in kilobytes, for a Db2-managed data set. It affects the allocation when a new data set is created; changing it does not magically resize every existing extent. A primary quantity that is far below normal object size causes extension work soon after creation. One that is far above a realistic horizon can reserve capacity that other objects need.

SECQTY controls secondary allocation. If SECQTY is omitted in eligible CREATE statements, or an existing object is altered to SECQTY -1, Db2 uses a sliding-scale algorithm. Secondary extents grow as the data set grows, reducing the risk created by a permanently tiny fixed request. IBM documents that a specified SECQTY can still influence allocation when it is larger than the calculated minimum; the actual request also remains bounded by limits such as DSSIZE or index PIECESIZE.

SECQTY 0 is not a general growth strategy. It prevents secondary allocation in supported contexts and can turn exhaustion of the primary allocation into an immediate outage. Prefer Db2-managed growth unless measurements prove that a site-specific fixed value is better. Storage teams should also enable and validate the appropriate SMS extent relief policy rather than trying to compensate for obsolete limits with huge manual extents.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Illustrative definitions; validate values for your Db2 level.
CREATE TABLESPACE ORDERTS
  IN APPDB
  USING STOGROUP APPSTG
  PRIQTY -1
  SECQTY -1
  DSSIZE 32G
  MAXPARTITIONS 64
  PCTFREE 10
  COMPRESS YES;
 
-- Return an existing Db2-managed object to calculated allocation.
ALTER TABLESPACE APPDB.ORDERTS
  PRIQTY -1
  SECQTY -1;
 
-- DDL changes policy. It does not by itself compact existing data
-- or guarantee that every physical attribute is materialized now.

DSSIZE, MAXPARTITIONS, and partition capacity

DSSIZE specifies the maximum size of a data set for a table space or partition. It is a capacity boundary, not an initial allocation. For a partition-by-growth (PBG) universal table space, Db2 grows the current partition until the applicable boundary and then creates another partition, up to MAXPARTITIONS. IBM permits as many as 4096 partitions in supported combinations, but the valid maximum depends on DSSIZE and page size.

Partition-by-range (PBR) separates rows according to key boundaries; its sizing must account for skew. A year with unusual activity can fill one range while other partitions remain mostly empty. PBG avoids key-range skew but still needs a ceiling and maintenance plan. Monitor the size and growth of every partition, not only the sum for the table space. A healthy total percentage can hide one partition approaching its individual limit.

MAXPARTITIONS cannot be reduced below the number of physical partitions already allocated. Increasing it can extend the capacity envelope only when the page-size and DSSIZE combination permits the value. Raising a limit is not reclamation: if old data should have been purged, adding partitions merely delays the next boundary and increases utility and recovery scope.

Free space: PCTFREE, FREEPAGE, and page health

Free space is deliberate room for change. PCTFREE reserves a percentage of each data page when data is loaded or reorganized. It helps an INSERT find room near the desired clustering position and gives an expanding variable-length row space to remain on its page. FREEPAGE leaves one whole empty page after a chosen number of populated pages. IBM permits FREEPAGE values from 0 through 255 for supported table spaces and PCTFREE values from 0 through 99.

These values describe the post-utility layout, not an inviolable reserve. Inserts and updates can consume it. Too little space can increase page splits, disorganized clustering, indirect references, and REORG pressure. Too much space increases page counts, I/O, buffer-pool demand, copy size, and utility elapsed time. Choose values from insert pattern, update expansion, clustering, and observed real-time statistics rather than from a universal percentage.

Db2 also supports PCTFREE FOR UPDATE behavior for eligible table spaces. It targets update expansion rather than general insert placement, and automatic behavior can use real-time statistics. After changing free-space attributes with ALTER, plan the utility that will rebuild existing pages if redistribution is required. Merely committing the ALTER does not move old rows.

Compression and compression dictionaries

Table-space compression stores more logical data in fewer pages. With COMPRESS YES, Db2 uses a compression method appropriate to the definition and active function level. Traditional fixed-length and newer Huffman approaches use dictionaries that represent recurring data patterns compactly. Dictionaries are built or rebuilt by eligible utility processing, and Db2 can also create a dictionary as data reaches the documented threshold in supported cases.

Compression changes every sizing equation. Estimate with representative production data by using tools such as DSN1COMP where appropriate, and do not assume another table's ratio. Highly repetitive character data often compresses well; already compressed, encrypted, or random bytes may not. A better ratio reduces allocated pages, I/O, copies, and buffer-pool pressure, but compression and expansion use processor resources. Measure total service and elapsed time, not DASD alone.

Index compression is different from table-space dictionary compression. Compressed indexes require an 8 KB, 16 KB, or 32 KB buffer pool. Leaf pages are compressed on disk and represented at the assigned larger page size in memory; no table-space-style dictionary performs that work. Changing index page size or compression can produce pending physical changes and require REBUILD INDEX or REORG. Include buffer-pool memory, I/O savings, utility time, and rollback in the decision.

Extents: what they mean and when they matter

An extent is a contiguous allocation acquired for a data set. The primary allocation is normally the first extent, and later growth uses secondary extents. A high extent count is not automatically a modern Db2 performance defect. Db2's sliding-scale allocation increases requests over time, while contemporary SMS and storage reduce the old performance penalty associated with physical discontinuity.

Extents still matter for availability. IBM documents up to 7257 extents when the SMS data class uses Extent Constraint Removal, while per-volume and volume-count rules still apply. A data set can also reach its DSSIZE before its extent count looks alarming. Monitor extents as one part of a boundary model that includes allocated bytes, active pages, last data set or piece, eligible volume capacity, and partition headroom.

• Treat rapid increases in extent count as evidence of growth or undersized requests, not proof of slow SQL.
• Confirm that production SMS data classes provide the intended extent-relief behavior.
• Keep enough eligible volumes and storage-group capacity for the next extensions.
• Reorganize for data organization, reclamation, pending changes, or measured utility goals—not solely to make an extent number aesthetically small.

Monitoring allocation and growth

Real-time statistics provide the best Db2-level operational feed. SYSIBM.SYSTABLESPACESTATS and SYSIBM.SYSINDEXSPACESTATS include SPACE in kilobytes and EXTENTS, along with active-page, row or entry, and maintenance information. For a multi-piece page set, read IBM's column definition carefully: SPACE can cover all pieces while EXTENTS can describe the last data set. Catalog definition values and storage-system data complete the picture.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- Table-space and partition growth snapshot
SELECT DBNAME,
       NAME,
       PARTITION,
       SPACE,
       EXTENTS,
       NACTIVE,
       TOTALROWS,
       REORGLASTTIME,
       STATSLASTTIME
FROM SYSIBM.SYSTABLESPACESTATS
ORDER BY DBNAME, NAME, PARTITION
WITH UR;
 
-- Index-space growth snapshot
SELECT DBNAME,
       NAME,
       PARTITION,
       SPACE,
       EXTENTS,
       NACTIVE,
       TOTALENTRIES,
       REBUILDLASTTIME
FROM SYSIBM.SYSINDEXSPACESTATS
ORDER BY DBNAME, NAME, PARTITION
WITH UR;
 
-- Column availability and types depend on catalog/function level.
-- Validate against IBM documentation for the active subsystem.

A snapshot shows current state; a history shows risk. Capture regular samples and calculate growth per day or business cycle. Alert on forecast time to DSSIZE, remaining PBG partitions, abnormal acceleration, storage-group exhaustion, and repeated extension warnings. STOSPACE remains relevant for updating catalog space values used by traditional catalog queries, but do not confuse its last-run result with continuously maintained real-time statistics.

Production growth review

1. Identify the largest and fastest-growing table-space and index partitions.
2. Compare current allocation with DSSIZE, PIECESIZE, and available partitions.
3. Calculate days to boundary from several historical intervals, including peak season.
4. Check SMS storage groups, eligible volumes, extent policy, and copy or utility space.
5. Explain growth through business rows, retention, index additions, or data skew.
6. Choose purge, compression, REORG, repartitioning, or capacity growth before ALTER.

ALTER impacts and pending physical changes

ALTER is not one kind of operation. PRIQTY and SECQTY change allocation policy for applicable future data-set creation or extension. PCTFREE and FREEPAGE alter the layout requested by future LOAD or REORG processing. DSSIZE, page-size-related choices, compression, and partition changes can be immediate, deferred, restricted, or pending depending on the object type and active Db2 level.

IBM notes that changing storage attributes for more than one partition requires separate ALTER TABLESPACE statements. USING or ERASE changes can require the target to be stopped. ALTER cannot proceed while a utility controls the object, and mixing some space ALTER activity with inserts in one commit scope can require an intervening COMMIT. Read the exact statement rules, check object state after DDL, and schedule any required REORG or recovery action.

• Capture the current definition, object states, catalog values, and RTS baseline.
• State whether the change affects future allocation, existing pages, or both.
• Identify pending DDL, required utilities, outage or drain needs, and recovery assets.
• Model temporary space, log volume, image-copy impact, elapsed time, and application concurrency.
• Define verification and rollback before executing production DDL.

Common allocation errors and diagnosis

An application can report SQLCODE -904 because the required page set or data set is unavailable. When the underlying reason is 00D70014, Db2 attempted to extend a data set and media manager reported a failure. IBM advises checking associated DSNP001I, DSNO007I, DSNP011I, or related messages. The volume might be full, the data set might have reached an allowable extent or size boundary, or another allocation problem might exist.

• DSNP031I extent warning: a data set is approaching or has reached an extent boundary. Treat the warning as capacity work, even if applications still run.
• SQLCODE -904 / 00D70014: the requested resource is unavailable because a data-set extension failed; use accompanying messages to find the physical cause.
• DSSIZE or partition ceiling: storage can remain available while the Db2 object has no addressable room under its current physical definition.
• Utility allocation failure: a REORG, LOAD, REBUILD, copy, or sort can exhaust temporary storage even when the target page set still has growth headroom.

Do not respond by blindly increasing SECQTY. First identify the object and data set, partition or piece, current size, DSSIZE or PIECESIZE, extent count, storage group, candidate volumes, and recent growth. If a PBG space reached MAXPARTITIONS, adding volume capacity does not raise the partition ceiling. If a volume is full, changing PCTFREE does not make the failed extension succeed. If deleted data is reclaimable, REORG may be part of the long-term answer, but an emergency recovery procedure must follow the site's tested runbook.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Allocation failure triage
-------------------------
1. Preserve SQLCODE, reason code, resource type, and DSNP/DSNO messages.
2. Resolve the Db2 object to its VSAM data set, partition, and piece.
3. Check:
   - allocated size versus DSSIZE / PIECESIZE
   - current partition count versus MAXPARTITIONS
   - extent count and SMS extent-relief policy
   - free capacity on eligible storage-group volumes
   - recent growth and abnormal load activity
4. Restore service with the approved storage/Db2 runbook.
5. Correct the forecast, allocation policy, retention, or physical design.
 
Never delete, rename, or manually redefine a Db2-owned VSAM
data set as an improvised space fix.

Production practices

• Use Db2-managed primary and sliding-scale secondary allocation as the default, then override only with measured evidence.
• Forecast table spaces and every index separately; include LOB, XML, work-file, image copy, sort, and utility requirements.
• Alert on time to boundary rather than only a fixed percentage full. Fast small objects can be riskier than stable large ones.
• Keep RTS history and correlate Db2 statistics with SMS and volume-level monitoring.
• Use PCTFREE and compression from measured row behavior and representative data.
• Treat REORG as a planned physical-maintenance action with measurable reasons, not a ritual response to every extent.
• Test DDL and utility combinations, object states, fallback, and recovery in a production-like environment.
• Review seasonal peaks, retention changes, application releases, and new indexes with application and storage teams before they change the growth curve.

Explain It Like I'm Five

Imagine a toy room with shelves. PRIQTY is how much shelf space you get when the room opens. SECQTY is how much more shelf the helper adds when it fills up; SECQTY -1 lets the helper choose increasingly useful pieces. DSSIZE says how big one shelf can become, and MAXPARTITIONS says how many shelves the room may have. PCTFREE leaves little gaps beside toys so new toys fit without moving everything. Compression packs soft toys into smaller boxes. Extents are the extra shelf pieces added over time. A good manager counts the toys, watches how quickly shelves fill, and orders space before the last shelf is full.

Exercises

1. Forecast a table with 80 million 300-byte rows growing 20% per year for three years. List the additional inputs needed before selecting DSSIZE and MAXPARTITIONS.
2. Compare PRIQTY 4G with PRIQTY -1 for a small object that might grow rapidly. Explain what evidence would justify either choice.
3. A PBG table space has 14 physical partitions, MAXPARTITIONS 16, and rapidly growing daily inserts. Design an alert and remediation plan.
4. Explain why changing PCTFREE from 5 to 20 does not immediately create 20% free room in every existing page.
5. Use test RTS data to rank objects by allocated space, extent growth, and forecast days to DSSIZE. Explain why each ranking answers a different question.
6. Draft a COMPRESS YES evaluation covering DSN1COMP estimates, CPU, I/O, buffer pools, utility materialization, image copies, and rollback.
7. Given SQLCODE -904 with reason 00D70014, write a diagnostic checklist that distinguishes volume exhaustion, extent limits, DSSIZE, and MAXPARTITIONS.
8. Design a monthly space review that joins Db2 RTS history with SMS capacity and planned application releases.