Db2 Table Spaces Guide: PBG, PBR, Pages, and Storage



Db2 table spaces organize table storage and growth.

A COBOL program sees a table name in embedded SQL, but Db2 stores the rows in a table space. That storage choice affects page size, partition growth, REORG work, buffer pool assignment, locking behavior, and recovery planning. When a table grows from 5 million rows to 500 million rows, the table-space design becomes part of the application performance story.

This refresh updates the original short definition into a practical Db2 for z/OS guide. It explains table spaces, partition-by-growth and partition-by-range universal table spaces, buffer pool relationship, and the information developers should collect before asking a DBA to change storage design.

What Is a Db2 Table Space?

A table space is the Db2 storage object that holds table data. IBM's Db2 for z/OS documentation for CREATE TABLESPACE states that the statement defines a table space at the current server, and that the type depends on the keywords specified. For current universal table spaces, the main choices are partition-by-growth and partition-by-range.

Application SQL normally references tables, not table spaces. Still, table-space design influences the path Db2 takes to read, insert, update, delete, reorganize, and recover the data.

Table, Table Space, Index Space, and Database

The names can sound similar, but they refer to different layers. A table is the logical object your SQL reads. A table space is the storage object that contains the table rows. Indexes are stored separately in index spaces. A database is a logical container for table spaces and related objects.

Object	Purpose	Developer sees it where?
Table	Logical rows and columns.	SELECT, INSERT, UPDATE, DELETE.
Table space	Stores table data pages.	DDL, DBA reports, utility jobs, storage reviews.
Index space	Stores index pages.	EXPLAIN output, access-path tuning, DBA reports.
Database	Groups related Db2 storage objects.	DDL naming and administration.

PBG and PBR Universal Table Spaces

Most new Db2 for z/OS designs use universal table spaces. The two common forms are partition-by-growth and partition-by-range.

Type	How it grows	Good fit
Partition-by-growth (PBG)	Db2 adds partitions as the object grows, up to the maximum.	Tables that need growth handling but do not have a useful range partitioning key.
Partition-by-range (PBR)	Rows are placed by defined range boundaries.	Large tables with date, account, region, or other range-based access and maintenance needs.

PBG can be easier to start with. PBR can be stronger when data lifecycle and access patterns follow a known range. For example, a transaction table partitioned by business month can support targeted REORG, LOAD, COPY, and recovery work.

CREATE TABLESPACE Example

The exact DDL belongs to the DBA and site standards, but developers should understand the shape of the definition. This example shows a small partition-by-growth table space assigned to an 8 KB buffer pool.

CREATE TABLESPACE ACCTTS01
  IN ACCTDB
  MAXPARTITIONS 64
  SEGSIZE 32
  BUFFERPOOL BP8K0
  LOCKSIZE ROW
  COMPRESS YES;

The BUFFERPOOL clause matters because it identifies the buffer pool used for the table space and determines the page size. If the workload uses wide rows or large indexes, page size and buffer pool choice need DBA review before the object is built.

How Table Spaces Affect Application Performance

A table-space choice does not replace SQL tuning, but it can support or hurt the access pattern. A table that receives heavy inserts, monthly purges, and frequent account lookups has different storage needs from a small reference table.

• Partitioning can limit utility and recovery work to part of a large table.
• Page size affects how rows fit on pages and which buffer pool is used.
• Compression can reduce storage and I/O but adds CPU considerations.
• Lock size and row density can affect concurrency during batch and online peaks.
• Poor clustering can increase page reads even when an index is used.

For page-cache behavior, see the related Db2 Buffer Pool Guide. For predicate and access-path checks, see Db2 SQL Optimization Tips for COBOL Programs.

Developer Checklist Before Requesting a Table-Space Change

Table-space changes are not casual edits. They can require utilities, outages, storage planning, and fallback steps. Bring evidence instead of a vague performance complaint.

• SQL statements and package or job names that show the problem.
• EXPLAIN output for the slow statements.
• Row counts before and after the data growth.
• Insert, update, delete, and purge pattern.
• Whether queries are range-based, random lookup, or scan-heavy.
• Utility pain points: REORG time, COPY time, LOAD restart, or recovery window.

Questions to Ask the DBA

A good table-space review is a joint exercise. Developers know the business access pattern; DBAs know the physical design and subsystem constraints.

Question	Why it matters
Is this table better as PBG or PBR?	Growth-only tables and range-maintained tables need different designs.
Which buffer pool and page size will it use?	Page size and cache behavior affect reads, writes, and memory pressure.
What are the REORG, COPY, and recovery expectations?	Utility windows often drive partitioning decisions.
Does the clustering index match the main access pattern?	Good clustering reduces unnecessary page reads.

Common Mistakes

• Choosing PBG for every table because it is simple, even when range maintenance is the real requirement.
• Ignoring buffer pool page size until after wide rows are already in production.
• Designing partitions around today’s row count without considering five years of growth.
• Assuming a table-space change will fix a query with non-indexable predicates.
• Leaving utility and recovery teams out of the design conversation.

FAQ

Can one Db2 table space contain more than one table?

Older designs can include multi-table table spaces, but modern Db2 for z/OS designs commonly use universal table spaces that are centered on one table. Confirm the rule with your site standards and Db2 version.

Does a COBOL program access a table space directly?

No. COBOL embedded SQL references tables and indexes indirectly through access paths. Db2 uses the table space and index spaces underneath.

Is PBG or PBR better for large tables?

It depends on the growth and access pattern. PBG is useful when growth is the main concern. PBR is usually better when data is naturally managed by range, such as month, account range, or region.

A table-space design is good when it matches the way the data grows, the way SQL reads it, and the way operations must recover it. Treat it as part of application design, not only a storage detail.

