DROP/CREATE generated instead of ALTER ADD COLUMN when doing a RC/Migrator ALTER strategy.

Products

• RC/Migrator for DB2 for z/OS
• RC/Update for DB2 for z/OS

Issue/Introduction

When trying to Alter a table to add columns with a NOT NULL definition RC/Migrator for Db2 for z/OS (RCM) will generate DDL top DROP/CREATE the table
when an ALTER ADD COLUMN definition is expected. NOT NULL prevents the column from containing null values.

Cause

According to the IBM SQL Reference guide:

If NOT NULL is specified, the DEFAULT clause must be used to specify a nonnull default value for the column unless the column has a row ID data type or is an identity column.
For a ROWID column, NOT NULL must be specified, and DEFAULT must not be specified. For an identity column, although NOT NULL can be specified, DEFAULT must not be specified.

Attempts to add a column to a table with NOT NULL, and no default, will result in SQL -104. For this reason, the table must be dropped and created.

Resolution

Attempts to add a column with NOT NULL WITH DEFAULT will generate the ALTER TABLE...ADD COLUMN statements.

Attempts to add a column with NOT NULL and no default will generate a DROP/CREATE of the table and any dependents, data, and authorizations are automatically restored
and all changes, including the impact on any indexes using columns, are automatically propagated to any dependent object types in order to leave the database in a consistent state.

Additional Information

Updating tables.

Refer to subsection "Alteration Support"