View implementation

Views are implemented by the query optimizer using one of two methods:

• The optimizer combines the query select statement with the select statement of the view (view composite)
• The optimizer places the results of the view in a temporary table and then replaces the view reference in the query with the temporary table (view materialization)

This also applies to nested table expressions and common table expressions except where noted.

View composite implementation

The view composite implementation takes the query select statement and combines it with the select statement of the view to generate a new query. The new, combined select statement query is then run directly against the underlying base tables.

This single, composite statement is the preferred implementation for queries containing views, since it requires only a single pass of the data.

Examples:

CREATE VIEW  D21EMPL AS 	
  SELECT * FROM CORPDATA.EMPLOYEE
  WHERE WORKDEPT='D21'
 

Using SQL:

  SELECT LASTNAME, FIRSTNME, SALARY
  FROM D21EMPL
  WHERE JOB='CLERK'  

Using OPNQRYF:

OPNQRYF FILE(D21EMPL)
  FORMAT(FORMAT1)
  QRYSLT('JOB *EQ ''CLERK''')  

The query optimizer will generate a new query that looks like the following example:

SELECT LASTNAME, FIRSTNME, SALARY 	
  FROM CORPDATA.EMPLOYEE
  WHERE WORKDEPT='D21' AND JOB='CLERK'  

The query contains the columns selected by the user's query, the base tables referenced in the query, and the selection from both the view and the user's query.

Note:

The new composite query that the query optimizer generates is not visible to users. Only the original query against the view will be seen by users and database performance tools.

View materialization implementation

The view materialization implementation runs the query of the view and places the results in a temporary result table. The view reference in the user's query is then replaced with the temporary table, and the query is run against the temporary result table.

View materialization is done whenever it is not possible to create a view composite. The following types of queries require view materialization:

• The outermost select of the view contains grouping, the query contains grouping, and refers to a column derived from a column function in the view in the HAVING or select-list.
• The query is a join and the outermost select of the view contains grouping or DISTINCT.
• The outermost select of the view contains DISTINCT, and the query has UNION, grouping, or DISTINCT and one of the following:
  • Only the query has a shared weight NLSS table
  • Only the view has a shared weight NLSS table
  • Both the query and the view have a shared weight NLSS table, but the tables are different.
• The query contains a column function and the outermost select of the view contains a DISTINCT
• The view does not contain an access plan. This can occur when a view references a view and a view composite cannot be created because of one of the reasons listed above. This does not apply to nested table expressions and common table expressions.

Since a temporary result table is created, access methods that are allowed only with ALWCPYDTA(*OPTIMIZE) may be used to implement the query. These methods include hash grouping, hash join, and bitmaps.

Examples:

CREATE VIEW AVGSALVW AS
  SELECT WORKDEPT, AVG(SALARY) AS AVGSAL
  FROM CORPDATA.EMPLOYEE
  GROUP BY WORKDEPT
 

SQL example:

  SELECT D.DEPTNAME, A.AVGSAL
  FROM CORPDATA.DEPARTMENT D, AVGSALVW A
  WHERE D.DEPTNO=A.WORKDEPT

OPNQRYF example:

OPNQRYF FILE(CORPDATA/DEPARTMENT AVSALVW)
  FORMAT(FORMAT1)
  JFLD((1/DEPTNO 2/WORKDEPT *EQ))  

In this case, a view composite cannot be created since a join query references a grouping view. The results of AVGSALVW are placed in a temporary result table (*QUERY0001). The view reference AVGSALVW is replaced with the temporary result table. The new query is then run. The generated query looks like the following:

SELECT D.DEPTNAME, A.AVGSAL
  FROM CORPDATA.DEPARTMENT D, *QUERY0001 A
  WHERE D.DEPTNO=A.WORKDEPT

Whenever possible, isolatable selection from the query, except subquery predicates, is added to the view materialization process. This results in smaller temporary result tables and allows existing indexes to be used when materializing the view. This will not be done if there is more than one reference to the same view or common table expression in the query. The following is an example where isolatable selection is added to the view materialization:

SELECT D.DEPTNAME,A.AVGSAL 	
  FROM CORPDATA.DEPARTMENT D, AVGSALVW A
  WHERE D.DEPTNO=A.WORKDEPT
  A.WORKDEPT LIKE 'D%' AND AVGSAL>10000

OPNQRYF example:

OPNQRYF FILE(CORPDATA/DEPARTMENT AVSALVW)
  FORMAT(FORMAT1)
  JFLD((1/DEPTNO 2/WORKDEPT *EQ))
  QRYSLT('1/WORKDEPT *EQ %WLDCRD(''D*'') *AND 2/AVGSAL *GT 10000')  

The isolatable selection from the query is added to view resulting in a new query to generate the temporary result table:

SELECT WORKDEPT, AVG(SALARY) AS AVGSAL
  FROM CORPDATA.EMPLOYEE
  WHERE WORKDEPT LIKE 'D%'
  GROUP BY WORKDEPT
  HAVING AVG(SALARY)>10000