Don’t Ignore the View

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 then 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. Of course the host variables have to be defined to receive the column values from the subsequent Fetch statement.

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 1, 2, 3
 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 to that shown in the next piece of code. There is also the added benefit of having a *FILE object that may be used to define an externally defined data structure to be used as the host variable on the fetch.

Declare C1 Scroll Cursor For
 select *

 from MyView
 where a.event = :ForEvent

 order by 1, 2, 3
 for read only;

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 define the view shown in the code below, which redefines the numeric field as a proper date field.

CREATE VIEW EMPVIEWA ( EMPNO , NAME , BIRTHDATE )
AS SELECT EMPNO, NAME,
DATE( SUBSTR( CHAR(DATEBIRTH) ,1 ,4 ) || '-'
|| SUBSTR( CHAR(DATEBIRTH) ,5 ,2 ) || '-' 
|| SUBSTR( CHAR(DATEBIRTH) ,7 ,2 ) )   FROM EMPLOYEES ;  

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.

Getting Started With Views

If the thought of having to learn all that DDL syntax for views seems a little bit daunting never fear: iSeries Navigator to the rescue. Navigator offers a GUI intuitive interface to defining views, as shown in Figure 6. I’m sure you recognize a join logical when you see one! Just click on the Show SQL button at any stage to see the DDL you are defining.

Defining a View in Navigator.

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.

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

Have fun!

Paul Tuohy is CEO of ComCon, an iSeries consulting company, and is one of the co-founders of System i Developer, which hosts the RPG & DB2 Summit conferences. He is an award-winning speaker who also speaks regularly at COMMON conferences, and is the author of “Re-engineering RPG Legacy Applications,” “The Programmers Guide to iSeries Navigator,” and the self-study course called “iSeries Navigator for Programmers.” Send your questions or comments for Paul to Ted Holt via the IT Jungle Contact page.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot