DB2- V10.5 SQL Enhancements

Starting with DB2® Version 10.5, you can use the EXCLUDE NULL KEYS clause on the CREATE INDEX statement to reduce the size of sparse indexes.

An index that you create with the EXCLUDE NULL KEYS clause does not insert a key into the index object when all the columns in the key are null. Excluding null keys can help optimize storage and

performance for cases where you do not want queries to access data that is associated with null keys. For unique indexes, the enforcement of the uniqueness of table data causes rows with null index keys to be ignored.

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

In previous releases, the maximum number of bytes in a table row was depended on the page size of the table space. Any attempt to create a table whose row length exceeded the maximum record length for the page size resulted in an error (SQLSTATE 54010).

For example, in previous releases, you could not create the table in the following statement in a table space with a 4 KB page size because of the table’s large row size:

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

However, starting with DB2 Version 10.5, you can create table T1 in a table space with a 4 KB page size.