One of the often problems when migrating databases from Oracle to DB2 were with unique indexes. Oracle does not include and consider NULL keys when checking for uniqueness of values, DB2 does - or better: did. Starting with DB2 10.5 it is possible now to specify whether to INCLUDE NULL KEYS (the default) or to EXCLUDE NULL KEYS when CREATE INDEX is executed. This allows to have either the classic DB2 semantics or to mimic the behavior of other database systems. And thereby it reduces migration effort further.
In the following I tried the new feature by creating two tables, T1 and T2, with a unique index each. On T2 the index uses "exclude null keys". So let's see how they respond to my attempts to insert some values...:
create table t1(id int, s varchar(60))
DB20000I The SQL command completed successfully.
create table t2(id int, s varchar(60))
DB20000I The SQL command completed successfully.
create unique index t1_id_u_idx on t1(id)
DB20000I The SQL command completed successfully.
create unique index t2_id_u_idx on t2(id) exclude null keys
DB20000I The SQL command completed successfully.
insert into t1 values(1,'one')
DB20000I The SQL command completed successfully.
insert into t2 values(1,'one')
DB20000I The SQL command completed successfully.
insert into t1 values(2,'two')
DB20000I The SQL command completed successfully.
insert into t2 values(2,'two')
DB20000I The SQL command completed successfully.
insert into t1 values(2,'two again')
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0803N One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains
table "HLOESER.T1" from having duplicate values for the index key.
SQLSTATE=23505
insert into t2 values(2,'two again')
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0803N One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains
table "HLOESER.T2" from having duplicate values for the index key.
SQLSTATE=23505
insert into t1 values(null,'null')
DB20000I The SQL command completed successfully.
insert into t2 values(null,'null')
DB20000I The SQL command completed successfully.
insert into t1 values(null,'null again')
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0803N One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains
table "HLOESER.T1" from having duplicate values for the index key.
SQLSTATE=23505
insert into t2 values(null,'null again')
DB20000I The SQL command completed successfully.
As can be seen, both indexes check uniqueness of non-NULL values. When I try to insert the value "2" again, an error is returned. Both indexes accept a NULL value. However, on T1 only a single NULL value can be inserted and then a violation of the uniqueness criteria is reported. On the second table we are able to insert another NULL value without problems because NULL keys are not included in the index.
Try it yourself, best by migrating a database from Oracle to DB2... ;-)