DB2 10.5 for Linux, UNIX, and Windows

Table page sizes

Rows of table data are organized into blocks that are called pages. Pages can be four sizes: 4, 8, 16, and 32 KB. Table data pages do not contain the data for columns that are defined with LONG VARCHAR, LONG VARGRAPHIC, BLOB, CLOB, DCLOB, or XML data types. An exception is if the LOB or XML document is inlined by using INLINE LENGTH option of the column. The rows in a table data page do, however, contain a descriptor of these columns.

Note: Some LOB and XML data can be placed into the base table row by using the INLINE LENGTH option of the CREATE and ALTER TABLE statements.

All tables that are created within a table space of a particular size have a matching page size. A single table or index object can be as large as 64 TB, assuming a 32-KB page size.

Larger page sizes can reduce the number of levels in the index. Larger pages support rows of greater length. Using the default of 4-KB pages, tables are restricted to 500 columns. Larger page sizes (8 KB, 16 KB, and 32 KB) support 1012 columns. The maximum size of the table space is proportional to the page size of the table space.

Page size defines the size of pages that are used for the table space. The page size limits the row length and column count of tables according to the figures shown in Table 1.

Table 1. Implications of page size with regular table space
Page size	Row count limit	Row size limit	Column count limit
4 KB	255	4 005 bytes	500
8 KB	255	8 101 bytes	1 012
16 KB	255	16 293 bytes	1 012
32 KB	255	32 677 bytes	1 012

Note: A large table space can support more than 255 rows per data page. A table that is created in a large table space can be larger than a table created in a regular table space. In addition, a large table space stores all permanent data just as a regular table space does. The result is a better use of space on data pages.

To determine the page size for a table space, consider the following points:

For OLTP applications that run random row read and write operations, a smaller (4- or 8-KB) page size is preferable. It takes less buffer pool space with unwanted rows. A 16- or 32-KB page size is appropriate for analytics, and a 32-KB page size is recommended for column-organized tables.
For DSS applications that access large numbers of consecutive rows at a time, a larger page size is better. A larger page size reduces the number of I/O requests that are required to read a specific number of rows. There is, however, an exception to this rule. If your row size is smaller than pagesize / maximum rows, there is used space on each page. In this situation, a smaller page size might be more appropriate.