Views, Aliases, and Synonyms

DB2 provides several table-like database objects that can be used to assist in the development of queries and applications. These objects include views, aliases, and synonyms. Each of the objects can be accessed by SQL statements in much the same way as a DB2 table. However, there are significant differences among these objects that you must understand in order to use them effectively.

Views

DB2 enables you to create a virtual table known as a view . A view is a representation of data stored in one or more tables. Recall from Chapter 1 that all operations on a DB2 table result in another table. This is a requirement of the relational model. So a view is defined as a SELECT statement on other tables (or views).

A view is represented internally to DB2 by SQL statements, not by stored data. You therefore can define views using the same SQL statements that access data in base tables. The SQL comprising the view is executed only when the view is accessed. This allows the creation of logical tables that consist of a subset of columns from a base table or tables. When the data in the underlying base tables changes, the changes are reflected in any view that contains a base table. You also can create views based on multiple tables by using joins.

One of the most fertile grounds for disagreement between DB2 professionals is the appropriate use of views. Some analysts promote the liberal creation and use of views, whereas others preach a more conservative approach. Usually, their recommendations are based on notions of reducing a program's dependency on a DB2 object's data structure.

This section delineates the best philosophy for the creation and use of views based on my experience. By following each of the guidelines in this section, you can establish a sound framework for view creation and use in your organization.

The View Usage Rule

Create a view only when a specific, stated, and rational goal can be achieved by the view.

Each view must have a specific and logical use before it is created. (Do not simply create a view for each base table.) Views excel for the following six basic uses:

• To provide row and column level security

• To ensure proper data derivation

• To ensure efficient access paths

• To mask complexity from the user

• To provide limited domain support

• To rename columns

If you're creating a view that does not apply to one of these categories, you should reexamine your view requirements. Chances are, the use is not a good one.

Using Views to Implement Security

Views created to provide security on tables effectively create a logical table that is a subset of rows, columns, or both from the base table. By eliminating restricted columns from the column list and providing the proper predicates in the WHERE clause, you can create views to limit a user's access to portions of a table.

For additional details on using views to implement security consult Chapter 10, "DB2 Security and Authorization."

Using Views for Data Derivation

Data derivation formulas can be coded into the SELECT list of a view, thereby ensuring that everyone is using the same calculation. Creating a view that contains a column named TOTAL_COMP that is defined by selecting SALARY + COMM + BONUS is a good example of derived data in a view. Instead of trying to ensure that all queries requiring total compensation add the three component columns, the queries can use the view containing the TOTAL_COMP column instead and not worry about how it is calculated.

Using Views to Ensure Optimal Access

When you create a view for access, you can guarantee efficient access to the underlying base table by specifying indexed columns and proper join criteria. For efficient access, you can code views so that they specify columns indexed in the WHERE clause. Coding join logic into a view also increases the efficiency of access because the join is always performed properly. To code a proper join, use the WHERE clause to compare the columns from like domains.

Using Views to Mask Complexity

Somewhat akin to coding appropriate access into views, coding complex SQL into views can mask the complexity from the user. Coding this way can be extremely useful when your shop employs novice DB2 users (whether they are programmers, analysts, managers, or typical end users).

