DB2 Server for VSE & VM: Application Programming

Using Views

Views allow multiple users to see different presentations of the same data. For example, several users may be operating on a table of data about employees. One may see data about some employees but not others; another may see data about all employees but none of their salaries; and a third may see data about employees joined together with some data from another table. Each of these users is operating on a view that is derived from the real table of data about employees. Each view appears to be a table and has a name of its own.

You can create views with authorization statements to control access to sensitive data. For example, you might create a view based on a GROUP BY query that gives certain users access to the average salary of employees in each department, but prevents them from seeing any individual salaries.

A view is a dynamic "window" on tables. When you update a real table, you can see the updates through a view; when you update a view, the real table underlying the view is updated. There are, however, restrictions on modifying tables through a view.

Because a view is not physically stored, you cannot create an index on it. However, if you create an index on the real table underlying a view, you may improve the performance of queries on the view.

Creating a View

>>-CREATE VIEW--view_name----+----------------------------+-----> | .-,--------------. | | V | | '-(-----column_name---+---)--' >----AS--subselect--+-------------------+---------------------->< '-WITH CHECK OPTION-'

In the following example, a view is created from the EMPLOYEE table:

   CREATE VIEW PHONEBOOK (FNAME, LNAME, NUMBER, DEPART, JOBTITLE) AS
        SELECT FIRSTNME, LASTNAME, PHONENO, WORKDEPT, JOB
        FROM EMPLOYEE WHERE JOB <> 'PRES' WITH CHECK OPTION

The CREATE VIEW statement causes the indicated select-statement to be stored as the definition of a new view, and gives a name to the view and (optionally) to each column in it. If you do not specify the column names, the columns of the view inherit the names of the columns from which they are derived.

You must specify a name for any view column that is not derived directly from a single table column (for example, if a view column is defined as AVG(SALARY) or SALARY+COMMISSION).

Columns derived in this manner are often called virtual columns, (and contain virtual data). You must also specify new column names if the selected columns of the view do not have unique names (for example, if the view is a join of two tables, each of which has a column named PROJNO).

In general, the data types of the columns of the view are inherited from the columns on which they are defined. If a view column is defined on a function, the data type of the view column will be the data type of the function result. (For more details on functions, refer to the DB2 Server for VSE & VM SQL Reference manual.)

If you want to prevent the execution of subsequent inserts or updates to the view that involve data that is outside the domain of the view's definition (as specified in the WHERE clause of its subselect), you can add the WITH CHECK OPTION clause. This clause, however, is not allowed for updateable views that are built on subqueries. The checking that is performed at insert or update time is performed according to a set of rules that cover the situation in which a view is dependent on other views. See the DB2 Server for VSE & VM SQL Reference manual for these rules.

Some other considerations when creating views are:

Internal database manager limitations restrict a view to approximately 140 columns. The number of referenced tables, lengths of column names, and WHERE clauses all further reduce this number.
If the subselect in a view definition has a "SELECT *" clause, the view has as many columns as the underlying table. If columns are later added to the underlying table by ALTER statements, the new columns will not appear in the view (unless you drop and re-create the view).
The name of the view must be unique among all the tables, views, and synonyms that you have already created. You can refer to another user's views, if so authorized, by using the owner-name as a prefix (for example, SMITH.PHONEBOOK).
You can define a view in terms of another view: that is, the subselect that defines a view may refer to one or more other views. In this case, follow the rules listed under Using Views to Manipulate Data.
There is no ORDER BY clause in a subselect; therefore, like a table, a view has no intrinsic order. (Of course, you can specify an ORDER BY clause when you write queries against the view.)
Host variables are not permitted in a CREATE VIEW statement. (For example, predicates such as PRICE = :X are not permitted.)
The owner of the view is considered to be the authorization ID under which the program is preprocessed.
When you define a new view, you receive the same privileges that you have on the underlying table. If you possess these privileges with the GRANT option, you can grant privileges on your view to other users. (See Chapter 10, Assigning Authority and Privileges for information on the GRANT option.) If the view is derived from more than one underlying table, you receive the SELECT privilege, provided that you have this privilege on all the tables from which it is derived. (If you have no privileges on the underlying tables, the CREATE VIEW statement returns an error code.) Only the SELECT privilege is possible, because multi-table views do not permit insertion, deletion, or update.
Primary keys and foreign keys (discussed in Ensuring Data Integrity) cannot be defined on a view.
If you defined your view on a table that has a primary key, and you make changes to that view, the view should contain all the columns of the key.
The subselect is not executed when the view is created, which means that semantic errors (for example, specifying "WHERE COL = '10'" when COL is a decimal column) are not detected until the view is used. To determine whether a statement contains semantic errors, you can enter 'SELECT *' against the view after creating it.

Querying Tables through a View

You can write queries (select-statements) against views exactly as if they were real tables. When you make a query against a view, the query is combined with the definition of the view to produce a new query against real stored tables. This query is then processed in the usual way. For example, the following query might be written against the view PHONEBOOK that was defined under Creating a View:

   SELECT FNAME,LNAME
   FROM PHONEBOOK
   WHERE DEPART = 'D11'
   ORDER BY 2

The system combines the query with the definition of PHONEBOOK, and processes the resulting internal query:

   SELECT FIRSTNME, LASTNAME
   FROM EMPLOYEE
   WHERE JOB <> 'PRES'
   AND WORKDEPT = 'D11'
   ORDER BY 2

