In this topic, we described about the below sections -
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.
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.
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.
|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|
|VIEWCHECK||CHAR(1)||The type of view checking|
|READONLY||CHAR(1)||Y = Read-only view because of its definition |
N = Not read-only view
|VALID||CHAR(1)||Y = Valid view or materialized query table definition valid|
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|
CREATE VIEW USER_DETAILS_V AS (SELECT * FROM USER_DETAILS ) ON MTH1DB.MFTHTS