Views can be of two types based on the usage -

1. Read only views
2. Updatable views

Read only views -

The views which can be used to retrieve the data only.Views are not used to perform any Data manipulation statements on it to manipulate the data. Non updatable Views also called as Read only Views.

The READONLY column in SYSCAT.VIEWS indicates that the structure of the view forces it to be read only. If READONLY column value is ‘Y’, then the VIEW is read only.SYSCAT.VIEWS contains the VIEWs complete information.

Syntax -

CREATE VIEW View-name[Column-names] 
AS
(SELECT *[column-names] 
FROM database-name.tablespace-name.table-name) 
ON database-name.tablespace-name 
READONLY.

Example -

DB2 Code:

CREATE VIEW USER_DETAILS_V AS 
	(SELECT * FROM USER_DETAILS )
	ON MTH1DB.MFTHTS
	READ ONLY

Updatable views -

The views can be used to retrieve the data and along with that the views can be modified by applying the Data manipulation like insertion, deletion and update.

The READONLY column in SYSCAT.VIEWS indicates that the structure of the view forces it to be read only. If READONLY column value is ‘N’, then the VIEW is read only. SYSCAT.VIEWS contains the VIEWs complete information. Updatable views has below characteristics:

1. View should not be defined as read-only.
2. View should be derived from single base table. If any update applies to view then only it will apply to the base table.
3. View should not be created by using GROUP BY, HAVING etc.

Syntax -

CREATE VIEW View name[Column-names] 
AS
(SELECT *[column-names] 
FROM database-name.tablespace-name.table-name) 
ON database-name.tablespace-name

Example -

DB2 Code:

CREATE VIEW USER_DETAILS_V AS 
	(SELECT * FROM USER_DETAILS )
	ON MTH1DB.MFTHTS