Schema Tables for Views
Three schema tables store information on views: _View, _View-Col, and _View-Ref. The view schema tables translate all references into fully qualified base table terms.
The _View schema table contains view definitions, including view name, the names of base tables to which the view refers, base table translations of the WHERE and GROUP BY clauses (if included), and the actual text of the view definition from the CREATE VIEW statement. Four privilege fields contain read, write, create, and delete privileges. The _Updatable field indicates whether the view is updatable.
The _View-Col schema table is the equivalent of the _Field schema table. There is a record for each column in the view definition. Each record contains the view name, column name, equivalent base table column or expression, and two privilege fields.
The _View-Ref schema table is a cross-reference table used to connect views with their underlying tables. A record is created in this table for each base table column to which the view refers. This table provides information on which views are defined in terms of other views, as well as which base tables are used. This schema table prevents you from deleting tables, columns, or views that are referred to in view definitions. For example, you can use this table to look up view dependencies to see all the view definitions that refer to a particular view, base table, or column. When operating in a multi-database environment, only the view relationships within a single database are stored in that database’s _View-Ref table. Dependencies on other databases are not maintained.
Table B–3, Table B–4, and Table B–5 list and describe fields that the CREATE VIEW statement creates. Never modify fields in the view schema tables.
Table B–3: Fields in the _View Schema Table
Field
|
Description
|
_Auth-id
|
The owner (creator) of the view.
|
_View-Name
|
The name of the view.
|
_Base-Tables
|
A list of the base tables referred to directly or indirectly in the FROM clause of the view definition. If the view is defined in terms of other views, these view definitions are resolved to base tables when the CREATE VIEW statement is compiled.
|
_Where-Cls
|
The WHERE clause of the view definition, in terms of base tables. All column references are fully qualified and references to other views are resolved when the CREATE VIEW statement is compiled.
|
_Group-By
|
The GROUP BY clause of the view definition, also in base table terms. If the view definition contains the CHECK OPTION, the CHECK OPTION WHERE clause is stored here.
|
_View-Def
|
The original view definition as entered in the CREATE VIEW statement. This field is included primarily for documentation purposes so you can see the original view definition. Never alter this field. Any quotation marks in the view definition are not included in the _View-Def field. Otherwise, the original text is unchanged, but might be truncated when displayed on the screen.
|
_Can-Read _Can-Write _Can-Create _Can-Delete
|
These fields are initialized as base tables and are modified using the GRANT and REVOKE statements.
|
_Updatable
|
When the view is created, SQL determines whether it is updatable according to the ANSI standard. Set to YES or NO.
|
Table B–4: Fields in the _View-Col Schema Table
Field
|
Description
|
_Auth-id
|
The owner (creator) of the view.
|
_View-Name
|
The name of the view.
|
_Col-Name
|
The name of the column. Can be specified explicitly in the view definition, or, if no column list is included, is the same as the name of the corresponding column in the SELECT list.
|
_Base-Col
|
The fully qualified base table column name or expression to that this view column references. Fully resolved when the CREATE VIEW statement is compiled.
|
_Can-Write
|
Initialized to the owner in the CREATE VIEW statement. You can modify this value using the GRANT and REVOKE statements.
|
_Can-Create
|
Reserved for future use.
|
_Vcol-Order
|
Provides the default, left-to-right display order of the columns. The lowest number represents the leftmost column; the highest number represents the rightmost column.
|
Table B–5: Fields in the _View-Ref Schema Table
Field
|
Description
|
_Auth-id
|
The owner (creator) of the view.
|
_View-Name
|
The name of the view whose definition contains the reference.
|
_Ref-Table
|
The referenced table or view. This field is not resolved to the base table if the immediate reference is to a view.
|
_Base-Col
|
The referenced base table column. This field is resolved to the base table, even if the immediate reference is to a view.
|