A View of a View of a View

April 13, 2011 Ted Holt

One of the traits of a good database is that users are not forced to perceive the structure of the data as it is stored in the tables, but are free to perceive the data in ways that are meaningful to them. Today I share an SQL technique that achieves that goal. (No, you can’t do this with logical files.)

Alternate Reality

Before I illustrate the technique, let’s be sure we understand this idea of allowing the user to perceive the data as he wants to. Suppose a table (physical file) of invoices includes the date that an invoice is generated. That date may be stored as an eight-digit number in YYYYMMDD format. However, the user may not like that format. Instead of 20101116, he may prefer to see 11/16/10, 16-11-2011, or 16-Nov-2011. The following idea gives more ideas of how the user can have his own version of reality.

Reality

Alternate Reality

A physical file
contains 47 fields.

The user only sees 12
of the fields.

The user sees fields
that are derived from other fields.

The user sees the
fields arranged in a different order.

The customer account
number field is called CUSNUM.

The user sees the
field as CustomerNbr.

A physical file
contains 150 records.

The user only sees
records of type-A customers.

Data is stored in four
physical files.

The user sees data as
if all of it is stored in only one file.

Under DB2 for i, alternate reality is achieved through the use of logical files and SQL views. Logical files are so 1980. They can only take you so far. To create an effective alternate reality, you need SQL.

Let me begin my illustration by generating some test data. The following command creates a physical file called OBJD.

DSPOBJD OBJ(SOMELIB/*ALL)
        OBJTYPE(*ALL)
        OUTPUT(*OUTFILE)
        OUTFILE(MYLIB/OBJD)

A View of a Table

If you run this command, you’ll see a lot of information about the objects in a library. To create an alternate reality that is easier to work with, let’s:

Select only the fields we want to deal with
Give the fields names that are more easily remembered
Convert the six-byte character date fields (in MMDDYY on my U.S.-based system) to the date data type
Give the columns (fields) more meaningful names

create view mylib/objects as
select obj.ODLBNM as Library,
       obj.odobnm as Object,
       obj.ODOBTP as ObjectType,
       obj.odobat as ObjectAttr,
       obj.ODOBTX as Description,
       case
          when substr(obj.odcdat,5,1) between '0' and '3'
             then date('20'|| substr(obj.odcdat,5,2)||'-'||
                              substr(obj.odcdat,1,2)||'-'||
                              substr(obj.odcdat,3,2))
          when substr (obj.odcdat,5,1) <> ' '
             then date('19'|| substr(obj.odcdat,5,2)||'-'||
                              substr(obj.odcdat,1,2)||'-'||
                              substr(obj.odcdat,3,2))
          else null
       end as CreationDate,
       case
          when substr(obj.odldat,5,1) between '0' and '3'
             then date('20'|| substr(obj.odldat,5,2)||'-'||
                              substr(obj.odldat,1,2)||'-'||
                              substr(obj.odldat,3,2))
          when substr (obj.odldat,5,1) <> ' '
             then date('19'|| substr(obj.odldat,5,2)||'-'||
                              substr(obj.odldat,1,2)||'-'||
                              substr(obj.odldat,3,2))
          else null
       end as ChangeDate,
       case
          when substr(obj.odudat,5,1) between '0' and '3'
             then date('20'|| substr(obj.odudat,5,2)||'-'||
                              substr(obj.odudat,1,2)||'-'||
                              substr(obj.odudat,3,2))
          when substr (obj.odudat,5,1) <> ' '
             then date('19'|| substr(obj.odudat,5,2)||'-'||
                              substr(obj.odudat,1,2)||'-'||
                              substr(obj.odudat,3,2))
          else null
       end as LastUsedDate,
       obj.oducnt as NbrDaysUsed
  from mylib/objd as obj

The new view is called OBJECTS. The data looks a lot different under this view, even though how it’s stored in the database hasn’t changed. I can use this view from any query tool, read it with an RPG program, or otherwise reference it as I would the underlying physical file.

A View of a View of a Table

But we’re not finished yet. Let’s make it even more meaningful by creating some new fields to help us categorize the data.

create view mylib/ObjectUsage as
select Library, Object, ObjectType, ObjectAttr, 
       Description, 
       CreationDate, ChangeDate, LastUsedDate,
       case
          when LastUsedDate is null
             then '0-Never'
          when LastUsedDate >= current date - 1 month
             then '4-Within last month'
          when LastUsedDate >= current date - 6 months
             then '3-Within last six months'
          when LastUsedDate >= current date - 1 year
             then '2-Within last year'
          else '1-Long ago'
       end as UsagePeriod,
       NbrDaysUsed,
       case 
          when NbrDaysUsed > 100
             then '3-A Lot'
          when NbrDaysUsed >  25
             then '2-Some'
          when NbrDaysUsed >   0
             then '1-A Little'
          else '0-Never'
       end as UsageFrequency
  from mylib/objects

I have two new columns called UsagePeriod and UsageFrequency, which I can read for my own benefit. But I can also sort and select on those columns.

But what I really want you to notice is the FROM clause. Did you notice that it refers not to the physical file, but to the first view I created. Yes, that’s right! You can create views over views! Try creating a logical file over a logical file.

of a Table

But wait, there’s more! Let’s say that our analysis requires that we concentrate on objects that are deemed critical, which we define as those that have been used many times and/or have been used recently. Let’s create an alternate reality that helps us zero in on those objects.

create view mylib/ObjectUsageAnalysis as
select Library, Object, ObjectType, ObjectAttr, Description,
       CreationDate, ChangeDate, LastUsedDate, 
       UsagePeriod,  NbrDaysUsed, UsageFrequency,
       case
          when UsagePeriod >= '3' 
            or UsageFrequency >= '3'
             then '1'
          else '0'
       end as Critical
  from mylib/objectusage

You are probably not surprised to see that this view was created from the second view. ObjectUsageAnalysis is a view of a view of a view of a table. And I can query that view, like this:

select * from mylib/ObjectUsageAnalysis
 order by Critical desc, Library, Object

The system presents the data to me much differently from the way the DSPOBJD command stored it in a physical file. Try it for yourself.

It Isn’t Necessarily What It Is

“It is what it is” is a saying I have heard more and more over the past few years. I am not impressed with this saying. “It is what it is” seems to me to be an excuse to maintain a less-than-desirable situation, rather than take action to improve.

“It is what it is” accurately described disk files back in my System/34 and System/36 days, when I had to use input and output specs in RPG programs, and beginning and ending positions in #GSORT specs. But those days are long gone. The database tools we use today allow us to create realities of our own. We should take advantage of it.