DB2 - Views

Db2 - Using Views

This chapter describes views, which are powerful tools for simplifying data access, enhancing security, and creating a stable API for your database.

What is a View?

A view is a ‘virtual table’ based on the result-set of a stored SELECT query. It contains no data of its own; it’s a named specification that dynamically retrieves data from one or more underlying tables when you query it.

Key Benefits of Using Views

• Simplicity: A view can hide the complexity of multi-table joins and complex calculations, presenting the data as a simple, single table to the end user or application.
• Security: You can create a view that exposes only specific columns and rows from a table, hiding sensitive data from certain users.
• Logical Data Independence: You can change the structure of the underlying tables (e.g., split a table into two) but maintain the original structure through a view, preventing the need to rewrite application code.

Creating and Managing Views

Creating a View

Use the CREATE VIEW statement. You can create simple views on a single table or complex views that join multiple tables.

-- Example 1: A simple view for security
-- Hides the salary column from the EMPLOYEES table
CREATE VIEW PUBLIC_EMPLOYEE_INFO AS
SELECT EMP_ID, FIRST_NAME, LAST_NAME, EMAIL, DEPARTMENT
FROM EMPLOYEES;

-- Example 2: A complex view to simplify data access
-- Joins orders and customers to show order details with customer names
CREATE VIEW CUSTOMER_ORDERS AS
SELECT
    O.ORDER_ID,
    O.ORDER_DATE,
    C.CUSTOMER_ID,
    C.FIRST_NAME || ' ' || C.LAST_NAME AS CUSTOMER_NAME,
    O.TOTAL_AMOUNT
FROM ORDERS O
JOIN CUSTOMERS C ON O.CUSTOMER_ID = C.CUSTOMER_ID;

Modifying a View

You cannot use ALTER VIEW to change the underlying SELECT statement. The standard and recommended practice is to use CREATE OR REPLACE VIEW, which will either create the view if it doesn’t exist or replace it if it does.

-- Add the shipping address to the CUSTOMER_ORDERS view
CREATE OR REPLACE VIEW CUSTOMER_ORDERS AS
SELECT
    O.ORDER_ID,
    O.ORDER_DATE,
    C.CUSTOMER_ID,
    C.FIRST_NAME || ' ' || C.LAST_NAME AS CUSTOMER_NAME,
    C.SHIPPING_ADDRESS, -- New column added
    O.TOTAL_AMOUNT
FROM ORDERS O
JOIN CUSTOMERS C ON O.CUSTOMER_ID = C.CUSTOMER_ID;

Dropping a View

To remove a view, use the DROP VIEW statement. This does not affect the data in the underlying tables.

DROP VIEW PUBLIC_EMPLOYEE_INFO;

Updatable vs. Read-Only Views

Some views can be used in INSERT, UPDATE, or DELETE statements, while others are strictly read-only.

A view is generally updatable if it’s based on a single table and doesn’t contain operations that would make a target row ambiguous. A view becomes read-only if its definition includes:

• A JOIN operation
• Aggregate functions (SUM, COUNT, etc.)
• GROUP BY or HAVING clauses
• DISTINCT keyword
• Set operators (UNION, EXCEPT, etc.)

Best Practice: For clarity, it’s often better to treat all but the simplest views as read-only and perform data modifications directly on the base tables or through stored procedures.

Performance and Alternatives

A view is just a stored query; it does not inherently improve performance. The performance of a query against a view is determined by the performance of the underlying query. If a complex view is queried frequently, its performance can become a bottleneck.

For performance-critical scenarios, consider a Materialized Query Table (MQT). An MQT physically stores the result set of the query, acting like a cached or summary table. It provides much faster read access than a view, but at the cost of disk space and the overhead of keeping the MQT’s data synchronized with the base tables.