A view is a virtual table defined on one or more tables or
views, or a combination of tables and views.
The term base table refers to the table in which data is
actually stored. Views and query results, although they appear in
the form of a table, are virtual tables derived from one or
more base tables.
ALLBASE/SQL maintains information and statistics on views in the
SYSTEM.TABLE, SYSTEM.SECTION, and SYSTEM.VIEWDEF views in the system
catalog, which is presented fully in the chapter, "System Catalog."
The following are some primary reasons for defining a view:
Restricting access to the base tables.
Keeping data independent of the applications that use it.
In an interactive environment, views are commonly used to
restrict access to base tables.
A large table containing data from several areas
can be broken up into smaller views, one for each area. The
data is available to those who need it, but the entire table
is secure from unauthorized users.
For example, in a personnel
database, a view containing employee names, extensions,
and locations can be derived from a table that also contains
home addresses, salary, and other information to
which access should be restricted. Users can then be
granted access to the views without being able to access
the sensitive data in the base tables.
Refer to chapter 5, "Database Creation and Security,"
for information on
coordinating view definition with security design.
Data independence is usually a concern
in programmatic environments. An application that accesses several
tables would have to be modified each time the tables were altered.
A view defined to look like the old tables would keep applications
independent of database changes.
Refer to the appropriate ALLBASE/SQL Application Programming
Guide for more information on how views affect and are manipulated by
application programs.
View definition is not restricted to base tables.
You can define a view on another view's derived columns.
Refer to the ALLBASE/SQL Reference Manual for a complete list of
restrictions on defining views.
To define a view, use the same guidelines as for defining
a table (refer to "Defining Tables" in this chapter),
plus the following:
ALLBASE/SQL does not limit the number of views you can
define.
A view cannot be created with an ORDER BY clause.
You cannot define an index on a view.
You can define as many as 1023 columns per view.
You can use up to 32 base tables to define a view, which includes
the tables that comprise a view from which another view is defined.
You can manipulate data through views as you would through tables,
but certain restrictions apply. In general, you cannot INSERT,
DELETE, or UPDATE through a view if any of the following are used
to define it:
An aggregate function (AVG, MAX, MIN, SUM, COUNT).
A DISTINCT in the SELECT clause.
Defined fields in the SELECT clause, e.g., ColumnA + ColumnB. In
this case, DELETE is allowed. For more information, refer to "Updatability
of Queries" in the "SQL Queries" chapter of the ALLBASE/SQL Reference Manual.
A view is essentially a stored SELECT statement.
Therefore you cannot alter a view by adding columns to it.
To add a column to a view, simply delete the old view and create
a new view, specifying the additional column in the SELECT clause.
For the most part views are used to restrict data access.
However, views are actually an additional
layer to the base tables. This means that each time a view is used,
ALLBASE/SQL must perform an additional step to build the view before the
user gets the data.
Preprocessing applications also takes longer if the application
contains views because the system catalog must be accessed twice:
once for the view and once for the base tables on which the
view is defined.
Ultimately, the DBA must weigh the factors for and against
creating views depending on the needs of the users.
