Modifying Schema, View, Table, and Routine Definitions › Maintaining Views › Modifying a View

Modifying a View

To modify a view, use the SQL DDL DROP VIEW statement to drop the view and then use the SQL DDL CREATE VIEW statement to re-add the view.

Before modifying a view, you can use the SELECT SYNTAX FROM SYSCA.SYNTAX statement to display the syntax used to create a view.

select syntax from sysca.syntax
  where schema=HR
  and table=EMP-SALARY;

Note: For more information about SYSCA.SYNTAX table, see the CA IDMS SQL Reference Guide.

Example

In the following example, the syntax for the view EMP_HOME_INFO is displayed using the SELECT SYNTAX statement. The view is then dropped (DROP) and re-added (CREATE) with an additional column (CITY).

This SELECT SYNTAX statement:

select syntax from sysca.syntax
   where schema=demoempl
   and table=emp_home_info;

Displays this view syntax:

create view emp_home_info
    as select emp_id, emp_lname, emp_fname, phone
        from employee;

DROP VIEW AND CREATE VIEW are used to modify the view.

drop view emp_home_info;
create view emp_home_info
  as select emp_id, emp_lname, emp_fname, phone, city
     from employee;