Nullable partitioning columns

Db2 lets you use nullable columns as partitioning columns. The use of nullable columns has different implications for table-controlled partitioning than for index-controlled partitioning.

With table-controlled partitioning, Db2 can restrict the insertion of null values into a table with nullable partitioning columns, depending on the order of the partitioning key:

  • If the partitioning key is ascending, Db2 prevents the INSERT of a row with a null value for the key column, unless a partition is created that specifies MAXVALUE, which will hold the null values.
  • If the partitioning key is descending, Db2 prevents the INSERT of a row with a null value for the key column, unless a partition is created that specifies MINVALUE, which will hold the null values.

Examples

Begin general-use programming interface information.
Example

Assume that a partitioned table space is created with the following SQL statements: 

CREATE TABLESPACE TS IN DB
	USING STOGROUP SG
	NUMPARTS 4 BUFFERPOOL BP0;

CREATE TABLE TB (C01 CHAR(5),
						C02 CHAR(5) NOT NULL,
						C03 CHAR(5) NOT NULL)
	IN DB.TS
		PARTITION BY (C01)
			PARTITION 1 ENDING AT ('10000'),
			PARTITION 2 ENDING AT ('20000'),
			PARTITION 3 ENDING AT ('30000'),
			PARTITION 4 ENDING AT ('40000'));

Because the CREATE TABLE statement does not specify the order in which to put entries, Db2 puts them in ascending order by default. Db2 subsequently prevents any INSERT into the TB table of a row with a null value for partitioning column C01, because no partition specifies MAXVALUE. If the CREATE TABLE statement had specified the key as descending and the first partition specified MINVALUE, Db2 would subsequently have allowed an INSERT into the TB table of a row with a null value for partitioning column C01. Db2 would have inserted the row into partition 1.

With index-controlled partitioning, Db2 does not restrict the insertion of null values into a value with nullable partitioning columns.

Example
Assume that a partitioned table space is created with the following SQL statements: 
CREATE TABLESPACE TS IN DB
	USING STOGROUP SG
	NUMPARTS 4 BUFFERPOOL BP0;

CREATE TABLE TB (C01 CHAR(5),
						C02 CHAR(5) NOT NULL,
						C03 CHAR(5) NOT NULL)
	IN DB.TS;

CREATE INDEX PI ON TB(C01) CLUSTER
	(PARTITION 1 ENDING AT ('10000'),
	PARTITION 2 ENDING AT ('20000'),
	PARTITION 3 ENDING AT ('30000'),
	PARTITION 4 ENDING AT ('40000'));

Regardless of the entry order, Db2 allows an INSERT into the TB table of a row with a null value for partitioning column C01. If the entry order is ascending, Db2 inserts the row into partition 4; if the entry order is descending, Db2 inserts the row into partition 1. Only if the table space is created with the LARGE keyword does Db2 prevent the insertion of a null value into the C01 column.

End general-use programming interface information.