Views in DB2
Views in DB2

In simple words a VIEW is “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”.

In general Views are created using SQL, and these are available in any database. For example DB2, ORACLE, MYSQL etc. The typical point is the way writing SQL determines, if a view is updatable or not.

SQL Query to create a view

CREATE VIEW NONFICTIONBOOKS AS SELECT * FROM BOOKS WHERE BOOKTYPE = 'N';

How to make a VIEW is READ ONLY or updatable VIEW

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.

Other Key Rules for Views

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.

How will you determine a VIEW is updatable or READ-ONLY

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.

How to avoid a VIEW is updatable

You can write SQL in the following way:

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.

How to UPDATE Read-only View

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.

Advertisements