Below are the SQL statements a DBA is planning to execute on a database:
CREATE TABLE t1 (name VARCHAR (1));
INSERT INTO t1 VALUES ("˜A'), ("˜B'), ("˜C'), ("˜D');
ALTER TABLE t1 ALTER COLUMN name SET DATA TYPE INT;
What will be the outcome of the last statement?
The statement will fail due to an incompatible data type error. In SQL, altering a column to change its data type from VARCHAR to INT when it contains character data is not allowed because the existing character data cannot be interpreted or converted directly to integers.
The good answer is "B- The statement will fail due to an incompatible data type error". Indeed, the reference with MySQL is totally wrong. With Db2, you get the error SQL0190N ALTER TABLE "T1" specified attributes for column "NAME" that are not compatible with the existing column. SQLSTATE=42837
Answer is B [db2inst2@vbox1 ~]$ db2 "CREATE TABLE t11 (name VARCHAR (1))" DB20000I The SQL command completed successfully. [db2inst2@vbox1 ~]$ db2 "INSERT INTO t11 VALUES ('A'), ('B'), ('C'), ('D')" DB20000I The SQL command completed successfully. [db2inst2@vbox1 ~]$ db2 "ALTER TABLE t11 ALTER COLUMN name SET DATA TYPE INT" DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0190N ALTER TABLE "T11" specified attributes for column "NAME" that are not compatible with the existing column. SQLSTATE=42837 [db2inst2@vbox1 ~]$