Views allow different users or applications to look at the same data in
different ways. This not only makes the data simpler to access, but it
can also be used to restrict which rows and columns users view or
SELECT statement, when it is run,
produces a table as a result. A view essentially is a name for a
stored query that you can use much like a table.
For example, suppose that a company has a table containing information about its employees. A manager needs to see address, telephone number, and salary information about his employees only, while a directory application needs to see all employees in the company along with their address and telephone numbers, but not their salaries. You can create one view that shows all the information for the employees in a specific department and another that shows only the name, address, and telephone number of all employees.
To the user, a view just looks like a table. Except for the view definition, a view does not take up space or store its own data; the data presented in a view is derived from other tables. You can create a view on existing tables, on other views, or some combination of the two. A view defined on another view is called a nested view.
You can define a view with column names that are different than the corresponding column names of the base table. You can also define views that check to see if data inserted or updated stays within the conditions of the view.
The list of views defined is stored in the VIEWS system catalog table. (Note: z/OS the system catalog tables begin with SYSIBM.SYS) The system catalog VIEWDEP table has some number of rows for each view, naming each view or table that view is built upon. Also, each view has an entry in the TABLES system catalog table, and entries in COLUMNS (since views are generally used like tables).
CREATE VIEW SQL statement is used to
define a view. A
SELECT statement is used
to specify which rows and columns are presented in the view.
For example, imagine that you want to create a view that shows only the nonfiction books in our BOOKS table, as shown in Listing 28.
Listing 28. NONFICTIONBOOKS view
CREATE VIEW NONFICTIONBOOKS AS SELECT * FROM BOOKS WHERE BOOKTYPE = 'N';
After you define this view, there are entries for it in system catalog tables, but there is not any separate storage for the data, as the data within a view is derived from other tables or views.
To define column names in the view that are different from those in the
base table, you can specify them in the
CREATE VIEW statement. The statement shown
in Listing 29 creates a MYBOOKVIEW view that contains two columns:
TITLE, which represents the BOOKNAME column, and TYPE, which
represents the BOOKTYPE column.
Listing 29. MYBOOKVIEW with two columns
CREATE VIEW MYBOOKVIEW (TITLE,TYPE) AS SELECT BOOKNAME,BOOKTYPE FROM BOOKS;
DROP VIEW SQL statement is used to
remove a view. Note: For Linux, UNIX, and Windows, if a table or
another view on which a view is based is dropped, the dependent view
remains defined in the system catalog but becomes inoperative. The
VALID column of the VIEWS catalog table indicates whether a view is
valid (Y), or not (X). On z/OS, dependent views are dropped when the
underlying table or view is dropped.
You can drop the NONFICTIONBOOKS view via:
DROP VIEW NONFICTIONBOOKS;
You cannot modify a view. To change a view definition, drop it and then
recreate it. Use the
ALTER VIEW statement
provided only to modify reference types.
When you create a view, due to its structure it may be either a
read-only view or an updatable view. The
SELECT statement of a view determines
whether the view is read-only or updatable. Generally, if the rows of
a view can be mapped to rows of the base table, then the view is
updatable. For example, the view NONFICTIONBOOKS, as you defined it in
the previous example, is updatable because each row in the view is a
row in the base table.
The rules for whether a view is updatable are complex and depend on the
SELECT statement in the definition. For
example, views that use VALUES, DISTINCT, or any form of join are not
directly updatable. You can easily determine whether a view is
updatable by looking at the READONLY column of the VIEWS system
catalog table: Y means it is read-only and N means it is not.
The detailed rules for creating updatable views are documented in the DB2 SQL Reference (see the Resources section).
There is a mechanism to allow the appearance of updating data through a read-only view: INSTEAD OF triggers. These triggers can be defined on a view to intercept UPDATE, INSERT, and DELETE against a view, and instead perform actions against other tables, most commonly the base tables the view is built upon.
An updatable view which selects a subset of data from a table may be able to insert data which is not to be included in that subset. For example, the NONFICTIONBOOKS view defined previously includes only the rows where the BOOKTYPE is 'N'. If you insert into the view a row where the BOOKTYPE is 'F', DB2 inserts the row into the base table BOOKS. However, if you then select from the view, the newly inserted row cannot be seen through the view. If you do not want to allow a user to insert rows that are outside the scope of the view, you can define the view with the check option. Defining a view using WITH CHECK OPTION tells DB2 to check that statements using the view result in data that satisfies the conditions of the view, as shown in Listing 30.
Listing 30. View using WITH CHECK OPTION
CREATE VIEW NONFICTIONBOOKS AS SELECT * FROM BOOKS WHERE BOOKTYPE = 'N' WITH CHECK OPTION;
This view still restricts the user to seeing only non-fiction books. In addition, it prevents the user from inserting rows that do not have a value of N in the BOOKTYPE column and updating the value of the BOOKTYPE column in existing rows to a value other than N. The statements shown in Listing 31, for instance, are no longer allowed.
Listing 31. Statements not allowed
INSERT INTO NONFICTIONBOOKS VALUES (...,'F'); UPDATE NONFICTIONBOOKS SET BOOKTYPE = 'F' WHERE BOOKID = 111;
A view built on top of another view is a nested view. When defining nested views, the check option can be used to restrict operations. However, there are other clauses you can specify to define how the restrictions are inherited. The check option can be defined either as CASCADED or LOCAL.
CASCADED is the default if the keyword is not specified. Several possible scenarios explain the differences between the behavior of CASCADED and LOCAL.
When a view is created WITH CASCADED CHECK OPTION, all statements executed against the view must satisfy the conditions of the view and all underlying views even if those views were not defined with the check option. Suppose that the view NONFICTIONBOOKS is created without the check option, and you also create a view NONFICTIONBOOKS1 based on the view NONFICTIONBOOKS using the CASCADED keyword, as shown in Listing 32.
Listing 32. View created without check option
CREATE VIEW NONFICTIONBOOKS AS SELECT * FROM BOOKS WHERE BOOKTYPE = 'N'; CREATE VIEW NONFICTIONBOOKS1 AS SELECT * FROM NONFICTIONBOOKS WHERE BOOKID > 100 WITH CASCADED CHECK OPTION;
INSERT statements shown in
Listing 33 would not be allowed because they do not satisfy the
conditions of at least one of the views.
Listing 33. INSERT statements not allowed
INSERT INTO NONFICTIONBOOKS1 VALUES( 10,..,'N'); INSERT INTO NONFICTIONBOOKS1 VALUES(120,..,'F'); INSERT INTO NONFICTIONBOOKS1 VALUES( 10,..,'F');
However, the following
would be allowed because it satisfies the conditions of both of the
INSERT INTO NONFICTIONBOOKS1 VALUES(120,...,'N');
Next, suppose you create a view NONFICTIONBOOKS2 based on the view NONFICTIONBOOKS using WITH LOCAL CHECK OPTION. Now, statements executed against the view need only satisfy conditions of views that have the check option specified, as shown in Listing 34.
Listing 34. Satisfying conditions with check option specified
CREATE VIEW NONFICTIONBOOKS AS SELECT * FROM BOOKS WHERE BOOKTYPE = 'N'; CREATE VIEW NONFICTIONBOOKS2 AS SELECT * FROM NONFICTIONBOOKS WHERE BOOKID > 100 WITH LOCAL CHECK OPTION;
In this case, the following
statements shown in Listing 35 would not be allowed because they do
not satisfy the BOOKID > 100 condition of the NONFICTIONBOOKS2
Listing 35. INSERT statements not allowed
INSERT INTO NONFICTIONBOOKS2 VALUES(10,..,'N'); INSERT INTO NONFICTIONBOOKS2 VALUES(10,..,'F');
However, the following
shown in Listing 36 would be allowed even though the value N does not
satisfy the BOOKTYPE = 'N' condition of the NONFICTIONBOOKS view.
Listing 36. INSERT statements allowed
INSERT INTO NONFICTIONBOOKS2 VALUES(120,..,'N'); INSERT INTO NONFICTIONBOOKS2 VALUES(120,..,'F');