• views using constants
• views having columns with derived data in the SELECT-list
• views that do not contain all columns defined as NOT NULL from the tables from which they were defined

• The View Usage Rule
• The Proliferation Avoidance Rule
• The View Synchronization Rule

1. to provide row and column level security
2. to ensure efficient access paths
3. to mask complexity from the user
4. to ensure proper data derivation
5. to provide domain support
6. to rename columns, and
7. to provide solutions which can not be accomplished without views

Security
One of the most beneficial purposes served by views is to extend the data security features of DB2. Views can be created that provide a subset of rows, a subset of columns, or a subset of both rows and columns from the base table.
 
How do views help provide row and column level security? Consider an EMPLOYEE table that contains all of the pertinent information regarding an enterprise's employees. Typically, name, address, position, age, and salary information would be contained in such a table. However, not every user will require access to all of this information. Specifically, it may become necessary to shield the salary information from most users. This can be done by creating a view that does not contain the salary column and granting most users the ability to access the view, instead of the base table.
 
Similarly, row level security may be necessary. Consider a table that contains project information. Typically this would include project name, purpose, start date, and who is responsible for the project. Perhaps the security requirements of the projects within your organization deem that only the employee who is responsible for the project can access their project data. By storing the authid of the responsible employee in the PROJECT table, a view can be created using the USER special register such as the one shown below:



CREATE VIEW 	MY_PROJECTS
	(PROJ_NO, PROJ_NAME, DEPT_NO, 
	 PROJ_STAFF, PROJ_START_DATE, 
	 PROJ_END_DATE)
AS	
	SELECT	PROJNO, PROJNAME, DEPTNO,
		PRSTAFF, PRSTDATE, PRENDATE
	FROM	DSN8230.PROJ
	WHERE	RESPEMP = USER


The USER special register will contain the primary authorization ID of the process initiating the request. So, if user TED001 issues a SELECT statement against the MY_PROJECTS view, only rows where RESPEMP is equal to TED001 will be returned. This is a fast and effective way of instituting row level security.
 
By eliminating restricted columns from the SELECT list and providing the proper predicates in the WHERE clause, views can be created to allow access to only those portions of a table that each user is permitted to access.

Efficient Access
Views can also be used to ensure optimal access paths. By coding efficient predicates in the view definition SQL, efficient access to the underlying base tables can be guaranteed. The use of stage 1 predicates, proper join criteria, and predicates on indexed columns can be coded into the view.
 
For example, consider the following view:


CREATE VIEW 	EMP_DEPTS
	(EMP_NO, EMP_FIRST_NAME, EMP_MID_INIT,
	 EMP_LAST_NAME, DEPT_NO, DEPT_NAME)
AS	
	SELECT	E.EMPNO, E.FIRSTNME, E.MIDINIT,
		E.LASTNAME, D.DEPTNO, D.DEPTNAME
	FROM	DSN8230.EMP	E,
		DSN8230.DEPT	D
	WHERE	D.DEPTNO = E.WORKDEPT


By coding the appropriate join criteria into the view definition SQL you can ensure that the correct join predicate will always be utilized.

Complexity
Somewhat akin to coding appropriate access into views, complex SQL can be coded into views to mask the complexity from the user. This can be extremely useful when your shop employs novice DB2 users (whether those users are programmers, analysts, managers, or typical end users).
 
Consider the following rather complex SQL that implements relational division:


