{"id":629,"date":"2019-06-05T01:50:40","date_gmt":"2019-06-05T09:50:40","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=629"},"modified":"2020-04-11T06:58:49","modified_gmt":"2020-04-11T14:58:49","slug":"db2-view","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-view\/","title":{"rendered":"Db2 View"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about Db2 views and its advantages including simplicity, security, and consistency.<\/p>\n\n\n\n

Introduction to Db2 views<\/h3>\n\n\n\n

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<\/a><\/code> statement:<\/p>\n\n\n

SELECT<\/span> \n    b.title, \n    b.isbn,\n    p.name publisher,\n    b.published_date\nFROM<\/span> books b\nINNER<\/span> JOIN<\/span> publishers p \n    ON<\/span> p.publisher_id = b.publisher_id;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
The following picture illustrates a view:<\/p>\n\n\n\n
\"db2<\/figure><\/div>\n\n\n\n
To create a view, you use the CREATE VIEW<\/a><\/code> statement. For example, the following statement creates a view namedbook_publisher<\/code>:<\/p>\n\n\n
CREATE<\/span> VIEW<\/span> book_publisher \nAS<\/span> \nSELECT<\/span> \n    b.title, \n    b.isbn,\n    p.name publisher,\n    b.published_date\nFROM<\/span> books b\nINNER<\/span> JOIN<\/span> publishers p \n    ON<\/span> p.publisher_id = b.publisher_id;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The books<\/code> and publishers<\/code> tables are called the base tables.<\/p>\n\n\n\n
Now, you can reference the query via its name book_publisher<\/code> like you query data from a table:<\/p>\n\n\n
SELECT<\/span> \n    title, \n    isbn, \n    publisher, \n    published_date \nFROM<\/span> \n    book_publisher\nORDER<\/span> BY<\/span> \n    title;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
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.<\/p>\n\n\n\n
Db2 view advantages<\/h2>\n\n\n\n
Db2 views provide the following advantages:<\/p>\n\n\n\n
Simplicity<\/h3>\n\n\n\n
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.<\/p>\n\n\n\n
Security<\/h3>\n\n\n\n
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.<\/p>\n\n\n\n
Consistency<\/h3>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Managing views in Db2<\/h2>\n\n\n\n