VIEW

VIEW

What is a View?

A view is a virtual table that represents a stored query. Views are powerful tools for organizing and presenting data without duplicating the underlying database records.

A view in DB2 is a virtual table that is defined by a SELECT query on one or more base tables. This query is stored in the database as a view, and when the view is accessed, DB2 executes the underlying query and presents the results as a table. Views do not store data themselves, which makes them lightweight, and they can be used to restrict data access, simplify query logic, or present data in a specific format.

All views information is present in SYSIBM.SYSVIEWS system table.

Purpose or Advantages:

  • Data Security: Limit access to specific columns or rows, providing a security layer.
  • Simplified Queries: Provide a simplified representation of complex joins or filters for easier querying.
  • Logical Data Organization: Present data in a way that is meaningful to users without changing the actual database structure.
  • Data Consistency: Ensure consistency by centralizing complex queries that users or applications might need.

Types of Views:

  • Standard View: A basic virtual table created from a query on one or more base tables. It does not store data but retrieves it from the underlying tables when accessed.
  • Readonly View: The views are used to retrieve the data and are not used to perform any data manipulation statements (insert, delete, update) on it to manipulate the data. Non-updatable views are also called read-only views.
  • Updatable View: The views are used to retrieve the data and perform data manipulation statements (insert, delete, update) to manipulate it. In this case, the backend tables get updated if we update the view. Updatable views are also called read-only Views.
  • Materialized Query Table (MQT): A specialized view that physically stores the result of a query for improved performance. Unlike standard views, MQTs are updated periodically and are useful for complex, resource-intensive queries.

Creating a View

To create a view in DB2, use the CREATE VIEW statement. We define a view by specifying its name, columns, and the SELECT query that represents the data.

Syntax -

CREATE VIEW view_name (column_alias1, column_alias2, ...)
AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
  • view_name: The name of the view.
  • column_alias: Aliases for the columns in the view, which allow you to specify custom names for columns.
  • SELECT statement: The query that defines the view, specifying the tables and columns to include and any conditions to apply.

Examples - Creating a View

Suppose we want to create a view to show employee details without revealing their salary.

CREATE VIEW EMPLOYEE_VIEW (EMP_ID, EMP_NAME, DEPARTMENT)
AS
SELECT EMP_ID, EMP_NAME, DEPARTMENT
FROM EMPLOYEE
WHERE DEPARTMENT = 'SALES';

Altering a View

DB2 does not directly support ALTER VIEW. If we need to change a view, we will first have to drop the existing view and then recreate it with the updated definition.

Deleting a View

To delete a view, use the DROP VIEW statement. This action removes the view definition from the database, but it does not affect the underlying data in the base tables.

Syntax -

DROP VIEW view_name;

Examples - Deleting a View

DROP VIEW EMPLOYEE_VIEW;