Db2 View

Summary: in this tutorial, you will learn about Db2 views and its advantages including simplicity, security, and consistency.

Introduction to Db2 views

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;
Code language: SQL (Structured Query Language) (sql)

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.

The following picture illustrates a view:

To create a view, you use the CREATE VIEW statement. For example, the following statement creates a view namedbook_publisher:

CREATE VIEW book_publisher 
AS 
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;
Code language: SQL (Structured Query Language) (sql)

The books and publishers tables are called the base tables.

Now, you can reference the query via its name book_publisher like you query data from a table:

SELECT 
    title, 
    isbn, 
    publisher, 
    published_date 
FROM 
    book_publisher
ORDER BY 
    title;
Code language: SQL (Structured Query Language) (sql)

Note that a view does not store any data physically. When you query data from a view, Db2 goes to the base tables, retrieves the data, and returns the result set.

Db2 view advantages

Db2 views provide the following advantages:

Simplicity

Instead of writing a query with many joins and conditions over and over again, you can write the query once and save it as a view. Then, you can simply access the data via the view with a much simpler query.

Security

Through the view, you can expose a portion of data of base tables and grant access to users to access the view instead of accessing all data in the base tables.

Consistency

Sometimes, you may want to include formula and logic in queries. However, this formula or logic must document somewhere so that they can be used later.

The better way of doing this is to wrap these queries with formula and logic in the form of views. Whenever you need to use the logic or formula, you just need to use the views instead.

Managing views in Db2

• CREATE VIEW – learn how to use the CREATE VIEW statement to create views based on data in columns of one or more tables.
• DROP VIEW – describe how to use the DROP VIEW statement to delete views from the database.