Scenario


Application developer also uses the type 'Registered' and 'Temporary' rather than using 'R' and 'T' in the application. Because of the mismatch in column value in the column and the application, application will not run.


Problem


As the occurrences of CUSTOMER_TYPE column in the application is high the DBA has to change data type of column FROM CHAR to VARCHAR and update the column value from 'R' to 'Registered' and 'T' to 'Temporary' to get the application working.


Solution


To solve the above problem DB2 9.7 offers SET DATA TYPE Option. Now the DBA can change the data type of a column in the table using SET DATA TYPE option in the ALTER TABLE command. DBA performs below steps to accommodate this requirement.

  1. ALTER data type of column CUSTOMER_TYPE to VARCHAR using SET DATA TYPE option in ALTER TABLE command.

  2. CALL ADMIN_REVALIDATE_DB_OBJECTS to revalidate the object.

  3. Check the new definition of table from syscat.columns for verification.

  4. Fetch the rows from customer table using SELECT command.


Output


After running the SQL scripts on the right pane,
Result tab 4 shows a datatype of column nameTYPE change from CHAR to VARCHAR in the base table CUSTOMER.