During the processing of a query on a view, the system may detect and report errors (by a negative SQLCODE) in either of two phases:

The combination of the query with the view-definition (for example, attempting to add together two strings of character-type)
The execution of the resulting query on real tables (for example, attempting to fetch a null value when no indicator variable is provided).

Note: If a view materialization is required to process the view, this view must not contain any LONG VARCHAR columns in the view definition. For a detailed description of view materialization, refer to the DB2 Server for VSE & VM Database Administration manual.

Using Views to Manipulate Data

Like select-statements, INSERT, DELETE, and UPDATE statements can be applied to a view just as though it were a real stored table. The SQL statement that operates on the view is combined with the definition of the view to form a new SQL statement that operates on a stored table. Any data modification made by such a statement is visible to users of the view, the underlying table, or other views defined on the same table (if the views "overlap" in the modified area).

The following is an example of an update applied to the view PHONEBOOK, showing how the update can be modified to operate on the real table EMPLOYEE:

View Definition for PHONEBOOK:

CREATE VIEW PHONEBOOK (FNAME, LNAME, NUMBER, DEPART, JOBTITLE) AS SELECT FIRSTNME, LASTNAME, PHONENO, WORKDEPT, JOB FROM EMPLOYEE WHERE JOB <> 'PRES' WITH CHECK OPTION

   UPDATE PHONEBOOK
   SET NUMBER = '9111'
   WHERE LNAME = 'SMITH'
   AND FNAME = 'DANIEL'

becomes:

   UPDATE EMPLOYEE
   SET PHONENO = '9111'
   WHERE LASTNAME = 'SMITH'
   AND FIRSTNME = 'DANIEL'
   AND JOB <> 'PRES'

Note: Because of the WITH CHECK OPTION, the following update will not be allowed when Sally takes over as president:
UPDATE PHONEBOOK SET JOBTITLE = 'PRES' WHERE LNAME = 'KWAN' AND FNAME = 'SALLY'

You must observe the following rules when modifying tables through a view:

INSERT, DELETE, and UPDATE of the view are not permitted if the view involves any of the following operations: join, GROUP BY, DISTINCT, or any column function such as AVG.
A column of a view can be updated only if it is derived directly from a column of a single stored table. Columns defined by expressions such as SALARY + BONUS or SALARY * 1.25 cannot be updated. (These columns are sometimes called virtual columns.) If a view is defined containing one or more such columns, the owner does not receive the UPDATE privilege on these columns. INSERT statements are not permitted on views containing such columns, but DELETE statements are.
The ALTER TABLE, CREATE INDEX, and UPDATE STATISTICS statements cannot be applied to a view.

You can use an INSERT statement on a view that does not contain all the columns of the stored table on which it is based. For example, consider the EMPLOYEE table with none of the columns defined as NOT NULL. You could insert rows into the view PHONEBOOK even though it does not contain the MIDINIT, EDLEVEL or any other columns of the underlying table EMPLOYEE.

You can insert or update rows of a view in such a way that they do not satisfy the definition of the view. For example, the view PHONEBOOK is defined by the condition JOB <> 'PRES'. It would be possible to insert rows into PHONEBOOK having a value equal to 'PRES' in the JOB column. This insertion takes effect on the underlying table, EMPLOYEE, but the resulting rows are not visible in the view PHONEBOOK, because they do not satisfy the definition of PHONEBOOK. In fact, an update to PHONEBOOK that sets JOB='PRES' causes a row to "vanish" from PHONEBOOK (a cursor positioned on the row retains its position, but later scans through PHONEBOOK do not see this row). If you want to ensure that all rows inserted or updated are subsequently visible in the view, then define your view with 'WITH CHECK OPTION'.

However, the EMPLOYEE table does have columns defined as NOT NULL, and two of them (MIDINIT and EDLEVEL) are not available through the PHONE view. If you try to insert a row through the view, the system attempts to insert NULL values into all the EMPLOYEE columns that are "invisible" through the view. Because the MIDINIT and the EDLEVEL columns are not included in the view, and do not permit null values, the system does not permit the insertion through the view.

Be extremely careful when updating tables through views that may contain duplicate rows. For example, suppose a view JOBS is defined on the EMPLOYEE table containing only the columns WORKDEPT and JOB. Because EMPNO is not included in the view, and many employees may have the same job description, a user of the view cannot tell which EMPNO corresponds to a given row of the view. If the user positions a cursor on a row where JOB = 'CLERK', and then updates the current row of this cursor, a row of the stored EMPLOYEE table is updated. However, because there may be many clerks in the EMPLOYEE table, and the unique qualifier EMPNO is not part of the view, the user cannot control which employee is updated.

Dropping a View

Format
>>-DROP VIEW--view_name----------------------------------------><

The DROP VIEW statement drops the definition of the indicated view from the database. When you drop a view, the system also:

Drops all other views defined in terms of the indicated view. (The underlying tables on which the views are defined are not affected.)
Deletes all privileges on the dropped views from the authorization catalog tables.
Marks invalid all packages that refer to the dropped views.
The invalid packages remain in the database until they are explicitly dropped by a DROP PACKAGE statement. When an invalid package is next invoked, the system attempts to regenerate it and restore its validity. However, if the program contains any SQL statement that refers to a dbspace, table, or view that has been dropped, that SQL statement returns an error code at run time.

If a DROP VIEW statement attempts to drop a view that is currently in use by another running logical unit of work, the statement is queued until that LUW ends.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]