DB2 10.5 for Linux, UNIX, and Windows

Extended row size

Extended row size support allows you to create a table where its row length can exceed the maximum record length for the page size of the table space.

Rows of table data are organized into blocks that are called pages which can be four sizes: 4, 8, 16, and 32 kilobytes. All tables that are created within a table space of a particular size have a matching page size.

Table 1. Limits for number of columns and row size in each table space page size
Page size	Row size limit	Column count limit
4K	4 005	500
8K	8 101	1 012
16K	16 293	1 012
32K	32 677	1 012

Without extended row size support, the maximum number of bytes allowed in a table row is dependant on the page size of the table space. Any attempt to create a table whose row length exceeds the maximum record length for the page size results in an error (SQLSTATE 54010). For example, the following table could not be created in a 4K page size table space because of its row size.

CREATE TABLE T1 (C1 INTEGER, C2 VARCHAR(5000))

The row size for this table is 5010 bytes; calculated as 5 bytes (C1 plus nullable column overhead) + 5005 bytes (C2 plus varying length column overhead)

With extended row size support, tables that contain large rows that exceed the maximum record length for the page size of the table space can be created. With extended row size support, the table T1 can be created in a 4K page size table space.

Extended row size support can be used to:

Migrate tables that have row sizes that exceed 32K to DB2® Version 10.5.
Improve the performance of applications where most of data rows can fit on a smaller page, but the table definition requires a bigger page size.
Create tables with more VARCHAR or VARGRAPHIC columns. The maximum number of columns does not change, but the ability to exceed the maximum record length for the page size allows for more columns.

Existing tables can be altered to take advantaged of extended row size support. For example, more columns can be added or the length of character and graphic string columns can be increased. Without extended row size support, these changes would exceed the row size for the page size of the table space.

Any row-organized tables created by a user support extended row size except for range clustered tables (RCT).

Working with tables with extended row size

Enabling extended row size support for a table requires that:

The extended_row_sz database configuration parameter must be set to ENABLE.
The table definition must contain at least one varying length string column (VARCHAR or VARGRAPHIC).
The row size of the table cannot exceed 1048319 bytes (SQLSTATE 54010).
Queries requiring an explicit or implicit system temporary table with extended rows needs a system temporary table space that can fully contain the minimum width of the row. The minimum width of the row is calculated in the same way as the maximum width except that all VARCHAR and VARGRAPHIC columns are assumed to have a length of 1.

Tables with extended row size support can be identified by checking the EXTENDED_ROW_SIZE column of the SYSCAT.TABLES catalog view.

Inserting data into a table with extended row size support: When a data row is inserted or updated in a table with extended row size support and the physical data row length exceeds the maximum record length for the table space, a subset of the varying length string columns (VARCHAR or VARGRAPHIC) is stored as large object (LOB) data outside of the data row. The table column in the base row is replaced by a descriptor that is 24 bytes in size. For columns of VARCHAR(n) where n is less than or equal to 24, or VARGRAPHIC(n) where n is less than or equal to 12, data remains in the base data row.
If some VARCHAR or VARGRAPHIC data is stored out of row in a LOB data object, there is no change to the data type. The VARCHAR or VARGRAPHIC column does not become a LOB column. Operations like IMPORT, EXPORT, and LOAD do not require any LOB modifiers to work with the VARCHAR or VARGRAPHIC data.

Examples

In the following examples the extended_row_sz database configuration parameter is set to ENABLE.

Creating a table without specifying a table space

The following CREATE TABLE statement is issued:

CREATE TABLE T1 (C1 INT, C2 VARCHAR(4000));

The row size for T1 is 4010 bytes. If a table space with a page size of at least 8K cannot be found, then T1 is created in a 4K page size table space and if required some varying length data might be stored out of row. If there is no 4K page size table space that can be used, an error is returned (SQLSTATE 42727).

Adding a column

Table T1 was created with the following CREATE TABLE statement.

CREATE TABLE T1 (C1 INT, C2 VARCHAR(3995)) in TS1;

Table space TS1 has a 4K page size and so table T1 is created without the need to store any data out of row because its byte count is 4005. A new column is added to table T1 with the following ALTER TABLE statement:

ALTER TABLE T1 ADD C3 CLOB(1M);

The byte count for the table now exceeds the maximum record length. The ALTER TABLE is successful because the extended_row_sz database configuration parameter is set to ENABLE.

Deactivating VALUE COMPRESSION

Table T1 was created with the following CREATE TABLE statement.

CREATE TABLE T1 (C1 INT, C2 VARCHAR(1993), C3 VARCHAR(2000))
 IN TS1 VALUE COMPRESSION;

Table space TS1 has a 4K page size and table T1 has a byte count of 4005 and so no data is stored out of row. Compression on table T1 is deactivated with the following ALTER TABLE statement:

ALTER TABLE T1 DEACTIVATE VALUE COMPRESSION;

This changes the byte count of table T1 to 4008. The ALTER TABLE is successful because the extended_row_sz database configuration parameter is set to ENABLE. If required some varying length data might be stored out of row.

If VALUE COMPRESSION is reactivated, any subsequent inserts of VARCHAR data are stored in the base row. All rows that were inserted when VALUE COMPRESSION was deactivated remain out of row until they are updated or table T1 is reorganized.

Creating an index

Table T1 was created with the following CREATE TABLE statement.

CREATE TABLE T1
 (C1 INT, C2 VARCHAR(1000),
  C3 VARCHAR(1000),
  C4 VARCHAR(1000),
  C5 VARCHAR(1000))
 IN TS1;

Table space TS1 has a 4K page size and table T1 is successfully created because the extended_row_sz database configuration parameter is set to ENABLE. No special handling is required to create indexes on table T1.

CREATE INDEX I1 on T1 (C2);
CREATE INDEX I2 on T1 (C3);
CREATE INDEX I3 on T1 (C4);
CREATE INDEX I4 on T1 (C5);