IBM PureData System for Analytics, Version 7.1

ALTER VIEW and ALTER VIEWS ON

Use the ALTER VIEW command to change the name or the owner of a view or to refresh or suspend a materialized view. Use the ALTER VIEWS ON command to refresh or suspend all the materialized views for a base table. Views are read-only. The system does not allow an insert, update, or delete on a view.

Syntax

To alter a view:

ALTER VIEW <viewname> [ RENAME TO <newname> ] 
  [ OWNER TO <newowner> ] [SET PRIVILEGES TO <view> ]

To alter a materialized view:

ALTER VIEW <viewname> MATERIALIZE {REFRESH|SUSPEND}

To alter all the materialized views for a base table:

ALTER VIEWS ON <base_table> MATERIALIZE {REFRESH|SUSPEND}

Inputs

The ALTER VIEW command takes the following inputs:

Table 1. ALTER VIEW inputs
Input	Description
<viewname>	The name of the view.
<newowner>	The new owner of the view.
<newname>	The new name of the view.
SET PRIVILEGES TO <view>	Copy the privileges from one view to another.
MATERIALIZE	The specified view is a materialized view: REFRESH Re-create the materialized table from the base table. SUSPEND Truncate the materialized table and redirect all queries against the materialized view to the base table.
VIEWS ON <base_table>	Refresh or suspend all materialized views that are associated with the base table.

Output

The ALTER VIEW command has the following output:

Table 2. ALTER VIEW output
Output	Description
ALTER VIEW	The command was successful.

Privileges

The ALTER VIEW command requires the following privileges:

For views

You must be the admin user, the view owner, the owner of the database or schema where the view is defined, or your account must have Alter privilege for theview or for the View object class. If you are changing the view owner, you must have List access to the user account.

For materialized views

You must be the admin user, or the owner of the database or schema where the view is defined. For all other users, the following table lists the privileges required.

Table 3. Materialized view privileges
Task	Privilege
Create an SPM view	Your account must have the Create Materialized View administration privilege.
Alter an SPM view	Your account must have the Alter object privilege for a specific view or the View object class.
Drop an SPM view	Your account must have the Drop object privilege for a specific view or the View object class.
Select from an SPM view	Your account must have the Select object privilege for a specific view or the View object class.
Alter Views on a table	Your account must have the Insert object privilege for a specific table or the Table object class.
List on SPM views	Your account must have the List object privilege for a specific view or the View object class.

Usage

The following provides sample usage.

Rename a view:

MYDB.SCH1(USER)=> ALTER VIEW emp RENAME TO employees

Change the owner of a view:

MYDB.SCH1(USER)=> ALTER VIEW emp OWNER TO john