Db2 – Dropping a table/column automatically drops views using it – how to warn instead

db2db2-midrangedrop-tableview

If I have a view that uses a column, and I remove that column/table, the view is dropped automatically.

Is there a setting I can change such that it instead prevents the removal of the column/table, warning that it has dependencies?

Operation System: I5 or IBM i

Operating System Version: V7R1M0

[From comment] The view is not dropped; it may be marked as invalid. 

SELECT * FROM QSYS2.SYSTABLES
WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' and table_name = 'MYVIEW';
--returns 1 row

ALTER TABLE MYLIB.MYTABLE DROP COLUMN TEST2;

SELECT * FROM QSYS2.SYSTABLES
WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' and table_name = 'MYVIEW';
--returns 0 rows

…That looks to me like it was dropped?

Best Answer

For i-series, at least for 7.1, you can try the RESTRICT clause of DROP COLUMN.

There is also a RESTRICT clause for DROP TABLE.

Per documentation

RESTRICT

Specifies that the column cannot be dropped if any views, indexes, triggers, or constraints are dependent on the column. 1

If all the columns referenced in a constraint are dropped in the same ALTER TABLE statement, RESTRICT does not prevent the drop.

Related Question