SELECT 	DISTINCT PROJNO
FROM	DSN8230.PROJACT		P1
WHERE	NOT EXISTS
		(SELECT	ACTNO
		 FROM	DSN8230.ACT	A
		 WHERE	NOT EXISTS
			(SELECT	PROJNO
			 FROM	DSN8230.PROJACT		P2
			 WHERE	P1.PROJNO = P2.PROJNO
			 AND		A.ACTNO = P2.ACTNO);


This query uses correlated subselects to return a list of all projects in the PROJACT table that require every activity listed in the ACT table. By coding this SQL into a view called, say ALL_ACTIVITY_PROJ, then the end user need only issue the following simple SELECT statement instead of the more complicated query:


SELECT 	PROJNO
FROM	ALL_ACTIVTY_PROJ

Now isn't that a lot simpler?

Derived Data
Another valid usage of views is to ensure consistent derived data by creating new columns for views that are based upon arithmetic formulae. For example, creating a view that contains a column named TOTAL_COMPENSATION which is defined by selecting SALARY + COMMISSION + BONUS is a good example of using derived data in a view.

Domain Support
It is a sad fact of life that most relational database management systems do not support domains, and DB2 is no exception. Domains are an instrumental component of the relational model and, in fact, were in the original relational model published by Ted Codd in 1970-almost 25 years ago! Although the purpose of this article is not to explain the concept of domains, a quick explanation is in order. A domain basically identifies the valid range of values that a column can contain. Of course, domains are more complex than this simple definition can support. For example, the relational model states that only columns pooled from the same domain should be able to be compared within a predicate (unless explicitly overridden).
 
Some of the functionality of domains can be implemented using views and the WITH CHECK OPTION clause. The WITH CHECK OPTION clause ensures the update integrity of DB2 views. This will guarantee that all data inserted or updated using the view will adhere to the view specification. For example, consider the following view:


CREATE VIEW 	EMPLOYEE
	(EMP_NO, EMP_FIRST_NAME, EMP_MID_INIT,
 	 EMP_LAST_NAME, DEPT, JOB, SEX, SALARY)
AS 
	SELECT 	EMPNO, FIRSTNME, MIDINIT, LASTNAME,
	    	WORKDEPT, JOB, SEX, SALARY
	FROM	DSN8230.EMP
	WHERE  	SEX IN ('M', 'F')
WITH CHECK OPTION;


The WITH CHECK OPTION clause, in this case, ensure that all updates made to this view can specify only the values 'M' or 'F' in the SEX column. Although this is a simplistic example, it is easy to extrapolate from this example where your organization can create views with predicates that specify code ranges using BETWEEN, patterns using LIKE, or a subselect against another table to identify the domain of a column.
 
A word of caution however: when inserts or updates are done using these types of views, DB2 will evaluate the predicates to ensure that the data modification conforms to the predicates in the view. Be sure to perform adequate testing prior to implementing domains in this manner to be safeguard against possible performance degradation.

Column Renaming
As you can tell from looking at the sample views shown in the other sections, you can rename columns in views. This is particularly useful if a table contains arcane or complicated column names. A wonderful example of such tables are the DB2 Catalog tables.
 
Consider the following view:


CREATE VIEW	PLAN_DEPENDENCY
	(OBJECT_NAME, OBJECT_CREATOR, OBJECT_TYPE,
	 PLAN_NAME, IBM_REQD)
AS
	SELECT	BNAME, BCREATOR, BTYPE,
		DNAME, IBMREQD
	FROM	SYSIBM.SYSPLANDEP


Not only have we renamed the entity from SYSPLANDEP to the more easily understood name, PLAN_DEPENDENCY, but we have also renamed each of the columns. Isn't it much more easy to understand that PLAN_NAME than DNAME, or OBJECT_CREATOR than BCREATOR? Views can be created on each of the DB2 Catalog tables in this manner so that your programmers will be better able to determine which columns contain the information that they require. Additionally, if other tables exist with clumsy table and/or column names, views can provide an elegant solution to renaming without having to drop and recreate anything!

Single Solution Views
The final view usage situation that may be encountered is probably the most practical usage for views-when views are the only solution! Sometimes, a complex data access request may be encountered that can not be coded using SQL alone. However, sometimes a view can be created to implement a portion of the access. Then, the view can be queried to satisfy the remainder.
 
Consider the scenario where you want to report on detail information and summary information from a single table. For instance, what if you would like to report on column length information from the DB2 Catalog. For each table, provide all column details, and on each row, also report the maximum, minimum, and average column lengths for that table. Additionally, report the difference between the average column length and each individual column length. Try doing that in one SQL statement!
 
Or, you could create a view to solve the dilemma. Consider the COL_LENGTH view based on SYSIBM.SYSCOLUMNS shown below:


CREATE VIEW	 COL_LENGTH
	(TABLE_NAME, MAX_LENGTH, 
	 MIN_LENGTH, AVG_LENGTH)
AS
	SELECT	 TBNAME, MAX(LENGTH), 
		 MIN(LENGTH), AVG(LENGTH)
	FROM	 SYSIBM.SYSCOLUMNS
	GROUP BY TBNAME


After the view is created, the following SELECT statement can be issued joining the view to the base table, thereby providing both detail and aggregate information on each report row:


SELECT 	TBNAME, NAME, COLNO, LENGTH, 
	MAX_LENGTH, MIN_LENGTH, AVG_LENGTH, 
	LENGTH - AVG_COL_LENGTH                                                 
FROM   	SYSIBM.SYSCOLUMNS	C,                                                   
	authid.COL_LENGTH	V
WHERE  	C.TBNAME = V.TABLE_NAME                                                     
ORDER BY 1, 3                                                                  


Situations such as these are a great opportunity for using views to make data access a much simpler proposition.

• Code keywords such as SELECT, WHERE, FROM, and ORDER BY such that they stand off and always begin at the far left of a new line.
• Use parentheses where appropriate to clarify the intent of the SQL statement.
• Use indentation to show the different levels within the WHERE clause.

© 1999 Craig S. Mullins. All rights reserved.
Home.