Guru Classic: Don’t Ignore the View

January 8, 2020 Paul Tuohy

Author’s Note: This article was originally published in April 2009. The use of DDL and embedded SQL have come a long way since then but the basic premise of the article still applies. I have removed the embedded SQL example using a SELECT * since this is a style that I no longer recommend (from the point of view of self-documenting code, possible performance gains and breaking old habits of thinking in records). I also changed the example of reformatting a numeric date column to use a DATES table as opposed to functions (a faster and better approach). I removed the example of using a wizard to define a view (it no longer exists). I also added a few more words in relation to performance when using views.

I recently had a discussion with some of my colleagues about the increased use of Data Definition Language (DDL) in SQL to define a database as opposed to traditional DDS. One of the items that came up in the discussion was that while people were using DDL to define tables and indexes, there seemed to be very few views being defined.

In this article I will discuss some of the benefits to be gained from using views, both within your applications and as a means to make data more readily accessible to your users.

What Is A View?

Those of us who come from the more traditional route on the system are inclined to think of a view as a non-keyed logical file. While this is true, it is also a little misleading since a view offers a lot more functionality than is found in a traditional logical file.

Another way of looking at a view is to think of it as an SQL statement that exists as a permanent object. By thinking of it as an SQL statement you start to get some idea of what may be defined in a view. So let us look at some of the advantages to be gained in defining a view as opposed to a traditional logical file:

• More join options are available. You may use inner joins, left outer joins, right outer joins, exception joins and cross joins.
• Joins are not limited to columns being equal, and you may also specify joins based on derived columns.
• You may define derived columns, the complexity of which far exceeds anything remotely possible in DDS. As well as normal expressions you also have full access to the SQL scalar functions. Therefore it is very easy to define a view that recasts or redefines the content of a column. We will see an example in a moment.
• You may define selection criteria that, again, far exceed anything that is remotely possible in DDS. A view definition may make full use of an SQL where clause.
• You may define a view that contains summary information, e.g., you could define a view that provides a summary of the sales as a region level. You can also make use of a having clause at the summary level.
• You may define a view of a view.

As you can see, these are features that go well beyond those offered by DDS. If there is a certain ring of familiarity about the above points, it is because they also aptly describe an SQL Select statement, which brings me full circle to thinking of a view as an SQL statement that exists as a permanent object.

The Perceived Problem

If views are so all powerful, why aren’t they being used more in our applications? The key (if you will pardon the pun) is that a view does not have a key. Therefore, we are inclined to think of them as un-usable in our traditional RPG or COBOL programs. How many non-keyed logical files do you have in your application?

But views are not unusable. It is true that you cannot use a view in the same way you would a logical file; you cannot define it as a keyed file on an RPG F spec. You just have to use embedded SQL to access the view. And this is not such a bad thing when you consider the amount of work being done for you by the view.

Using A View In An Application

The following code shows an embedded SQL statement declaring a cursor for a select statement that selects a number of columns from four tables with selection criteria based on a host variable.

declare C1 scroll cursor For
 select a.session, a.daynum, c.showseq, a.agendaid, a.room,
        b.shorttitle, d.day, d.dayname, c.fromtime, 
        c.totime

 from schedule a inner join sessions b
   on a.session = b.session
   inner join agenda c
   on a.event = c.event and
      a.daynum = c.daynum and
      a.agendaid = c.agendaid
   inner join daynums d
   on a.event = d.event and
      a.daynum = d.daynum
 where a.event = :ForEvent

 order by a.session, a.daynum, c.showseq
 for read only;

We can make life a little easier for ourselves by defining a view, as shown in the next piece of code. The select statement used in the creation of the view is the same as that used in the above code example, but without the where and order by clauses (although we could specify a where clause if required):

create view MyView as (
 select a.session, a.daynum, c.showseq, a.agendaid, a.room,
        b.shorttitle, d.day, d.dayname, c.fromtime, 
        c.totime

 from schedule a inner join sessions b
   on a.session = b.session
   inner join agenda c
   on a.event = c.event and
      a.daynum = c.daynum and
      a.agendaid = c.agendaid
   inner join daynums d
   on a.event = d.event and
      a.daynum = d.daynum);

With the view in place, the embedded SQL statement can now be simplified as follows:

declare C1 scroll cursor For
 select session, daynum, showseq, agendaid, room,
        shorttitle, day, dayname, fromtime, 
        totime

 from MyView
 where a.event = :ForEvent

 order by session, daynum, showseq
 for read only;

There is now the benefit that, if there is a problem with the SQL statement (perish the thought), you are debugging the problem in the view as opposed to in the RPG program.

Make Data More Accessible

The use of views can make any data we have defined on our system that much easier for users to decipher. Say we have a DDS-defined physical file that contains an employee ID, name, and date of birth. The date of birth is defined as a numeric field. To make the data that little bit more accessible we can utilize a DATES table that contains a row per day and columns that represent every permutation and combination of date we might want. The following view shows the table being used to retrieve a proper date in place of the numeric data field.

CREATE VIEW EMPVIEWA ( EMPNO , NAME , BIRTHDATE )
AS SELECT EMPNO, NAME, ISODATE  
FROM EMPLOYEES INNER JOIN DATES ON BIRTHDATE = YYYYMMDD;

But why stop there? Now that we have the basic view in place, we define another view of that view, which shows us the age of the employee. For instance:

CREATE VIEW EMPVIEWB ( EMPID , NAME , BIRTHDATE , AGE )
  AS SELECT EMPNO, NAME, BIRTHDATE, 
            YEAR( CURDATE( ) - BIRTHDATE)
     FROM EMPVIEWA ;

This gives you an idea of how views may be used to re-define and extend even DDS-defined files and also provides a simple example of defining a view of a view.

And Finally . . .

A quick word about performance: since views are not keyed (i.e., they do not have an access path associated with them), they do not incur maintenance overhead. The performance overhead is only incurred when the view is used–at which point the Query Engine determines which index best provides the requested data.

Even if you have a view of a view of a view there is no extra performance overhead. The Query Engine constructs a single SQL select statement from your statement and each of the select statements in the views – which is the statement you would have had to use if you didn’t have the views.

So, if you have not already done so, please go have a play with views. Ignore them and you will lose out.

Have fun!

Paul Tuohy, IBM Champion and author of Re-engineering RPG Legacy Applications, is a prominent consultant and trainer for application modernization and development technologies on the IBM Midrange. He is currently CEO of ComCon, a consultancy firm in Dublin, Ireland, and partner at System i Developer. He hosts the RPG & DB2 Summit twice per year with partners Susan Gantner and Jon Paris.

RELATED STORY

Don’t Ignore the View