3.3.1 Specification format and rules for the ALTER VIEW statement

Re-create a viewed table.

Run the ALTER VIEW statement to re-create a viewed table in the following cases:

When you revalidate a viewed table

After you have removed the cause that invalidated a viewed table, the viewed table is revalidated when you run the ALTER VIEW statement to re-create the viewed table.
When you become unsure of the reason why a viewed table has been invalidated

If you run the ALTER VIEW statement for a viewed table for which the cause of invalidation has not been removed, the ALTER VIEW statement results in an error. In this case, an error message is output. From this error message, you can identify the reason why the viewed table has been invalidated.

Important: The ALTER VIEW statement cannot change the definition of a viewed table. To change the definition of a viewed table, use the DROP VIEW statement to delete the viewed table, and then use the CREATE VIEW statement to redefine the viewed table.

Organization of this subsection

(1) Specification format
(2) Explanation of specification format
(3) Privileges required at execution
(4) Rules
(5) Examples

(1) Specification format

ALTER-VIEW-statement ::= ALTER VIEW table-name RECREATE

To Page Top

(2) Explanation of specification format

● table-name

Specifies the name of the viewed table to be re-created. For rules on specifying a table name, see (2) Table name specification format in 6.1.5 Qualifying a name.

The following tables cannot be specified:

Base tables
Dictionary tables
System tables

● RECREATE

Specify this to re-create a viewed table.

To Page Top

(3) Privileges required at execution

To run the ALTER VIEW statement, the CONNECT privilege and the schema definition privilege are required.

To Page Top

(4) Rules

If the authentication identifier connected to the HADB server is different from the schema name of a viewed table, the ALTER VIEW statement results in an error.
Even if viewed tables that depend on the viewed table to be re-created have been defined, the viewed table is re-created when the ALTER VIEW statement is run. In this case, the viewed tables that depend on the re-created viewed table are invalidated.
Re-creating a viewed table by using the ALTER VIEW statement does not affect the access privileges for the viewed tables that depend on the re-created viewed table.
The viewed table specified in the ALTER VIEW statement is always re-created regardless of whether the viewed table is valid or invalid.
If the ALTER VIEW statement is used to re-create a viewed table, the number of columns or column names of the viewed table might be changed. For example, the following cases apply.

Example of defining viewed table V1:
```
CREATE VIEW "V1" AS SELECT * FROM "T1" WHERE "C1">100
```
- Case where the number of columns of a viewed table increases
  
  1. The CREATE VIEW statement is used to define viewed table V1.
  
  2. The ALTER TABLE statement is used to add a column (for example, column C5) to underlying table T1.
  
  3. The ALTER VIEW statement is used to re-create viewed table V1.
  
  In this case, because column C5 is added to viewed table V1, the number of columns in the viewed table increases.
- Case where a column name of a viewed table changes
  
  In step 2 of the preceding procedure, assume that, for example, you use the ALTER TABLE statement to change the column name of column C2. In this case, if you then use the ALTER VIEW statement to re-create viewed table V1, the column name of column C2 in viewed table V1 changes.
When the ALTER VIEW statement is used to re-create a viewed table, the access privilege settings for the underlying table might have been changed^# since the viewed table was defined. In such a case, the dependent privileges of the access privilege for the re-created viewed table might be revoked.
#
Either of the following changes applies.
- An access privilege with the grant option has been changed to an access privilege without the grant option.
- An access privilege with the grant option has been removed so that no access privilege is granted.
The following shows an example in which a dependent privilege of the access privilege for a re-created viewed table is revoked.

Example:

1.

HADB user A, who has the SELECT privilege with the grant option for table X.T1, defines viewed table A.V1 by using table X.T1 as the underlying table.

2.

HADB user A grants the SELECT privilege for viewed table A.V1 to another HADB user. The SELECT privilege that was granted to another HADB user becomes a dependent privilege.

3.

HADB user A has the SELECT privilege with the grant option for table X.T1 revoked. At this time, viewed table A.V1 is invalidated because table X.T1 is an underlying table of the viewed table.

4.

To revalidate viewed table A.V1, the SELECT privilege without the grant option for table X.T1 is granted to HADB user A. The SELECT privilege with the grant option for table X.T1 that HADB user A had when defining viewed table A.V1 in step 1 has been changed to the SELECT privilege without the grant option.

5.

The ALTER VIEW statement is run to re-create viewed table A.V1.

Because the SELECT privilege was changed to a SELECT privilege without the grant option in step 4, the SELECT privilege that was granted to another HADB user and became a dependent privilege in step 2 is revoked.

To Page Top

(5) Examples

Example

Because a viewed table (VSHOPSLIST) for the shops table was invalidated, the ALTER VIEW statement is run to revalidate VSHOPSLIST.

ALTER VIEW "VSHOPSLIST" RECREATE

To Page Top