Let's say you have a code table with codes 01, 02, 03, 04, 05, etc., and
there are 20 reports in your system where users prefer more meaningful
codes to be shown instead. By placing the code column value transformation
in a view, and using the view in those 20 reports, the users get what they
want and you didn't have to change the database or the application.
This can get confusing. There is an example quoted above where you can
replace a code with a value to be more user friendly. What happens then if
the code is 2A and the description is 20A.
Following the rule to have a complete view layer, you would have two views,
one is showing up the short code, the other the long description. The old
parts of your application would use the first, newer reports simply the
second. With the view layer you could even change the internal structure of
your database (one table with the short values only and a second table with
short and long column, joined in the view layer - all in one table with
both - one table only having the long values.
Going a little bit deeper into the discussion:
If your system used views like that for reading, and for INSERT / UPDATE /
DELETE operations, implemented code column value reversal transformations
inside INSTEAD OF triggers, your entire application could use a more user
friendly set of codes on the screen and on reports (i.e. entire read/write
presentation layer), and your database could continue using the less user
friendly legacy code values. The application would be unaware that the
transformations are taking place.
The instead of triggers will bring in some workload for every triggered
operation, this would slow down the read a little bit. If you would have
much reads and few writes, the implementation could be changed (if needed):
Put triggers to the write operations filling additional columns with the
derived information. And again the view layer would hide the internal
changes from the application.
This strategy is not restricted to new applications!!! Even old applications
could be redesigned to make use of it. First step would be: eliminate all
record level access by replacing all I/O operations by SQL (this wouldn't be
as hard as it might sound in the first moment: write a data access SRVPGM
for the RLA operations using SQL and replace the read/write/setll... by
calls to the corresponding modules). Second step is to isolate the physical
layer by a set of one to one views. Third step is to think about redesign in
This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact