SQL Before Drop a Column Here are The Conditions to Check

ALTER statement we can use in DB2 SQL query to drop a column.

ALTER TABLE table_name
DROP COLUMN column_name

But, there are lot other conditions involved, which column we can drop or not.

  • 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 the following objects are dependent on the table:
  • Extended indexes
  • Row permissions
  • Column masks
  • Inline SQL table functions

When you have written SQL query to drop a column,but it is not executed. Then let us see what will happen:

ALTER TABLE DROP COLUMN is considered a pending definition change, at the time that the ALTER statement is executed, semantic validation and authorization checking are performed as usual.

However, the drop is not applied to the current definition or data at the time of the ALTER (that is, catalog and data are untouched). An entry is recorded in the SYSIBM.SYSPENDINGDDL catalog table for the pending drop column, and the table space is placed in an advisory REORG-pending (AREOR) state.

Related Posts

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.