DB2 - View

First Page
Next Page

DB2 View

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, 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.

In fact, these are common underlying reasons to use a view. Combining information from base tables and views simplifies retrieving data for a user, and limiting the data that a user can see is useful for security. You can use views for a number of different purposes. A view can, Control access to a table, Make data easier to use, Simplify authorization by granting access to a view without granting access to the table, Show only portions of data in the table, Show summary data for a given table, Combine two or more tables in meaningful ways, Show only the selected rows that are pertinent to the process that uses the view.

What is DB2 View?

DB2 Views are the database objects that can be created on a table to improve the performance of DB2 Server. A DB2 view has unique look on data from one or more tables. It can organize data in unique order, focus or hide some data. DB2 Views comprises of a stored query accessible as a fundamental table composed of the outcome set. Beside standard tables a perspective does not shaped a part of the physical schema. It is a virtual table, dynamic in the database. DB2 View is a stored query, and it can be attributed like a table.

Once a view is created from base table, all DML operations can be performed on that view which effects its base table. This kind of view is called simple view. Create any number of columns in a table. The system privileges are required to create own schema, and can execute any object privilege on object type.

The following CREATE VIEW statement defines the EMPINFO view that is shown in the preceding figure.

CREATE VIEW EMPINFO (EMPLOYEE, FIRSTNAME, LASTNAME, TEAM, JOBTITLE)
  AS SELECT EMPNO, FIRSTNME, LASTNAME, DEPT, JOB
  FROM EMP;

When you define a view, DB2 stores the definition of the view in the DB2 catalog. However, DB2 does not store any data for the view itself, because the data exists in the base table or tables.

You can narrow the scope of the EMPINFO view by limiting the content to a subset of rows and columns that includes departments A00 and C01 only.

CREATE VIEW EMPINFO (EMPLOYEE, FIRSTNAME, LASTNAME, TEAM, JOBTITLE)
  AS SELECT EMPNO, FIRSTNME, LASTNAME, DEPT, JOB
  WHERE DEPT = 'AOO' OR DEPT = 'C01'
    FROM EMP;

In general, a view inherits the attributes of the object from which it is derived. Columns that are added to the tables after the view is defined on those tables do not appear in the view.

To retrieve or access information from a view, you use views like you use base tables. You can use a SELECT statement to show the information from the view. The SELECT statement can name other views and tables, and it can use the WHERE, GROUP BY, and HAVING clauses. It cannot use the ORDER BY clause or name a host variable.

Whether a view can be used in an insert, update, or delete operation depends on its definition. For example, if a view includes a foreign key of its base table, INSERT and UPDATE operations that use the view are subject to the same referential constraint as the base table. Likewise, if the base table of a view is a parent table, DELETE operations that use the view are subject to the same rules as DELETE operations on the base table. Read-only views cannot be used for insert, update, and delete operations.

DB2 views

For Example
View Resource name
AA* AccelStats - Table Data
ACC* AccelThreads - Table Data
APPSTOBJ ApptThdObj PA08 - Table Data
AS* ASUTIME - Table Data
AUTH* Authorization Failures CDC0140 - Table Data
BFR* BufferPool - Table Data
DATASET* Data Set Opens CDC00107 - Table Data
DDF* DDF Connections - Table Data
DOBJ* Appl Workload - Table Data
HT* Thread Intervals - Table Data
ST* Status - Table Data
STDLOGS STDLOGS - Table Data
WZ* Status - Table Data
First Page
Next Page