This chapter describes introduction of views, creating, modifying and dropping the views. and in this tutorial, you will learn about Db2 views and its advantages including simplicity, security, and consistency.
The views which can be used to retrieve the data only. views are not used to perform any Data manipulation statements on it to manipulate the data. non updatable Views also called as Read only Views.
Syntax
CREATE VIEW View-name[Column-names] AS (SELECT *[column-names] FROM database-name.tablespace-name.table-name) ON database-name.tablespace-name READONLY.
A view is an alternative way of representing data that exists in one or more tables. A view can include all or some of the columns from one or more base tables. Suppose you want to get the detailed information of books including title, ISBN, publisher, and published date. To achieve this, you use the following SELECT statement -
SELECT
b.title,
b.isbn,
p.name publisher,
b.published_date
FROM books b
INNER JOIN publishers p
ON p.publisher_id = b.publisher_id;
To reuse this query, you may save it to a text file so that next time you access it. Also, any application that wants to have the same result set needs to have this query embedded in its code. Saving the query and copying it over multiple applications are not ideal. Because it takes time to type the query and may cause inconsistency between applications. Fortunately, Db2 allows you to save this query in the database catalog with a name so that you can reference it later. This named query is called a view. By definition, a view a named query that stored in the database. A view can include some or all columns from one or more base tables.
Creating a view
You can create a view using the following syntax -
Syntax
db2 create view <view_name> (<col_name>, <col_name1...) as select <cols>.. from <table_name>
A view is a named specification of a result table. Conceptually, creating a view is somewhat like using binoculars. You might look through binoculars to see an entire landscape or to look at a specific image within the landscape, such as a tree.
You can create a view that, Combines data from different base tables, Is based on other views or on a combination of views and tables, Omits certain data, thereby shielding some table data from users
To define a view, you use the CREATE VIEW statement and assign a name (up to 128 characters in length) to the view. Specifying the view in other SQL statements is effectively like running an SQL SELECT statement. At any time, the view consists of the rows that would result from the SELECT statement that it contains. You can think of a view as having columns and rows just like the base table on which the view is defined.