GUIDELINE
Review Date
08/2002
1.0 SUBJECT: DB2 Views
2.0 DISTRIBUTION: All Current Users
3.0 FROM: Bruce Roberts, Director of DISC
4.0 PURPOSE: To provide direction in the use of DB2 views.
5.0 BACKGROUND: A view is an alternative way of describing data that exists in one or more tables. It does not contain data but, instead, is a stored set of SQL commands that define a sub-set of rows and columns in the base table.
5.1 Views can provide customized tables for specific users.
5.2 Views can be utilized to limit access to certain kinds of data.
5.3 Views allow complicated queries to be stored for general use.
5.4 A view appears to be a simple table. However, if a view spans two or more tables, a large amount of I/O may be required to service the view, which could be harmful to performance.
6.0 GUIDELINE: Views should be created only when they achieve specific goals. Security, access, and data derivation are three ways of using views. Views should be created for one of the above reasons and should remain useful for that specific reason.
6.1 Views created for security purposes allow access to only those portions of a table that each user is allowed to access.
6.2 Views created for access reasons will provide efficient access to the underlying base table by specifying indexed columns and proper join criteria. For example, a proper join is done by coding the WHERE clause to compare the primary key of one table to the foreign key of another.
6.3 Views created for data derivation objectives supply a column which is not in the base table. The value in this derived column is calculated by formulas in the view. The formulas access one or more columns in the base table.
6.4 Views should not be needlessly created. Every DB2 object that is created constitutes an additional entry in the DB2 catalog. The larger the DB2 catalog tables become, the less efficient the entire DB2 system will be.
6.5 Views should be kept synchronized with their underlying base tables. When a base table is changed, all views that are dependent on it should be analyzed to determine if the change will affect them.
7.0 HISTORY: This Guideline was originally issued as 3612.00 dated 02/10/1992. Re-issued 3612.01, 8/15/2002
8.0 CONTACT: Deputy Director, Bureau of Information Systems, 785-296-3343