Oracle to DB2 Migration - NULL Unique Constraint

Oracle allows columns of the unique constraint to be NULL.

CREATE  TABLE MYTABLE

  (

         ID      VARCHAR2(9),

         CD     VARCHAR2(6),

         UNIQUE (ID, CD)

  );

This is OK in Oracle. But if you run the same script in DB2, you will have an error with the error code, SQLCODE=-542. The reason is you must explicitly declare all the columns of the unique constraint as NOT NULL in DB2. So only the following script will work. 

CREATE  TABLE MYTABLE

  (

         ID      VARCHAR2(9)   Not NULL,

         CD     VARCHAR2(6)   Not NULL,

         UNIQUE (ID, CD)

  );

But with the above script, if you are migrating the existing data from Oracle to DB2, the migration will fail with the error code SQLCODE=-407 if the existing data includes NULL in one of those columns because both columns of the above script do not allow NULL values. 

The good news is that starting with the DB2 10.5, EXCLUDE NULL KEYS on unique index is introduced. So the problem can be solved by creating a UNIQUE INDEX separately as the following. 

CREATE  TABLE MYTABLE

  (

         ID      VARCHAR2(9),

         CD     VARCHAR2(6)

  );

CREATE   UNIQUE  INDEX   MYTABLE_U01  ON   MYTABLE (ID, CD)   EXCLUDE NULL KEYS;