DB2 UDB Partitioned Database - Unique Index

In the absence of the DBA that usually works on DB2 UDB, I tried to create an unique index on a table that is defined on a partitioned database. I realized that it's not very straight forward to do so.

From DB2 manual: "There are performance advantages to creating a table across several database partitions in a partitioned database environment. Creating a table that will be a part of several database partitions is specified when you are creating the table. There is an additional option when creating a table in a partitioned database environment: the distribution key. A distribution key is a key that is part of the definition of a table. It determines the database partition on which each row of data is stored.

You must be careful to select an appropriate distribution key because it cannot be changed later. Furthermore, any unique indexes (and therefore unique or primary keys) must be defined as a superset of the distribution key. That is, if a distribution key is defined, unique keys and primary keys must include all of the same columns as the distribution key (they might have more columns)."

This is where I got totally lost "any unique indexes (and therefore unique or primary keys) must be defined as a superset of the distribution key. That is, if a distribution key is defined, unique keys and primary keys must include all of the same columns as the distribution key (they might have more columns)". I was trying create the unique index on a column which was not the hash key and I was getting an SQL0270N Reason Code 1. After including the hash key along with the other key, it worked.

UNIQUE WHERE NOT NULL - Explained

I always had confusion over the meaning of indexes created with UNIQUE WHERE NOT NULL clause on DB2 for z/OS. The manuals don't seem to do a good job explaining it. I understand it now and here is how it works.

It means its ok to have multiple rows with null values in columns included in the index, but its not ok to have rows where the combination of the columns included in the index is not unique and all the columns are not null. I tested this on a three column index. I was able to insert rows rows shown below just fine, the only duplicate exception I got was where column a, b, c were the same value for two different rows and none of the 3 columns contained a null value.

  COL1  COL2  COL3
  ----  ----  ----
   A     B     C
   A     B     -
   A     B     -
   A     -     -
   A     -     -
   -     -     -
   -     -     -

CREATE UNIQUE WHERE NOT NULL INDEX PRE.IDX1
       ON PRE.TEST
       (COL1   ASC
       ,COL2   ASC
       ,COL3   ASC)
       USING STOGROUP ASTERISK
             PRIQTY 12
             SECQTY 10800
             ERASE NO
       FREEPAGE 0
       PCTFREE 0
       BUFFERPOOL BP1
       CLOSE YES
       PIECESIZE 2097152  K;

CREATE TABLE PRE.TEST
       (COL1     CHARACTER(1)   FOR SBCS DATA WITH DEFAULT NULL
       ,COL2     CHARACTER(1)   FOR SBCS DATA WITH DEFAULT NULL
       ,COL3     CHARACTER(1)   FOR SBCS DATA WITH DEFAULT NULL
       )
       IN DB.TS;