October 2004 Technical Tip Why use views in DB2?

One of the benefits of my job as a trainer is the opportunity to learn from my students. During a recent SQL course I learned a new reason to use views in DB2. I had told the class that some applications create views for the sole purpose of renaming fields. This is easily done as follows:

CREATE VIEW MYVIEW AS
  SELECT OLDNAME1 AS NEWNAME1, OLDNAME2 AS NEWNAME2
    FROM MYTABLE;

One of my students then told me that he had once worked at a company where they created a view for every table. The application programs could not access any base tables directly: all access to data was through views. That way, if a change was made to the structure of a base table, the views may need to be redefined, but the application programs would be unchanged!

I was intrigued by this approach and came up with the following example to illustrate this technique. First, I created base tables T1 (with fields F1 and F2) and T2 (with fields F1 and F3) as follows. Note A and D are in both tables, but B is in T1 only and C is in T2 only:

T1
F1 F2
AA1
BB1
DD1
     
T2
F1 F2
AA2
CC2
DD2

I then created views V1 and V2 of tables T1 and T2 respectively as follows:

CREATE VIEW V1 AS SELECT F1, F2 FROM T1;
CREATE VIEW V2 AS SELECT F1, F3 FROM T2;

Application programs would use these views.

I then created a new table, T3, which is intended to replace tables T1 and T2:

T3
F1 F2 F3
AA1A2
BB1--
C--C2
DD1D2

I then recreated views V1 and V2 as follows. Both views return the same data as before, therefore application programs which used V1 and V2 would not require any changes!

CREATE VIEW V1 AS SELECT F1, F2 FROM T3 WHERE F2 IS NOT NULL;
CREATE VIEW V2 AS SELECT F1, F3 FROM T3 WHERE F3 IS NOT NULL;

The complete SQL code for this example (using SPUFI) can be found at http://www.caliberdt.com/tips/WhyViewsSpufi.txt

Want to know more about SQL, DB2 or data base design? Give us a call! You can count on Caliber Data Training for top quality mainframe and/or internet programming education.


Go to the articles index. Written by Bill Qualls. Copyright © 2004 by Caliber Data Training 800.938.1222