Getting ready

To perform the ALTER COLUMN SET DATA TYPE action, the user needs to have one of the following authorizations on the object:

How to do it...

We can do it using ALTER COLUMN SET DATA TYPE as follows:

ALTER TABLE SALES ALTER COLUMN SALES SET DATA TYPE SMALLINT ALTER TABLE EMPLOYEE ALTER COLUMN COMM SET DATA TYPE INTEGER 

The preceding SQL statements try to change the data type from one to another.

The reason for the failure in the case of second ALTER COLUMN statement is because an MQT "ADEFUSR" is referring to the base table EMPLOYEE.

db2 "? SQL0270N" 21 

A column cannot be dropped or have its length, data type, security, or nullability altered on a table that is a base table for a materialized query table.

We can change the COLUMN type in the base table as follows:

CREATE TABLE BTABLE (C1 INT, C2 INT); CREATE VIEW v1 AS SELECT C1, C2 FROM BTABLE; CREATE VIEW v2 AS SELECT C1, C2 FROM V1; ALTER TABLE BTABLE ALTER COLUMN C1 SET DATA TYPE SMALLINT; REORG TABLE BTABLE; SELECT SUBSTR(OBJECTNAME,1,20) NAME, SQLCODE, SQLSTATE, \ OBJECTTYPE FROM SYSCAT.INVALIDOBJECTS WHERE OBJECTNAME IN ('V1','V2') SELECT * FROM v2; 

How it works...

The ALTER COLUMN SET DATA TYPE statement downcasts the data type INT to SMALLINT, which invalidates the views V1 and V2. Since we have AUTO_REVAL set to DEFERRED, the dependent objects become invalid until used.

As soon as we access the dependent objects after altering the column data type, objects become valid and you won't see them in the SYSCAT.INVALIDOBJECTS system catalog table.

There's more...