Scenario


A company launches a new product campaign. Customers can register and try out the product during the promotion period. Company wants to track how many customers have registered for this campaign. Customer can be a 'Registered (R)' customer or a 'Temporary (T)' customer. To accommodate this requirement, a new column has to be added to the CUSTOMER table to store the customer type, by the DBA. When the column is added to the table the default value will be 'T'. Once the customers registers himself for the campaign, the column value will be changed to 'R'.


Problem


As a customer table contains customer data it is not possible for the DBA to drop and recreate the table with the new column required for the campaign.


Solution


To solve the above problem, DB2 9.7 offers (ADD COLUMN) option in the (ALTER TABLE) command. Now the DBA can add the column in the table using the ADD COLUMN option in the ALTER TABLE command. DBA performs below steps to meet campaign requirement.

  1. Add column in a table using ALTER TABLE command.

  2. Update the column information using UPDATE TABLE command.

  3. REORG TABLE customer.

  4. Verify the new definition of table from syscat.columns.

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


Output


After running the SQL statements on the right pane,
Result tab 4 of 5 shows a new column TYPE added in the base table CUSTOMER.






















Result tab 5 of 5 shows the values inserted in the column TYPE