Drop Column Support

I noticed that the DB2 11 announcement letter includes the customer requirement to drop a column. I was surprised that this feature had eight different requests to support either drop column or remove column through alter.

The announcement letter lists these requests:

• MR00031816 Drop of a column in a DB2-table
• MR00039130 Provide support for the "Drop Column" command
• MR00039691 DB2 - Remove columns from tables using ALTER instead of DROP
• MR00052327 Remove erroneous added column
• MR00063646 Ability to drop columns (BDUG)
• MR00070625 Drop column capability in DB2
• MR0530057018 DROP COLUMN
• MR1005105716 Drop Column

Some of the requests above cite a drop column command. While I didn't find such a command in the Command Guide, the SQL Reference Guide does include the ALTER TABLE DROP COLUMN statement. The syntax for this statement is:

       ALTER TABLE table-name DROP COLUMN column-name RESTRICT

When using the ALTER TABLE DROP COLUMN clause, no other ALTER TABLE option is allowed within that statement.

A column cannot be dropped if any of the following conditions are true:

• The containing table space is not a universal table space
• The table is a created global temporary table
• The table is a system-period temporal table
• The table is a history table
• The table is an archive-enabled table
• The table is an archive table
• The table has an edit procedure or a validation exit procedure
• The table contains check constraints
• The table is a materialized query table
• The table is referenced in a materialized query table definition
• The column is defined as a security label column
• The column is an XML column
• The column is a DOCID column
• The column is a hidden ROWID column
• The column is defined as ROWID GENERATED BY DEFAULT, and the table contains a hidden ROWID column
• The column is a ROWID column on which there is a dependent LOB column
• The column is part of the table partitioning key
• The column is part of the hash key
• All of the remaining columns in the table are hidden
• A view that is dependent on the table has INSTEAD OF triggers
• A trigger is defined on the table
• Any of these objects are dependent on the table:
• Extended indexes
• Row permissions
• Column masks
• Inline SQL table functions

 

Most of the time this change will be pending and the table space will be placed in advisory REORG-pending status (AREOR). However, if no table space data set has been created, the change will take effect immediately.

The RESTRICT parameter specifies that the column cannot be dropped if any views, indexes, unique constraints or referential constraints are dependent on the column.

I’m not sure why this has been such a highly requested feature. Have you ever added some columns to a table in production and then later had to take them off the table? I’m curious if others have found themselves in this situation.