DB2 Version 9.7 for Linux, UNIX, and Windows

Table page sizes

Rows of table data are organized into blocks called pages. Pages can be four sizes: 4, 8, 16, and 32 kilobytes. Table data pages do not contain the data for columns defined with LONG VARCHAR, LONG VARGRAPHIC, BLOB, CLOB, DCLOB, or XML data types, unless the LOB or XML document is inlined through the use of 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 through the use of the INLINE LENGTH option of the CREATE and ALTER TABLE statements.

You can create buffer pools or table spaces that have page sizes of 4 KB, 8 KB, 16 KB, or 32 KB. All tables 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.

You can have a maximum of 1012 columns when you are using an 8 KB, 16 KB, or 32 KB page size. You can have a maximum of 500 columns for a 4 KB page size. The maximum of rows that you can have per page is 255 for SMS and non-large DMS table spaces. For a large table space, the limit is based on the page size and table definition.

Maximum row lengths vary, depending on page size used:

When the page size is 4 KB, the row length can be up to 4 005 bytes.
When the page size is 8 KB, the row length can be up to 8 101 bytes.
When the page size is 16 KB, the row length can be up to 16 293 bytes.
When the page size is 32 KB, the row length can be up to 32 677 bytes.

To determine the page size for a table space you must consider the following:

For OLTP applications that perform random row read and write operations, a smaller page size is usually preferable, because it consumes less buffer pool space with unwanted rows.
For DSS applications that access large numbers of consecutive rows at a time, a larger page size is usually better, because it reduces the number of I/O requests that are required to read a specific number of rows. There is, however, an exception to this. If your row size is smaller than pagesize / maximum rows, there will be consumed space on each page. In this situation, a smaller page size might be more appropriate.

Larger page sizes might allow you to 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.