Calculations for record lengths and pages
In Db2, a record is the storage representation of a row. An important factor in estimating the required amount of space for a table is the size of the records.
Records are stored within pages that are 4 KB, 8 KB, 16 KB, or 32 KB. Generally, you cannot create a table in which the maximum record size is greater than the page size.
- Normalizing your entities
- Using larger page sizes
- Using LOB data types if a single column in a table is greater than 32 K
- A 6-byte prefix
- One additional byte for each column that can contain null values
- Two additional bytes for each varying-length column or ROWID column
- Six bytes of descriptive information in the base table for each LOB column
- Six bytes of descriptive information in the base table for each XML column. Or, if the column can contain multiple versions of an XML document, then 14 bytes of descriptive information for each XML column.
The sum of each column's length is the record
length, which is the length of data that is physically stored
in the table. You can retrieve the value of the AVGROWLEN column in
the SYSIBM.SYSTABLES catalog table to determine the average length
of rows within a table. The logical record length can
be longer, for example, if the table contains LOBs. 
- A 22-byte header
- A 2-byte directory entry for each record that is stored in the page
The maximum space available to store records in a 4 KB page is 4056 bytes. Achieving that maximum in practice is not always simple. For example, if you are using the default values, the LOAD utility leaves approximately 5 percent of a page as free space when loading more than one record per page. Therefore, if two records are to fit in a page, each record cannot be longer than 1927 bytes (approximately 0.95 × 4056 × 0.5).
Furthermore, the page size of the table space in which the table is defined limits the record length. If the table space is 4 KB, the record length of each record cannot be greater than 4056 bytes. Because of the eight-byte overhead for each record, the sum of column lengths cannot be greater than 4048 bytes (4056 minus the eight-byte overhead for a record).
Db2 provides three larger page sizes to allow for longer records. You can improve performance by using pages for record lengths that best suit your needs.
As shown in the following table, the maximum record size for each page size depends on the size of the table space, whether the table is enabled for hash access, and whether you specified the EDITPROC clause.
| Table type | 4 KB page | 8 KB page | 16 KB page | 32 KB page |
|---|---|---|---|---|
| Non-hash table | 4056 | 8138 | 16330 | 32714 |
| Non-hash table with EDITPROC | 4046 | 8128 | 16320 | 32704 |
| Hash table (hash home page) | 3817 | 7899 | 16091 | 32475 |
| Hash table with EDITPROC (hash home page) | 3807 | 7889 | 16081 | 32465 |
Creating a table using CREATE TABLE LIKE in a table
space of a larger page size changes the specification of LONG VARCHAR
to VARCHAR and LONG VARGRAPHIC to VARGRAPHIC. You can also use CREATE
TABLE LIKE to create a table with a smaller page size in a table space
if the maximum record size is within the allowable record size of
the new table space.