Db2 Buffer Pool Guide for COBOL and SQL Performance



Db2 buffer pools cache pages before disk I/O.

A nightly COBOL job can run slowly even when the SQL text has not changed. One common reason is that Db2 is reading too many table or index pages from disk instead of finding them in memory. A Db2 buffer pool is the memory area that holds those pages while SQL statements read or change data.

This refresh corrects the old page's mixed platform wording and focuses on Db2 for z/OS. Buffer pools are not application code, but application teams still need to understand them because access paths, table-space design, index usage, and batch volume all affect buffer pool pressure.

What Is a Db2 Buffer Pool?

A Db2 buffer pool is a virtual storage area used to cache pages from table spaces and index spaces. When a program executes SQL, Db2 requests pages. If the page is already in the buffer pool, Db2 can use it from memory. If not, Db2 must read the page from disk.

That difference matters. Memory access is much faster than synchronous disk I/O. A bad access path that scans millions of pages can put pressure on the buffer pool and slow other work running in the same subsystem.

Why Buffer Pools Matter to COBOL Programs

COBOL code does not name the buffer pool directly in embedded SQL. The effect shows up through elapsed time, CPU use, wait time, and batch-window pressure. A cursor that fetches 10,000 rows by index might behave well. The same cursor after a predicate change might scan a large table space and drive many GETPAGE requests.

Application symptom	Buffer pool angle	First check
Batch job elapsed time doubles after data growth.	More pages are read or scanned.	EXPLAIN access path and object statistics.
Online transaction waits during peak hours.	Hot pages may be competing with scan-heavy work.	Buffer pool display and high-volume SQL statements.
Query reads only a few rows but uses many pages.	Index or clustering may not match the access pattern.	Predicate indexability and clustering ratio.

GETPAGE, Hit Ratio, and Disk Reads

A GETPAGE is Db2 asking for a page. A high GETPAGE count is not automatically bad; a heavily used table can produce many logical page requests. The expensive case is when many requests require physical I/O or when one query scans far more pages than the business result needs.

Use hit ratio carefully. A high ratio can still hide a wasteful query if the system performs millions of unnecessary GETPAGEs. A low ratio can be normal for a one-time sequential scan. Tie the metric back to SQL volume, object size, and elapsed time.

Buffer Pool Page Sizes

Db2 for z/OS uses buffer pools for different page sizes. A table space or index space must be assigned to a buffer pool with a compatible page size. Common page sizes are 4 KB, 8 KB, 16 KB, and 32 KB.

Page size	Typical use	Developer impact
4 KB	Many standard table spaces and indexes.	Good fit for narrow rows and common OLTP access.
8 KB or 16 KB	Wider rows or larger index pages.	Can reduce overflow pressure but changes page economics.
32 KB	Very wide rows, LOB-related designs, or special cases.	Should be chosen deliberately with DBA review.

Commands DBAs Use

Application developers usually do not alter buffer pools, but they should recognize the commands used during investigation. Site standards vary, and production changes belong to the DBA team.

-- Display buffer pool activity
-DISPLAY BUFFERPOOL(BP0) DETAIL

-- Example of a DBA-controlled change pattern
-ALTER BUFFERPOOL(BP8K0) VPSIZE(120000)

Do not paste tuning commands into production from a tutorial. The right buffer pool size and thresholds depend on subsystem memory, workload mix, page size, object assignment, and service goals.

Object Assignment Matters

Table spaces and index spaces are assigned to buffer pools. A large reporting table, a heavily updated account table, and a hot index might not belong in the same pool if their access patterns fight each other.

• Random index lookups benefit from having hot index pages in memory.
• Large sequential scans can push useful pages out of a shared pool.
• Work files and temporary activity can affect sort-heavy workloads.
• High-update objects need attention to write thresholds and changed-page handling.

The next related post in this sequence, DB2 Table Spaces, should cover how table-space design connects to buffer pool assignment.

What Developers Should Check Before Blaming the Buffer Pool

Many buffer pool complaints are really SQL access-path problems. Before asking for a buffer pool change, collect evidence from the statement and object design.

• Run EXPLAIN for the statement and confirm index access versus table-space scan.
• Check whether RUNSTATS is current for the table, index, and key columns.
• Review predicates for functions, arithmetic, mismatched host variable types, and non-indexable patterns.
• Check whether the program fetches more rows than it uses.
• Compare test data volume with production data volume before trusting elapsed time.

The refreshed Db2 SQL Optimization Tips for COBOL Programs guide covers these access-path checks in more detail.

Buffer Pool Tuning Is a DBA Task

Developers can provide the failing SQL, package name, plan name, object names, row counts, and timing. DBAs can then review buffer pool statistics, object placement, thresholds, and memory tradeoffs.

Developer provides	DBA reviews
SQL text, package, collection, and job or transaction name.	Buffer pool activity and object assignment.
Before/after elapsed time and row counts.	Synchronous reads, writes, thresholds, and page residency.
EXPLAIN output and RUNSTATS date.	Whether memory tuning or SQL tuning is the better fix.

FAQ

Does a larger Db2 buffer pool always improve performance?

No. More memory can help when the workload is I/O-bound and pages can be reused, but it will not fix a bad access path that scans too many pages.

Can a COBOL program choose a buffer pool?

No. The program issues SQL. Buffer pool use follows the table space or index space that Db2 accesses for that SQL statement.

What should I collect before reporting a buffer pool issue?

Collect the SQL statement, package or job name, EXPLAIN output, row counts, elapsed time, object names, and whether the issue started after data growth, bind, RUNSTATS, or a code change.

A buffer pool problem is easiest to solve when the SQL evidence and Db2 subsystem evidence are reviewed together. Start with the statement, then decide whether the fix belongs in SQL, statistics, object design, or buffer pool tuning.