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:
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:
I then created views V1 and V2 of tables T1 and T2 respectively as follows:
Application programs would use these views.
I then created a new table, T3, which is intended to replace tables T1 and T2:
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!
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.