VIEW

Summary

Syntax
SYSCAT.VIEWS
Example

VIEW is nothing but the table which can be derived from one or more tables.In other words, the view can be defined as “ The Table which can be derived from one or more tables based on selection criteria”.

A VIEW is an alternative representation of data which is derived from one or more tables. MAX up to 15 tables can be used to create the view. VIEW can combine the data from different tables.

VIEW can omit the columns from base tables and can only create on with some columns of the base tables. VIEW can contain all the columns or some columns from the table.

CREATE VIEW is used to create the VIEW. VIEW is temporary table in storage point of view and no memory will be allocated.

A VIEW can be created on single table or more than one table. If a VIEW created with all the tables of a single table, then the data can be inserted by referring the view name in INSERT.

Advantages -

Makes data easier to use by representing the data from more than one table’s as a single table. Restrict the access to the base tables and providing the access to the view can increase security to base table data.

Increasing controlled access over the database. Can able to hide the complex queries and makes the end user life easy by representing the data in single table from more than one table. Views can have the derived columns, apart from the retrieved columns.

Syntax -

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

SYSCAT.VIEWS contains the VIEWs complete information. A VIEW is unchanged, if the base table altered when the VIEW is created on full table.

SYSCAT.VIEWS -

Field name	Data type	Description
VIEWSCHEMA	VARCHAR(128)	Along with VIEWNAME, the qualified name of a view or table that is used to define a materialized query table or staging table
VIEWNAME	VARCHAR(128)	Along with VIEWSCHEMA, the qualified name of a view or table that is used to define a materialized query table or staging table
DEFINER	VARCHAR(128)	Authorization ID of the creator of the view
SEQNO	SMALLINT	Always 1
VIEWCHECK	CHAR(1)	The type of view checking N = No check option L = Local check option C = Cascaded check option
READONLY	CHAR(1)	Y = Read-only view because of its definition or N = Not read-only view
VALID	CHAR(1)	Y = Valid view or materialized query table definition validor X = Inoperative view or materialized query table definition; must be re-created
QUALIFIER	VARCHAR(128)	Contains value of the default schema at the time of object definition
FUNC_PATH	VARCHAR(254)	The SQL path of the view creator at the time the view was defined When the view is used in data manipulation statements, this path must be used to resolve function calls in the view.
TEXT	CLOB(64K)	Text of the CREATE VIEW statement

Note! The above table information is collected from Internet.

Example -

DB2 Code -

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