Consider the following rather complex SQL that implements relational division:

 SELECT DISTINCT PROJNO FROM   DSN8810.PROJACT P1 WHERE  NOT EXISTS        (SELECT  ACTNO         FROM    DSN8810.ACT A         WHERE   NOT EXISTS                 (SELECT PROJNO                  FROM DSN8810.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. If you code this SQL into a view called ALL_ACTIVITY_PROJ , for example, the end user need only issue the following simple SELECT statement instead of the more complicated query:

 SELECT  PROJNO FROM    ALL_ACTIVTY_PROJ; 

CAUTION

Using views to mask complexity can cause problems when used in DB2 application programs. It can be difficult to support and maintain programs, especially when they fail during the night. Think about it. Would you rather have the entire SQL statement coded in the program ”even if it is complex ”or would you rather have to hunt down the view definition in the DB2 Catalog (where it is stored in SYSIBM.SYSVIEWS without any formatting)?

Using Views to Mimic Domain Support

Most relational database management systems do not support domains , and DB2 is no exception. Domains are instrumental components of the relational model and, in fact, were in the original relational model published by Ted Codd in 1970 ”over three decades ago! A domain basically identifies the valid range of values that a column can contain.

NOTE

Domains are more complex than this simple definition, of course. 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).

Views and table check constraints can be used to create crude domains. In general, table check constraints should be preferred over views for creating domain-like functionality, because check constraints are easier to implement and maintain. However, using views with the WITH CHECK OPTION can provide domain-like functionality combined with other view features (such as securing data by eliminating columns).

You can implement some of the functionality of domains by using views and the WITH CHECK OPTION clause. The WITH CHECK OPTION clause ensures the update integrity of DB2 views. It guarantees that all data inserted or updated using the view adheres 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    DSN8810.EMP   WHERE   SEX IN ('M', 'F') WITH CHECK OPTION; 

The WITH CHECK OPTION clause, in this case, ensures that all updates made to this view can specify only the values 'M' or 'F' in the SEX column. Although this example is simplistic, you can easily 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.

Although you can create similar functionality by using check constraints, views can limit the columns and rows while providing data value checking. Consider the following example:

 CREATE VIEW HIGH_PAID_EMP       (EMP_NO, EMP_FIRST_NAME, EMP_MID_INIT,        EMP_LAST_NAME, DEPT, JOB, SALARY)     AS SELECT  EMPNO, FIRSTNME, MIDINIT, LASTNAME,                WORKDEPT, JOB, SALARY        FROM    DSN8810.EMP        WHERE   SALARY > 75000.00     WITH CASCADED CHECK OPTION; 

This view eliminates several columns (for example, PHONENO , HIREDATE , SEX , and so on) and multiple rows (where SALARY is less than or equal to $75,000). The view is updateable because all the columns not included in the view are nullable. However, only rows in which the salary conforms to the predicate can be modified. This combined functionality cannot be provided by check constraints alone.

Let me add these words of caution, however: When inserts or updates are performed using these types of views, DB2 evaluates 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 safeguard against possible performance degradation.

You can specify the WITH CHECK OPTION clause for updateable views. This way, you can ensure that all data inserted or updated using the view adheres to the view specification. Consider the following view:

 CREATE VIEW HIGH_PAID_EMP  (EMPLOYEE_NO, FIRST_NAME, MIDDLE_INITIAL,   LAST_NAME, DEPARTMENT, JOB, SEX, SALARY) AS SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME,           WORKDEPT, JOB, SEX, SALARY    FROM   DSN8810.EMP    WHERE  SALARY > 75000.00; 

Without the WITH CHECK clause, you can use this view to add data about employees who make less than $75,000. Because this approach is probably not desirable, add WITH CHECK OPTION to the view to ensure that all added data is appropriate given the view definition.

There are two forms of the WITH CHECK OPTION :

• WITH CASCADED CHECK OPTION specifies that all search conditions are checked for the view in which the clause exists and any views it accesses regardless of the check options specified.

• WITH LOCAL CHECK OPTION specifies that search conditions on underlying views are checked conditionally. If a check option exists in underlying views, it is checked; otherwise , it is not.

Views created specifying WITH CHECK OPTION only will provide WITH CASCADED CHECK OPTION functionality. The general rule of thumb, as always, is to explicitly specify the options you want. In other words, never specify WITH CHECK OPTION only; instead, you should specify either WITH CASCADED CHECK OPTION or WITH LOCAL CHECK OPTION .

Using Views to Rename Columns

You can rename columns in views. This capability is particularly useful if a table contains arcane or complicated column names . Sometimes, particularly for application packages purchased from third-party vendors , renaming columns using a view is useful to make the names more user-friendly. Good examples 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 does this view rename the entity from SYSPLANDEP to the more easily understood name PLAN_DEPENDENCY , but it also renames each of the columns. Understanding PLAN_NAME as the name of the plan is easier than understanding DNAME . You can create views on each of the DB2 Catalog tables in this manner so that your programmers can better determine which columns contain the information that they require. Additionally, if you have other tables with clumsy table and/or column names, views can provide an elegant solution to renaming without your having to drop and re-create anything.

You can rename columns in queries by using the AS clause. However, the AS clause does not provide the same function as column renaming using views because you must still specify the original name of the column in the query.

Reasons Not to Create One View Per Base Table

Often, the dubious recommendation is made to create one view for each base table in a DB2 application system. The reason behind such a suggestion usually involves the desire to insulate application programs from database changes. This insulation is purported to be achieved by mandating that all programs access views instead of base tables. Although this idea might sound good on the surface, upon further examination you will see that it is a bad idea.

The following is an example of a base table and the view that would be created for it. Here is the base table:

 CREATE TABLE  userid  .BASE_TABLE  (COLUMN1  CHAR(10)  NOT NULL,   COLUMN2  DATE      NOT NULL WITH DEFAULT,   COLUMN3  SMALLINT,   COLUMN4  VARCHAR(50) ) IN DATABASE db_name; 

Here is the base view:

 CREATE VIEW  userid  .BASE_VIEW  (COL1, COL2, COL3, COL4) AS SELECT COLUMN1, COLUMN2, COLUMN3, COLUMN4    FROM  userid  .BASE_TABLE; 

Because a base table view does not break any of the rules for view updateability, all SQL statements can be executed against it. The basic reasoning behind creating base table views is the erroneous belief that it provides increased data independence.

For every reason that can be given to create one view per base table, a better reason can be given to avoid doing so. This section details all the arguments for creating one view per base table and explains why the reasoning is not sound.

Adding Columns and the Impact on DB2 Programs

The first argument in favor of base table views is typically, "If I add a column to a table, I will not have to change any programs accessing that table." The reasoning behind this assertion is that you can write programs that are independent of the table columns. If a program retrieves data using SELECT * or INSERT s rows, no knowledge of new columns would be required if the column is added correctly.

The SELECT * statement returns all the columns in the table. If a column is added to a table after the program is coded, the program does not execute because the variable needed to store the newly retrieved column is not coded in the program. If the program uses a view, however, the program executes because the view has only the old columns, not including the new column just added.

If the program is coded to update views instead of base tables, the INSERT statement continues to work as well. However, the column added to the base table must allow default values. The default value can be either the null value or the DB2 default when a column is defined as NOT NULL WITH DEFAULT . The INSERT to the view continues to work even though the view does not contain the new column. The row is inserted, and the new column is assigned the appropriate default value.

It is not a good idea to use base table views to insulate programs from the impact of new columns. If you code your application programs properly, you do not have to make changes when a column is added. Proper program coding refers to coding all SQL statements with column names. If column names can be supplied in an SQL statement, the columns should always be explicitly specified in the SQL statement. This rule applies in particular to the INSERT and SELECT statements and is true whether you are using views or base tables.

The SELECT * statement should never be permitted in an application program. Every DB2 manual and text issues this warning ”and with good reason. All DB2 objects can be dropped and re-created and/or altered . If a DB2 object upon which a program relies is modified, a SELECT * in that program ceases to function.

This caveat does not change because you're using views. Even views can be dropped and re-created. If the program uses SELECT * on a view and the view has changed, the program does not work until it is modified to reflect the changes made to the view.

Do not think that you will never modify a view. Some companies establish a policy of keeping views inline with their base tables. Doing so causes the view to change when the table changes. Others use views for security. As security changes, so do the views.

If you eliminate the SELECT * statement, you eliminate this reason for using views. An INSERT statement works against a base table the same as a base table view if the column names are provided in the INSERT statement. As long as you add the new column allowing a default value, the program continues to work.

Removing Columns and the Impact on DB2 Programs

When you remove a column from a DB2 table, you must drop and re-create the table without the column. You can re-create views that access the table being modified, substituting a constant value in place of the removed column. Application programs that access the views then return the constant rather than the column that was dropped.

It is not a good idea to use base table views to insulate programs from the impact of removing columns from a table. The thinking that, if you remove a column from a table, you do not have to change the application program is untrue. If you remove the column from the base table, you must remove it from the view. If you do not remove it from the view and instead add a constant, the view can no longer be updated. Also, all queries and reports return a constant instead of the old column value, and the integrity of the system is jeopardized.

Users must be able to rely on the data in the database. If constants are returned on screens and reports, confusion will arise. Also, if the data (that is now a constant) is used in any calculations, these values are also unreliable. These unreliable calculation results could be generated and then inserted into the database, propagating bad data.

The removal of data from a database must be analyzed in the same manner as any change. Simply returning constants is not a solution and will cause more problems than it solves .

Splitting Tables and the Impact on DB2 Programs

Another popular argument in favor of using base table views centers on anticipating the need to split a DB2 table into two tables. The argument is that if you split a table into two tables, you can change the base table view and thereby avoid changing any program accessing the table. Sometimes one DB2 table must be split into two tables. This is usually done based on access requirements to increase the efficiency of retrieval. For example, consider a table with 10 columns. Fifty percent of the queries against the table access the first 6 columns. The remaining 50% of the queries access the other 4 columns and the key column. This table could be a candidate for splitting into two tables to improve access: one new table containing the first 6 columns and the second new table containing the remaining 4 columns and the key column.

If the programs use a view, you can recode the view to be a join of the two new tables. You do not have to change the programs to reflect the modification; only the view changes.

It is not a good idea to use base table views to insulate programs from the impact of splitting tables. If you must split a table into two tables, you must have a very good reason for doing so. As I indicated, this action is usually driven by performance considerations. To increase efficiency, you must change the underlying SQL to take advantage of the tables that have been split. Queries accessing columns in only one of the new tables must be modified to access only that table.

Using the logic given by the view supporters, no changes are made to programs. If no changes are made, performance suffers because of the view changes, though. The views are now joins instead of straight SELECT s. No SQL code changes. Every straight SELECT now creates a join, which is less efficient than a straight SELECT .

A change of this magnitude requires a thorough analysis of your application code. When table column definitions change, SQL changes and programs change; these changes cannot be avoided. A trained analyst or DBA must analyze the application's SQL, including SQL in application plans, QMF queries, and dynamic SQL. Queries that access columns from both of the new tables must be made into a join. You do not want to create indiscriminate joins, however. Queries that access columns from only one of the two tables must be recoded as a straight SELECT against that table to increase performance. Also, any programs that update the view must be changed. Remember, views that join tables cannot be updated.

If, after investigating, you determine that some queries require joining the two new tables, you can create a view to accommodate these queries. The view can even have the same name as the old table so that you can minimize program changes. The two new tables can be given new names. The view is created only when it is needed ”a more reasonable approach to change management.

A change of this magnitude is rarely attempted after an application has been moved to production. This fact is usually not considered when the recommendation is made to use views.

Combining Tables and the Impact on DB2 Programs

Base table view proponents also advocate using views to insulate programs from the effects of combining two tables into a single table. This situation is the inverse of the preceding situation. If two tables are almost always joined, you can increase efficiency by creating a "pre-joined" table. The overhead incurred by joining the two tables is avoided. Instead of a join, a straight SELECT can now be issued against the new table.

If the application programs use views in this instance, you can modify the views to subsets of the new combination table. In this way, you can avoid program changes.

Once again, base table views do not provide the level of insulation desired. The two tables are combined because most queries must access both of the tables. If you simply combine the two tables into one table and change the views to subsets of the new pre-joined table without changing the SQL, you degrade performance. The queries that were joins are still joins, but now they join the new views. Remember that the views are just subsets of one table now, so these queries join this one table to itself. This approach is usually less efficient than joining the two tables as they were previously defined.

Again, you must perform a great deal of analysis for a change of this magnitude. You must investigate all application SQL. If you determine that some queries access only one of the two old tables, you can define views with the same name as the old tables. You can give the new pre-joined table a new name. This way, you can minimize program modification.

Additional Base Table View Reasoning

One final reason some DBAs have for creating base table views is that some folks believe base table views give them a "feeling" of safety over using just the base tables. I can think of no valid reasoning to support this "feeling." Base table views do not provide a layer of protection between the application and the data. If one view is created for each base table, all types of SQL can be performed on the views. You can perform update and retrieval SQL in the same manner on the views as you can on the base tables.

The advice to create one view per base table is rooted in the fallacious assertion that applications can be ignorant of underlying changes to the database. Change impact analysis must be performed when tables are modified. Failure to do so results in a poorly performing application.

The bottom line is that you should avoid indiscriminate view creation.

Miscellaneous View Guidelines

To ensure appropriate view usage, implement the following tips, techniques, and guidelines.

Follow the Synchronization Rule

Keep all views logically pure by synchronizing them with their underlying base tables.

When you make a change to a base table, you should analyze all views dependent on the base table to determine whether the change affects them. The view was created for a reason (see "The View Usage Rule" section earlier in this chapter) and should remain useful for that reason. You can accomplish this goal only by ensuring that subsequent changes pertinent to a specified use are made to all views that satisfy that use.

Consider a view that is based on the sample tables DSN8810.EMP and DSN8810.DEPT . The view is created to satisfy an access use; it provides information about departments, including the name of the department's manager. If you add a column specifying the employee's middle initial to the EMP table, you should add the column also to the EMP_DEPT view because it is pertinent to that view's use: to provide information about each department and each department's manager. You must drop and re-create the view.

The synchronization rule requires you to have strict procedures for change impact analysis. Every change to a base table should trigger the use of these procedures. You can create simple SQL queries to assist in the change impact analysis. These queries should pinpoint QMF queries, application plans, and dynamic SQL users that could be affected by specific changes. The following queries should assist your change impact analysis process.

To find all views dependent on the table to be changed, use the following:

 SELECT  DCREATOR, DNAME FROM    SYSIBM.SYSVIEWDEP WHERE   BCREATOR = 'Table Creator' AND     BNAME = 'Table Name'; 

To find all QMF queries that access the view, use the following:

 SELECT  DISTINCT OWNER, NAME, TYPE FROM    Q.OBJECT_DATA WHERE   APPLDATA LIKE '%View Name%'; 

To find all plans dependent on the view, use the following:

 SELECT  DNAME FROM    SYSIBM.SYSPLANDEP WHERE   BCREATOR = 'View Creator' AND     BNAME = 'View Name'; 

To find all potential dynamic SQL users, use the following:

 SELECT  GRANTEE FROM    SYSIBM.SYSTABAUTH WHERE   TCREATOR = 'View Creator' AND     TTNAME = 'View Name'; 

Always execute these queries to determine what views might be affected by changes to base tables.

Be Aware of Non-Updateable Views

If you adhere to the preceding guidelines, most of your views will not be updateable. Views that join tables, use functions, use DISTINCT , or use GROUP BY and HAVING cannot be updated, deleted from, or inserted to. Views that contain derived data using arithmetic expressions, contain constants, or eliminate columns without default values cannot be inserted to. Keep this information in mind when you're creating and using views.

Specify Column Names

When you're creating views, DB2 provides the option of specifying new column names for the view or defaulting to the same column names as the underlying base table or tables. Explicitly specify view column names rather than allow them to default, even when you plan to use the same names as the underlying base tables. This approach provides more accurate documentation and minimizes confusion when using views.

Be Aware of View Restrictions

Almost any SQL that can be issued natively can be coded into a view, except SQL that contains the FOR UPDATE OF clause, an ORDER BY specification, or the UNION operation.

Views can be accessed by SQL in the same way that tables are accessed by SQL. However, you must consider the rules about the types of views that can be updated. Table 5.11 lists the restrictions on view updating.

Table 5.11. Non-Updateable View Types

View Type	Restriction
Views that join tables	Cannot delete, update, or insert
Views that use functions	Cannot delete, update, or insert
Views that use DISTINCT	Cannot delete, update, or insert
Views that use GROUP BY and HAVING	Cannot delete, update, or insert
Views that contain derived data using arithmetic expression	Cannot insert
Views that contain constants	Cannot insert
Views that eliminate columns without a default value	Cannot insert

Consider Materialized Query Tables

A view is not materialized until it is accessed in a SQL statement. Depending on the context of the SQL, materialization can cause performance problems.

As of DB2 V8 it is possible to create materialized query tables that are essentially materialized views. For some types of complex queries, MQTs can significantly outperform views. Of course, this comes at a cost. An MQT requires disk storage that a view does not need.

Details on how DB2 optimizes view access are provided in Chapter 21, "The Optimizer." In-depth coverage of materialized query tables is provided in Chapter 45, "Data Warehousing with DB2," because MQTs are most frequently used in data warehouse and analytical implementations .

Aliases

A DB2 ALIAS is an alternate name defined for a table. It was introduced to simplify distributed processing, but aliases can be used in any context, not just for easing data distribution. Remote tables add a location prefix to the table name. However, you can create an ALIAS for a remote table, thereby giving it a shorter, local name because it no longer requires the location prefix.

Synonyms

A DB2 SYNONYM is also an alternate name for a table. Aliases can be accessed by users other than their creator, but synonyms can be accessed only by their creator. When a table is dropped, its synonyms are dropped but its aliases are retained.

As a general rule of thumb, consider using synonyms for individuals during program development, aliases for distributed applications, and views for security, performance, and ease of use.