DB2® provides many
options for data page sizes.
About this task
The size of the data page is determined by the buffer
pool in which you define the table space. For example, a table space
that is defined in a 4 KB buffer pool has 4 KB page sizes, and one
that is defined in an 8 KB buffer pool has 8 KB page sizes.
Data
in table spaces is stored and allocated in record segments. Any record
segment can be 4 KB in size, or the size determined by the buffer
pool (4 KB, 8 KB, 16 KB, or 32 KB). In a table space with 4 KB record
segments, an 8 KB page size requires two 4 KB records, and a 32 KB
page size requires eight 4 KB records.
Procedure
To choose data page sizes, use the following approaches:
- Use the default of 4 KB page sizes as a starting point
when access to the data is random and only a few rows per page are
needed. If row sizes are very small, using the 4 KB page
size is recommended.
- Use larger page sizes in the following situations:
- When the size of individual rows is greater than 4 KB
- In this case, you must use a larger page size. When considering
the size of work file table spaces, remember that some SQL operations,
such as joins, can create a result row that does not fit in a 4 KB
page. Therefore, having at least one work file that has 32 KB pages
is recommended. (Work files cannot use 8 KB or 16 KB pages.)
- When you can achieve higher density on disk by choosing a larger
page size
- For example, only one 2100-byte record can be stored in a 4 KB
page, which wastes almost half of the space. However, storing the
record in a 32 KB page can significantly reduce this waste. The downside
with this approach is the potential of incurring higher buffer pool
storage costs or higher I/O costs—if you only affect a few rows, you
are bringing a bigger chunk of data from disk into the buffer pool.
Using
8 KB or 16 KB page sizes can let you store more data on your disk
with less impact on I/O and buffer pool storage costs. If you use
a larger page size and access is random, you might need to go back
and increase the size of the buffer pool to achieve the same read-hit
ratio you do with the smaller page size.
- When a larger page size can reduce data sharing overhead
- One way to reduce the cost of data sharing is to reduce the number
of times the coupling facility must be accessed. Particularly for
sequential processing, larger page sizes can reduce this number. More
data can be returned on each access of the coupling facility, and
fewer locks must be taken on the larger page size, further reducing
coupling facility interactions.
If data is returned from the coupling
facility, each access that returns more data is more costly than those
that return smaller amounts of data, but, because the total number
of accesses is reduced, coupling facility overhead is reduced.
For
random processing, using an 8 KB or 16 KB page size instead of a 32
KB page size might improve the read-hit ratio to the buffer pool and
reduce I/O resource consumption.