Using the ALTER COLUMN SET DATA TYPE extended support
ALTER COLUMN SET DATA TYPE
was present in the earlier versions of DB2 as well, supporting SMALLINT
to INTEGER, INTEGER
to BIG, REAL
to DOUBLE
, and BLOB(n)
to BLOB(n+m)
conversions; data types could not be cast to smaller data types. In DB2 9.7, the ALTER TABLE
statement is extended to support all compatible types, from casting to small data types.
In some cases, data may be truncated upon altering the column data type such as DECIMAL
to INTEGER
. To avoid the data loss issues, DB2 9.7 scans the column data before the change and writes the error messages, such as overflow errors and truncation errors, into the notification log.
The column
data type is set to a new data type only if there is no error reported during the column data scan phase.
Getting ready
To perform the ALTER COLUMN SET DATA TYPE
action, the user needs to have one of the following authorizations on the object:
ALTER
privilegeCONTROL
privilegeALTERIN
privilege on the schemaDBADM
authority
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...
Casting of
VARCHAR, VARGRAHIC, BLOB, CLOB
, andDBCLOB
data types to types smaller than the current one will not scan the table for a compatibility check, as this casting is not supported by DB2 9.7In the case of range partitioning tables, the
string
data type cannot be altered if it's a part of the range partitioning keyThe identity column cannot be altered in DB2 9.7
In the case of the database partitioning feature, if the altering column is a part of the distribution key, then the new data type must meet the following listed conditions:
Same data type as the current one
Same column length
FOR BIT DATA
cannot be modified in the case ofCHAR
andVARCHAR